European Windows 2012 Hosting BLOG

BLOG about Windows 2012 Hosting and SQL 2012 Hosting - Dedicated to European Windows Hosting Customer

SQL Server Hosting - HostForLIFE.eu :: INTERSECTION in SQL Server

clock February 26, 2020 11:06 by author Peter

Sometimes, we have a scenario when we want to get a common set of rows from 2 different result sets. For example, we have 2 queries and both returns employees record. If we want to find who all employees are present in both result sets, that time we can use INTERSECT to get the result. Below is the graphical representation of how INTERSECT works.

If you see in the above graphical representation, the left 2 circles have the B and C letters in common. The left side picture shows how the 2 circles have B and C letters common which is nothing but an intersection.

Now let's see how it works in the database.

So we are going to create 2 tables, EmpTable and ManagerEmp and then we will insert records in them.
CREATE TABLE EmpTable(EmpName   VARCHAR(50),City    VARCHAR(50),Title   VARCHAR(50)) 
CREATE TABLE ManagerTable(EmpName   VARCHAR(50),City    VARCHAR(50),Title   VARCHAR(50)) 
 
INSERT INTO EmpTable 
SELECT EmpName='John',City='Stamford',Title='Operator' 
 
INSERT INTO EmpTable 
SELECT EmpName='Luis',City='Danbury',Title='Electrical Engineer' 
 
INSERT INTO EmpTable 
SELECT EmpName='Smith',City='Wilton',Title='Driver' 
 
 
INSERT INTO ManagerTable 
SELECT EmpName='Mike',City='Wilton',Title='Driver' 
 
INSERT INTO ManagerTable 
SELECT EmpName='Smith',City='Wilton',Title='Driver' 
 
INSERT INTO ManagerTable 
SELECT EmpName='Jonathan',City='Armonk',Title='Accountant' 
 
INSERT INTO ManagerTable 
SELECT EmpName='Warner',City='Stamford',Title='Customer Service' 
 
INSERT INTO ManagerTable 
SELECT EmpName='Luis',City='Danbury',Title='Electrical Engineer' 


Now run below the query to find out the common employees in both tables.
SELECT * FROM EmpTable 
 
SELECT * FROM ManagerTable 
 
-- INTERSECTION 
SELECT * FROM EmpTable 
INTERSECT 
SELECT * FROM ManagerTable
 

Here is the output.

If you see below, in both the "EmpTable" and the "ManagerTable" tables, Luis and Smith both are employees. To join these 2 queries with INTERSECT, it gave these 2 names.

One thing is to remember here is both the tables/result sets should have the same columns and the same datatype for those columns, otherwise, it may give you "Conversion failed when converting...." if the data type does not match.

 



SQL Server Hosting - HostForLIFE.eu :: Rethinking Sorting In SQL Server Stored Procedures

clock February 5, 2020 11:03 by author Peter

We know that sorting can be one of the most expensive things in an execution plan as shown below. However, we continue to do ORDER BYs repeatedly. Yes, I 100% agree that there is a need to sort a results set and that this should be done in the procedure for good reason, but my concern is having multiple sorts, erroneous sorts, and the sorts that can be done elsewhere. These are the ones that waste resources and can stifle performance.

Many of us writing procedures tend to write in code blocks. We write the SELECT, JOINS, FROMs and WHERES then immediately follow it up with and ORDER BY as a way to check result sets before moving onto the next block of code. I admit I do this almost every time. But what most developers do not do is remove unneeded ORDER BYs that are not required. This is very costly and can lead to suboptimal performance not only of your procedure but also for TEMPDB as this is where all sorting takes place.
Do you sort in your procedures that are used for data consumers like reports, ETL or an application? If you do, I ask, why are you sorting in the procedure and not in the consumer ? Many report end users will resort the data in Excel, or the report itself gives parameters for custom sorts or the data doesn’t need a sort at all. Why are you wasting resources on the SQL Server side just for it to be nullified? By removing unneeded sorts or performing the sort in the application tier you can have big performance gains. I would rather have a report, ETL process or application take the performance hit then a procedure.
 
Let’s look at one of the procedures that are available in AdventureWorks2016CPT3 called uspGetOrderTrackingBySalesOrderID. We will run it using the example execution in the code and then remove the ORDER BY, compile and rerun. We will be able to see clearly see the difference.
    USE [AdventureWorks2016CTP3] 
     
    GO 
     
    /****** Object:  StoredProcedure [dbo].[uspGetOrderTrackingBySalesOrderID]    Script Date: 1/28/2020 11:31:16 AM ******/ 
     
    SET ANSI_NULLS ON 
     
    GO 
    SET QUOTED_IDENTIFIER ON 
    GO 
     
    ALTER PROCEDURE [dbo].[uspGetOrderTrackingBySalesOrderID] 
     
       @SalesOrderID [int] NULL 
    AS 
    BEGIN 
    /* Example: 
     
          exec dbo.uspGetOrderTrackingBySalesOrderID 53498 
    */ 
       SET NOCOUNT ON; 
       SET STATISTICS IO, TIME ON 
     
       SELECT 
          ot.SalesOrderID, 
          ot.CarrierTrackingNumber, 
          ot.OrderTrackingID, 
          ot.TrackingEventID, 
          te.EventName, 
          ot.EventDetails, 
          ot.EventDateTime 
       FROM 
          Sales.OrderTracking ot, 
          Sales.TrackingEvent te 
       WHERE 
          ot.SalesOrderID = @SalesOrderID AND 
          ot.TrackingEventID = te.TrackingEventID 
       --ORDER BY 
       --   ot.SalesOrderID, 
       --   ot.TrackingEventID; 
    END; 

Plan with ORDER BY

Plan without ORDER BY and Query Store graph showing the difference in duration between the two. You can clearly see the performance improvement, and this was just one sort in a very simple procedure. Take a moment and consider the sorting that happens in your code. I’d ask that when writing store procedures, doing code reviews or performance tuning that you take a second to ask why the sorts are being done in the data tier and if they can be performed elsewhere. You can see get some performance gains not only in your code but in TEMPDB as well when sorting is reigned in.



SQL Server Hosting - HostForLIFE.eu :: SQL Server In Memory Table Indexes

clock January 29, 2020 10:49 by author Peter
Now that I have written about In-Memory Tables and Migrating to In-Memory tables, let’s look at indexes and how they are created and how they work within those tables. As you can imagine indexes, called memory optimized indexes are different for these types of tables, so let’s see just how different that are from regular tables. Before we dive into this subject it is VERY important to note the biggest differences.

First, ALL memory optimized indexes MUST be created when the table is created or migrated. You cannot add indexes in an existing table without dropping and recreating the table. Secondly, currently you can only have 8 indexes per table including your primary key. Remember that every table must have a primary key to enforce a secondary copy for a minimum of schema durability This means you can only really add 7 additional indexes so be sure to understand your workloads and plan indexing accordingly.

Third, Memory Optimized Indexes only exists in memory they are not persisted to disk and are not logged in the transaction logs. Therefore, this means they are also recreated upon database startup and do incur a performance hit as they are rebuilt.

Next, there is no such thing as key lookups against an In-Memory table, as all indexes are by nature a covering index. The index uses a pointer to the actual rows to get the needed fields instead of using a primary key like physical tables do. Therefore, these are much more efficient in returning the proper data.

Lastly, there also is no such thing as fragmentation for these indexes, since these are not read from disk. Unlike on disk indexes, these do not have a fixed page length. On disk index use physical page structures within the B-Tree, determining how much of the page should be filled is what the Fill Factor does. Since this is not a requirement fragmentation does not exist.

Ok now that we made it through all of that, let’s look at the types of indexes you can create and gain an understanding of what they are and how they are created.

Nonclustered HASH Index
This index is used to access the In-Memory version of the table, called a Hash. These are great for predicates that are singleton lookups and not ranges of values. These are optimized for seeks of equality values. For example, WHERE Name = ‘Joe’. Something to keep in mind when determining what to include in your indexes is this; if your query has two or more fields as your predicate and your index only consists of one of those fields, you will get a scan. It will not seek on that one field that was included.

Understanding your workloads and indexing on the appropriate fields (or a combinations thereof) is important since you are limited to only 7 additional indexes. Given that this In-Memory OLTP is mainly focused on heavy insert/update workloads, and less so reading, this should be less of a concern.

These types of indexes are highly optimized and do not work very well if there are a lot of duplicate values in an index, the more unique your values better the index performance gains you will get. It is always important to know your data. When it comes to these indexes knowing your memory consumption plays a part. The hash index type is a fixed length and consume a fixed amount of memory determined upon creation. The amount of memory is determined by the Bucket Count value. It is extremely important to make sure this value is as accurate as possible. Right sizing this number can make or break your performance, too low of a number according to Microsoft “can significantly impact workload performance and recovery time of a database.

Look for my upcoming blog on determining bucket counts for more information. Meanwhile you can learn more about hash indexes at docs.microsoft.

Using T-SQL (both methods give the same result)
Example One (Note the index comes after the table fields)

CREATE TABLE [Sales]   
([ProductKey] INT NOT NULL,  
[OrderDateKey] [intNOT NULL,  
INDEX IDX_ProductKey HASH ([ProductKey]) WITH (BUCKET_COUNT = 100))  
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)  

Example Two (Note the index comes after the field)
CREATE TABLE [Sales]  
([ProductKey] INT NOT NULL INDEX IDX_ProductKey HASH WITH (BUCKET_COUNT = 100),  
[OrderDateKey] [intNOT NULL)  
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)  

Nonclustered Index
These are also used to access the In-Memory version of the table however, these are optimized for range values such as less than and equal to, inequality predicates and sorts orders. Examples are WHERE DATE between ‘20190101’ and ‘20191231’ and WHERE DATE <> ‘20191231’. These indexes do not require a bucket count or fixed memory amount. The memory consumed by these indexes are determined by the actual row counts and size of the indexed key columns which makes it a simpler to create.

Moreover, in contrast to hash indexes which needs all fields required for your predicate to be part of your index to get a seek, these do not. If your predicates have more than one field and your index has that one of those as its leading index key value, then you can still attain a seek.

Using T-SQL (both methods give the same result)
Example One (Note the index comes after the table fields)

CREATE TABLE [Sales]   
([ProductKey] INT NOT NULL,  
[OrderDateKey] [intNOT NULL,  
INDEX IDX_ProductKey ([ProductKey]))  
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)

Example Two (Note the index comes after the field)

CREATE TABLE [Sales]  
([ProductKey] INT NOT NULL INDEX IDX_ProductKey,  
[OrderDateKey] [intNOT NULL)  
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY) 

Determining which index type to use can be tricky but Microsoft has given us a great guide in the below chart. As you can see there some key differences to how In- Memory table indexes, memory optimized indexes, work compared to the normal disk indexes we are used to. Like with any other table design it is important to consider your index needs before you embark on creating or migrating to memory optimized tables. You’ll be happy you did.



SQL Server Hosting - HostForLIFE.eu :: SQL Comments Statement

clock January 20, 2020 11:15 by author Peter

SQL Comments statement can make your application easier for you to read and maintain. For example, we can include a comment in a statement that describes the purpose of the statement within your application with the exception of hints, comments within SQL. The statement does not affect the statement execution. Please refer to using hints on using this particular form of comment statement. 
 
A comment can appear between any keywords, parameters, or punctuation marks in a statement. You can include a comment in a statement in two ways:

  • Begin the comment with a slash and an asterisk (/*). Proceed with the text of the comment. This text can span multiple lines.
  • End the comment with an asterisk and a slash (*/). The opening and terminating characters need not be separated from the text by a space or a line break.
  • Begin the comment with -- (two hyphens). Proceed with the text of the comment. This text cannot extend to a new line. End the comment with a line break.

Some of the tools used to enter SQL have additional restrictions. For example, if you are using SQL*plus, by default you cannot have a blank line inside a multiline comment.
 
For more information, please refer to the documentation for the tool you use as an interface to the database. A SQL statement can contain multiple comments of both styles. The text of a comment can contain any printable characters in your database character set.
 
The comment statement indicates the user-provided text. Comments can be inserted on a separate line, nested at the end of a SQL command line, or within a SQL statement. The server does not evaluate the comment. 
SQL Comment uses the two hyphens (--) for single-line or nested comments. Comments inserted with -- are terminated by a new line, which is specified with a carriage return. Character (U+000A), line feed character (U+000D), or a combination of the two in SQL comments.
 
There is no maximum length for comments. The following table lists the keyboard shortcuts that you can use to comment or uncomment text.
 
Syntax
    -- text_of_comment    

Examples
The following example uses the -- commenting characters.
 
Syntax
    -- Choose the sample database.     
    USE sample;     
    GO     
    -- Choose all columns and all rows from the Address table.     
    SELECT *     
    FROM OrderDetails     
    ORDER BY OrderId  ASC; -- We do not have to specify ASC because      
    -- that is the default.     


SQL Single Line Comments
Single line comments start with --. Any text between -- and the end of the line will be ignored (will not be executed). The following example uses a single-line comment as an explanation.
 
Syntax 
   --Select all:   
    SELECT * FROM OrderDetails ; 


The following example uses a single-line comment to ignore the end of a line.
 
Syntax
    SELECT * FROM OrderDetails -- WHERE OrderName='Coffee';  

The following example uses a single-line comment to ignore a statement.
 
Syntax
    --SELECT * FROM OrderDetails;   
    SELECT * FROM OrderDetails ;  


SQL Multi-line Comments
SQL Multi-line comments start with /* and end with */. Any text between /* and */ will be ignored. The following example uses a multi-line comment as an explanation.
 
Syntax 
    /*Select all the columns   
    of all the records   
    in the OrderDetails table:*/   
    SELECT * FROM OrderDetails;  


 The following example uses a multi-line comment to ignore many statements.
 
Syntax
    /*SELECT * FROM Customers;   
    SELECT * FROM Products;   
    SELECT * FROM Orders;   
    SELECT * FROM Categories;*/   
    SELECT * FROM OrderDetails;  


To ignore just a part of a statement, also use the /* */ comment. The following example uses a comment to ignore part of a line.
 
Syntax
    SELECT CustomerName, /*City,*/ Country FROM Customers;  

The following example uses a comment to ignore part of a statement
 
Syntax
    SELECT * FROM OrderDetails WHERE (OrderName LIKE 'L%'   
    OR OrderName LIKE 'R%' /*OR OrderName  LIKE 'S%'   
    OR OrderName LIKE 'T%'*/ OR OrderName LIKE 'W%')   
    AND OrderName ='Mango'   
    ORDER BY OrderrAddress;  


Summary
In this article, you learned how to use a SQL Comments statement with various options.

 



SQL Server Hosting - HostForLIFE.eu :: Transaction Explained in SQL Server

clock December 18, 2019 11:56 by author Peter

 In this blog, I will explain the SQL Transaction. A transaction is a logical unit of work. Each transaction begins with a specific task and ends with all tasks in a group successfully complete. If any tasks fail, it means that the transaction fails. All steps must be committed (transaction Success) or rolled back (transaction failure). A transaction begins to initiate the execution of the SQL statement. A transaction must be committed or rolled back. It is separate operations succeed is transaction succeed and committed to the database. If any separate operation fails means transaction failure and must be undone rolled back. The following are the properties of a transaction.
 
1. Atomicity
2. Consistency
3. Isolation
4. Durability

Transaction Process

  • BEGIN TRANSACTION - Starts the transaction
  • ROLLBACK - If an error occurred, reverts the existing transaction changes
  • COMMIT - No error occurred, then it saves all transaction states
  • SAVEPOINT - Rollback particular named transaction

Example
    BEGIN TRANSACTION T1 
    UPDATE TB_NAME SET FIRST_NAME ='R' WHERE ID=1 
    COMMIT; 
     
    BEGIN TRANSACTION T1 
    UPDATE TB_NAME SET FIRST_NAME ='R' WHERE ID=1 
    ROLLBACK; 
     
    SAVEPOINT T1 
    UPDATE TB_NAME SET FIRST_NAME ='R' WHERE ID=1 
    ROLLBACK T1; 

HostForLIFE.eu SQL Server Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.

 



SQL Server Hosting - HostForLIFE.eu :: Effective Paging, Sorting And Filtering Using SQL Server Stored Procedure

clock October 30, 2019 12:01 by author Peter

There was a situation where I had to implement fast/effective Paging, Sorting and Filtering with Stored Procedure in MS SQL Server. There are a number of articles and blogs where you can find about to do Paging, Sorting and Filtering with Stored Procedure in MS SQL Server. Hence, I started and done some research on this to find out the best solution. I found it in “Pagination with OFFSET / FETCH : A better way”

Using OFFSET / FETCH into the CTE I have created a stored procedure that was at least faster twice in return time as the alternatives found on the internet.

Here, I have kept all the implementation details along with a complete example.

Prerequisites
You should have a basic knowledge of MS SQL Stored Procedure, queries and CTE.

How to do effective Paging, Sorting and Filtering with Stored Procedure

To implement and execute this let us follow three steps:

  • Create table.
  • Insert data into the table.
  • Create stored procedure.


Create table
CREATE TABLE Employee 

    Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, 
    Name varchar(25) NOT NULL, 
    City varchar(25) NOT NULL 


Insert data into the table
declare @i int = 1 
declare @total int = 500000 
 
while @i <= @total 
begin 
    insert into Employee2 (Name, City) values (RIGHT('000000'+convert(varchar, @i),6), convert(varchar, @i%4)) 
    set @i += 1; 
end

For testing purpose, I have added 5 lakh records into the Employee table.

Create stored procedure
--GetAllEmployeesWay4 '', '', 1, 25, 'Name', 'Asc' 
--GetAllEmployeesWay4 'Name', '1', 1, 25, 'Name', 'Asc' 
--GetAllEmployeesWay4 'City', '1', 1, 25, 'Name', 'Asc' 
CREATE PROCEDURE [dbo].[GetAllEmployeesWay4] 

    @SearchColumn NVARCHAR(50) = NULL, 
    @SearchValue NVARCHAR(50) = NULL, 
    @PageNo INT = 1, 
    @PageSize INT = 10, 
    @SortColumn NVARCHAR(20) = 'Name', 
    @SortOrder NVARCHAR(20) = 'ASC' 

AS BEGIN 
    SET NOCOUNT ON; 
 
    SET @SearchColumn = LTRIM(RTRIM(@SearchColumn)) 
    SET @SearchValue = LTRIM(RTRIM(@SearchValue)) 
 
    ; WITH CTE_Results AS  
    ( 
        SELECT Id, Name, City from Employee 
 
        WHERE @SearchColumn= '' OR  (  
                CASE @SearchColumn  
                    WHEN 'Name' THEN Name  
                    WHEN 'City' THEN City 
                END 
            ) LIKE '%' + @SearchValue + '%' 
 
            ORDER BY 
            CASE WHEN (@SortColumn = 'Name' AND @SortOrder='ASC') 
                        THEN Name 
            END ASC, 
            CASE WHEN (@SortColumn = 'Name' AND @SortOrder='DESC') 
                        THEN Name 
            END DESC, 
            CASE WHEN (@SortColumn = 'City' AND @SortOrder='ASC') 
                        THEN City 
            END ASC, 
            CASE WHEN (@SortColumn = 'City' AND @SortOrder='DESC') 
                        THEN City 
            END DESC  
            OFFSET @PageSize * (@PageNo - 1) ROWS 
            FETCH NEXT @PageSize ROWS ONLY 
    ), 
    CTE_TotalRows AS  
    ( 
        select count(ID) as TotalRows from Employee 
        WHERE @SearchColumn= '' OR  (  
                CASE @SearchColumn  
                    WHEN 'Name' THEN Name  
                    WHEN 'City' THEN City 
                END 
            ) LIKE '%' + @SearchValue + '%' 
    ) 
    Select TotalRows, t.Id, t.Name, t.City from dbo.Employee as t, CTE_TotalRows  
    WHERE EXISTS (SELECT 1 FROM CTE_Results WHERE CTE_Results.ID = t.ID) 
 
    OPTION (RECOMPILE) 
END


Execute stored procedure
Execute the above stored procedure with different parameters and you can get result accordingly:
GetAllEmployeesWay4 '', '', 1, 25, 'Name', 'Asc'
GetAllEmployeesWay4 'Name', '1', 1, 25, 'Name', 'Asc'
GetAllEmployeesWay4 'City', '1', 1, 25, 'Name', 'Asc'


Complete example

For your reference, I have kept complete example in a single folder and uploaded that with this article and it contains below script files:
Step1_Create_Table
Step2_Insert_Data_into_Table
Step3_Create_Stored_Procedure

Summary
Now, I believe you will be able to do Effective Paging, Sorting and Filtering with Stored Procedure in MS SQL Server using.

HostForLIFE.eu SQL Server 2012 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.



SQL Server 2012 Hosting Belgium - HostForLIFE.eu :: How to Fix Distribution setup SQL Server Agent error: "RegCreateKeyEx() returned error 5, 'Access is denied.'" ?

clock October 15, 2019 12:15 by author Peter

With this short article, I will tell you about How to Fix Distribution setup SQL Server Agent error: "RegCreateKeyEx() returned error 5, 'Access is denied.'" on my SQL Server 2012 Hosting.

In the Configure Distribution Wizard, the step "Configuring SQL Server Agent to start automatically" errors with the following text:

TITLE: Configure Distribution Wizard
------------------------------
An error occurred configuring SQL Server Agent.
------------------------------
ADDITIONAL INFORMATION:
RegCreateKeyEx() returned error 5, 'Access is denied.' (Microsoft SQL Server, Error: 22002)

This is a very minor error, and not difficult to work around at all. The wizard is making an attempt to alter the SQL Server Agent service "Start Mode" to Automatic. you'll be able to try this via the SQL Server Configuration Manager instead.

In the Sysinternals method Monitor, you'll see: Operation: RegCreateKey Result: ACCESS DENIED Path: "HKLM\System\CurrentControlSet\Services\SQLAgent$SQL2012"

 

If you encounter this error, choose "No" in the "SQL Server Agent Start" page in the configure Distribution Wizard (as shown below), so set your agent service to Automatic start Mode via the SQL Server Configuration Manager.

The third step of the wizard that failed before won't happen. Why the failure truly happens I didn't figure this out, and i am open to feedback, however this seems like a vestigial step to a wizard that otherwise has no negative impact. Running SSMS with "run as Administrator" doesn't seem to fix this error either. i would like to recognize why this error happens within the 1st place.

HostForLIFE.eu SQL Server 2012 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.



SQL Server Hosting - HostForLIFE.eu :: How To Find Running Total And Final Total In SQL Server?

clock September 11, 2019 10:04 by author Peter

Here, we will explain how to find Running Total and Final Total or Grand Total in SQL Server. For example, you have the below given #tblEmployee temporary table and has the data, as shown below.

    CREATE TABLE #tblEmployee 
    ( 
       EmpID int identity, 
       Salary Decimal(8,2), 
       Grade varchar(10) 
    ) 


Insert some values in a temporary table like this.
    INSERT INTO #tblEmployee(Salary,Grade) Values(10000.51,'B') 
    INSERT INTO #tblEmployee(Salary,Grade) Values(20000.12,'B') 
    INSERT INTO #tblEmployee(Salary,Grade) Values(25000.02,'B') 
    INSERT INTO #tblEmployee(Salary,Grade) Values(9000.28,'C') 
    INSERT INTO #tblEmployee(Salary,Grade) Values(7000,'C') 
    INSERT INTO #tblEmployee(Salary,Grade) Values(6000,'C') 


Find the Running Total in the SQL Server

Write the following SQL Query to find the running total for the Salary column from #tblEmployee temporary table.
SELECT EmpID, Grade, Emp.Salary ,(SELECT SUM(Salary) FROM #tblEmployee WHERE EmpID <= Emp.EmpID) 'Running Total' FROM #tblEmployee Emp 
I have used the sub-query to find the running total.
 
Find the Final Total or Grand Total in SQL Server
 Write the following SQL Query to find the final total or grand total for the Salary column from #tblEmployee temporary table.
    SELECT EmpID, SUM(Salary) AS Salary FROM #tblEmployee GROUP BY EmpID WITH ROLLUP 

Here I used ROLLUP to achieve the desired result.
 
What is ROLLUP
The ROLLUP is an extension of the GROUP BY clause. The ROLLUP option allows you to include extra rows that represent the subtotals, which are commonly referred to as super-aggregate rows, along with the grand total row. By using the ROLLUP option, you can use a single query to generate multiple grouping sets.
 
Following Result produce by this SQL Query,

So, in this blog, we tried to explain how to find the running total from the SQL table. I hope this blog helps all of us. It is also most asked question from the interview.

HostForLIFE.eu SQL Server 2012 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.

 



SQL Server Hosting - HostForLIFE.eu :: Instead Of Triggers

clock September 4, 2019 12:41 by author Peter

Instead of triggers are used to skip DML commands. They fire when you try to execute insert, update or delete statement but instead of executing these commands trigger actually works and trigger functionality executes.
 

Example
    create table approved_emp ( eid int identity(1,1), ename varchar(30)) 
    create table emp ( id int identity(1,1) , ename varchar(30), AddedBy varchar(30)) 
      
    Create trigger instead_of on approved_emp 
    instead of insert 
    as 
    begin 
    declare @name varchar(30) 
    select @name=ename from inserted 
    insert into temp_audit values(@name, USER ) 
    end 


So, basically, trigger will work as, when we will try to add new record in approved_emp table, instead of inserting new records it will add ename into emp table. No data will reflect in approved_emp table as trigger is fired on the table every time while adding data into that table.
 
You can also create instead of triggers for update and delete as well.
 



SQL Server Hosting - HostForLIFE.eu :: Rename SQL Server Database

clock July 24, 2019 12:47 by author Peter

Database Administrators usually use the sp_renamedb system stored procedure to quickly rename a SQL Server Database. However, the drawback of using sp_renamedb is that it doesn't rename the Logical and Physical names of the underlying database files. It's a best practice to make sure the Logical Name and Physical File Name of the database is also renamed to reflect the actual name of the database to avoid any confusion with backup, restore or detach/attach operations.

Let's first create a new database named CoreDB using the T-SQL below:

USE master
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'CoreDB')
DROP DATABASE CoreDB
GO
USE master
GO
CREATE DATABASE [CoreDB]
ON PRIMARY
(
NAME = N'CoreDB',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\CoreDB.mdf' ,
SIZE = 2048KB ,
FILEGROWTH = 1024KB
)
LOG ON
(
NAME = N'CoreDB_log',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\CoreDB_log.ldf' ,
SIZE = 1024KB ,
FILEGROWTH = 10%
)
GO

Rename CoreDB Database Using sp_renamedb System Stored Procedure

Now let's rename the CoreDB database to ProductsDB by executing the below T-SQL code.

USE master
GO
ALTER DATABASE CoreDB
SET SINGLE_USER

WITH ROLLBACK IMMEDIATE
GO
EXEC master..sp_renamedb 'CoreDB','ProductsDB'
GO
ALTER DATABASE ProductsDB
SET MULTI_USER
GO

Once the above T-SQL has executed successfully the database name will change however the Logical Name and File Name will not change. You can verify this by executing the T-SQL below:

USE master
GO
/* Identify Database File Names */
SELECT

name AS [Logical Name],
physical_name AS [DB File Path],
type_desc AS [File Type],
state_desc AS [State]
FROM sys.master_files
WHERE database_id = DB_ID(N'ProductsDB')
GO

Your output should look something like this from the above query.

You can see in the above snippet that the Logical Name and File Name in the DB File Path column for ProductsDB are still reflecting the old name of CoreDB. This is not a good practice to follow in a Production Environment. Below you will see the steps which a DBA can follow to rename the database and its respective files.

Steps to Rename a SQL Server Database

DBAs should follow the below steps which will not only rename the database, but at the same time will also rename the Logical Name and File Name of the database.

This first set of commands put the database in single user mode and also modifies the logical names.


/* Set Database as a Single User */
ALTER DATABASE CoreDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
/* Change Logical File Name */
ALTER DATABASE [CoreDB] MODIFY FILE (NAME=N'CoreDB', NEWNAME=N'ProductsDB')
GO
ALTER DATABASE [CoreDB] MODIFY FILE (NAME=N'CoreDB_log', NEWNAME=N'ProductsDB_log')
GO

This is the output from the above code.


Now we need to detach the database, so we can rename the physical files.  If the database files are open you will not be able to rename the files.

/* Detach Current Database */
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'CoreDB'
GO

Once the CoreDB database is detached successfully then the next step will be to rename the Physical Files. This can be done either manually or by using the xp_cmdshell system stored procedure. You can enable xp_cmdshell feature using the sp_configure system stored procedure.

USE master
GO
sp_configure 'show advanced options'
GO
/* 0 = Disabled , 1 = Enabled */
sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE
GO

Once xp_cmdshell is enabled you can use the below script to rename the physical files of the database.

/* Rename Physical Files */
USE [master]
GO
EXEC xp_cmdshell 'RENAME "C:\Program Files\Microsoft SQL ServerMSSQL10.SQL2008\
MSSQL\DATA\CoreDB.mdf", "ProductsDB.mdf"'
GO
EXEC xp_cmdshell 'RENAME "C:\Program Files\Microsoft SQL ServerMSSQL10.SQL2008\
MSSQL\DATA\CoreDB_log.ldf", "ProductsDB_log.ldf"'
GO

Once the above step has successfully executed then the next step will be to attach the database, this can be done by executing the T-SQL below:

/* Attach Renamed ProductsDB Database Online */
USE [master]
GO
CREATE DATABASE ProductsDB ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\ProductsDB.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\ProductsDB_log.ldf' )
FOR ATTACH
GO

Once the above step has successfully executed then the final step will be to allow multi user access for the user database by executing the below T-SQL:

/* Set Database to Multi User*/
ALTER DATABASE ProductsDB SET MULTI_USER
GO

You can verify the Logical and File Names for the ProductsDB database by executing the T-SQL below:

USE master
GO
/* Identify Database File Names */
SELECT
name AS [Logical Name],
physical_name AS [DB File Path],
type_desc AS [File Type],
state_desc AS [State]
FROM sys.master_files
WHERE database_id = DB_ID(N'ProductsDB')

 

HostForLIFE.eu SQL Server 2012 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.



About HostForLIFE.eu

HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

We have offered the latest Windows 2016 Hosting, ASP.NET Core 2.2.1 Hosting, ASP.NET MVC 6 Hosting and SQL 2017 Hosting.


Tag cloud

Sign in