SQL Server Cursor Alternatives

http://www.dotnet-tricks.com/Tutorial/sqlserver/IT5G180512-SQL-Server-Cursor-Alternatives.html

s we know, the cursors are required when we need to update records in a database table in singleton fashion means row by row. A Cursor also impacts the performance of the SQL Server since it uses the SQL Server instance’s memory, reduce concurrency, decrease network bandwidth and lock resources.

You should avoid the use of cursor. In this article, I am explaining how you can use cursor alternatives like as WHILE loop, Temporary tables and Table variables. We should use cursor in that case when there is no option except cursor.

Example of Cursor Alternative

Suppose we have table "ProductSales" that stores the information about each product sales. Now we want to calculate the Total Sales Quantity and Amount of each and every product.
We can solve this problem by following three methods.

 CREATE TABLE ProductsSales
(
 ID int IDENTITY(1,1) NOT NULL,
 ProductID int NOT NULL,
 ProductName varchar(50) NOT NULL,
 Qty int NOT NULL,
 Amount decimal(10, 2) NOT NULL ) 
GO
SELECT * FROM ProductsSales
--We have the table with below data 

Problem solution methods

1. Using Cursor

   
   

   
    SET NOCOUNT ON 
   DECLARE @ProductID INT 
   DECLARE @ProductName VARCHAR(100) 
   DECLARE @TotalQty INT 
   DECLARE @Total INT 
   DECLARE @TProductSales TABLE 
   ( 
    SNo INT IDENTITY(1,1),
    ProductID INT,
    ProductName VARCHAR(100),
    TotalQty INT,
    GrandTotal INT
    ) 
   --Declare Cursor 
   DECLARE Cur_Product CURSOR FOR SELECT DISTINCT ProductID FROM ProductsSales
    --Open Cursor 
   OPEN Cur_Product 
   --Fetch Cursor 
   FETCH NEXT FROM Cur_Product INTO @ProductID
   WHILE @@FETCH_STATUS = 0
    BEGIN 
   SELECT @ProductName = ProductName FROM ProductsSales WHERE ProductID = @ProductID 
   SELECT @TotalQty = SUM(Qty),@Total = SUM(Amount) FROM ProductsSales WHERE ProductID = @ProductID 
   INSERT INTO @TProductSales(ProductID,ProductName,TotalQty,GrandTotal) VALUES(@ProductID,@ProductName,@TotalQty,@Total) 
   FETCH NEXT FROM Cur_Product INTO @ProductID END 
   --Close and Deallocate Cursor
    CLOSE Cur_Product 
    DEALLOCATE Cur_Product
    --See Calculated data 
   SELECT * FROM @TProductSales 
   
   

   

2. Using Table Variable

   
   

   
    SET NOCOUNT ON 
   DECLARE @ProductID INT 
   DECLARE @ProductName VARCHAR(100) 
   DECLARE @TotalQty INT 
   DECLARE @Total INT 
   DECLARE @i INT =1 
   DECLARE @count INT 
    --Declare Table variables for storing data 
   DECLARE @TProduct TABLE ( SNo INT IDENTITY(1,1),
    ProductID INT
    ) 
    DECLARE @TProductSales TABLE
    (
    SNo INT IDENTITY(1,1),
    ProductID INT,
    ProductName VARCHAR(100),
    TotalQty INT,
    GrandTotal INT
    ) 
    --Insert data to Table variable @Product 
    INSERT INTO @TProduct(ProductID)
    SELECT DISTINCT ProductID FROM ProductsSales ORDER BY ProductID ASC 
    -- Count number of rows
    SELECT @count = COUNT(SNo) FROM @TProduct WHILE (@i <= @count)
    BEGIN
    SELECT @ProductID = ProductID FROM @TProduct WHERE SNo = @i
    SELECT @ProductName = ProductName FROM ProductsSales WHERE ProductID = @ProductID 
    SELECT @TotalQty = SUM(Qty),@Total = SUM(Amount) FROM ProductsSales WHERE ProductID = @ProductID 
    INSERT INTO @TProductSales(ProductID,ProductName,TotalQty,GrandTotal) VALUES(@ProductID,@ProductName,@TotalQty,@Total)
    SELECT @i = @i + 1
    END
    --See Calculated data
    SELECT * FROM @TProductSales 
   
   

   

3. Using Temporary Table

   
   

   
    SET NOCOUNT ON 
   DECLARE @ProductID INT 
   DECLARE @ProductName VARCHAR(100) 
   DECLARE @TotalQty INT 
   DECLARE @Total INT 
   DECLARE @i INT =1
   DECLARE @count INT 
   --Create Temporary Tables for storing data 
   CREATE TABLE #TProduct ( SNo INT IDENTITY(1,1),
    ProductID INT
    ) 
    CREATE TABLE #TProductSales
    (
    SNo INT IDENTITY(1,1),
    ProductID INT, ProductName VARCHAR(100), TotalQty INT, GrandTotal INT )
    --Insert data to temporary table #Product 
   INSERT INTO #TProduct(ProductID) SELECT DISTINCT ProductID FROM ProductsSales ORDER BY ProductID ASC 
   SELECT @count = COUNT(SNo) FROM #TProduct 
   WHILE (@i <= @count) 
   BEGIN 
   SELECT @ProductID = ProductID FROM #TProduct WHERE SNo = @i
   SELECT @ProductName = ProductName FROM ProductsSales WHERE ProductID = @ProductID 
   SELECT @TotalQty = SUM(Qty),@Total = SUM(Amount) FROM ProductsSales WHERE ProductID = @ProductID 
   INSERT INTO #TProductSales(ProductID,ProductName,TotalQty,GrandTotal) VALUES(@ProductID,@ProductName,@TotalQty,@Total)
   SELECT @i = @i + 1
    END
    --See Calculated data
    SELECT * FROM #TProductSales 
   --Now Drop Temporary Tables
    DROP TABLE #TProduct
    DROP TABLE #TProductSales 
   
   

   

Remove unsent database email from SQL Server

http://www.dotnet-tricks.com/Tutorial/sqlserver/4761260812-Remove-unsent-database-email-from-SQL-Server.html

uppose, you are sending mail to different-different users by using while loop and you forgot to insert while loop update statement. In this case SQL Server will generate thousands or millions of mail against a specific email address with in a min.

To stop SQL Server for sending unwanted mails we required to clean the unsent mail from database mail queue. We can do this by running below queries.

 SELECT * FROM msdb.dbo.sysmail_event_log;
-- To get number of unsent emails
select count(*) from msdb.dbo.sysmail_unsentitems;
-- remove all the unsent emails
delete from msdb.dbo.sysmail_unsentitems; 

Now all the unexpected email hav been removed from SQL Server database mail queue.

Get nth highest and lowest salary of an employee

http://www.dotnet-tricks.com/Tutorial/sqlserver/SQ23310812-Get-nth-highest-and-lowest-salary-of-an-employee.html

ne student of me asked "how can we get nth highest and lowest salary on an employee ?". In this article I am going to expose, how can we achieve this in SQL Server.

Suppose we have employee name and salary as shown in below fig.

Query to get nth(3rd) Highest Salary

 Select TOP 1 Salary as '3rd Highest Salary' 
from (SELECT DISTINCT TOP 3 Salary from Employee ORDER BY Salary DESC) 
a ORDER BY Salary ASC 

Query to get nth(3rd) Lowest Salary

 Select TOP 1 Salary as '3rd Lowest Salary' 
from (SELECT DISTINCT TOP 3 Salary from Employee ORDER BY Salary ASC) 
a ORDER BY Salary DESC 

Download SQL Server 2014 Express from Microsoft download center

http://www.dotnet-tricks.com/Tutorial/sqlserver/N0SU020414-Download-SQL-Server-2014-Express-from-Microsoft-download-center.html

icrosoft SQL Server 2014 Express is a powerful and reliable free data management system that delivers a rich and reliable data store for lightweight Web Sites and desktop applications. The Express edition is free and ideal for learning, developing, powering desktop, web & small server applications. The SQL Server 2014 Express release includes the full version of SQL Server 2014 Management Studio.

Microsoft SQL Server 2014 Express download link: SQL Server 2014 Express Download

SQL Server 2014 downloads

Before downloading SQL Server 2014 Express version, let's understand the various files and its importance.

1. LocalDB (SqlLocalDB)

   LocalDB is a lightweight version of Express that has all its programmable features, yet runs in user mode and has a fast, zero-configuration installation and short list of pre-requisites. Use this if you need a simple way to create and work with databases from code. It can be bundled with Application and Database Development tools like Visual Studio and or embedded with an application that needs local databases. You will see two files one for 32-bit system (ENU\x86\SqlLocalDB.msi) and other for 64-bit system (ENU\x64\SqlLocalDB.msi).

2. Express (SQLEXPR)

   This package includes the SQL Server database engine only. Best suited to accept remote connections or administer remotely. You will see two files one for 32-bit system (SQLEXPR_x86_ENU.exe) and other for 64-bit system (SQLEXPR_x64_ENU.ex

3. Express with Tools (SQLEXPRWT)

   This package contains everything needed to install and configure SQL Server as a database server including the full version of SQL Server 2014 Management Studio. Choose either LocalDB or Express depending on your needs above. You will see two files one for 32-bit system (SQLEXPRWT_x86_ENU.exe) and other for 64-bit system (SQLEXPRWT_x64_ENU.exe

4. SQL Server Management Studio Express (SQLManagementStudio)

   This does not contain the database, but only the tools to manage SQL Server instances, including LocalDB, SQL Express, SQL Azure, full version of SQL Server 2014 Management Studio, etc. If you already have the database and only need the management tools, download this one. You will see two files one for 32-bit system (SQLManagementStudio_x86_ENU.exe) and other for 64-bit system (SQLManagementStudio_x64_ENU.

5. Express with Advanced Services (SQLEXPRADV)

   This package contains all the components of SQL Server Express including the full version of SQL Server 2014 Management Studio. This is a larger download than “with Tools,” as it also includes both Full Text Search and Reporting Services.. You will see two files one for 32-bit system (SQLEXPRADV_x86_ENU.exe) and other for 64-bit system (SQLEXPRADV_x64_ENU.ex

Difference between CTE and Temp Table and Table Variable

http://www.dotnet-tricks.com/Tutorial/sqlserver/X517150913-Difference-between-CTE-and-Temp-Table-and-Table-Variable.html

emp Table or Table variable or CTE are commonly used for storing data temporarily in SQL Server. In this article, you will learn the differences among these three.

CTE

CTE stands for Common Table expressions. It was introduced with SQL Server 2005. It is a temporary result set and typically it may be a result of complex sub-query. Unlike temporary table its life is limited to the current query. It is defined by using WITH statement. CTE improves readability and ease in maintenance of complex queries and sub-queries. Always begin CTE with semicolon.

A sub query without CTE is given below :

SELECT * FROM (
 SELECT Addr.Address, Emp.Name, Emp.Age From Address Addr
 Inner join Employee Emp on Emp.EID = Addr.EID) Temp
WHERE Temp.Age > 50
ORDER BY Temp.NAME

By using CTE above query can be re-written as follows :

;With CTE1(Address, Name, Age)--Column names for CTE, which are optional
AS
(
SELECT Addr.Address, Emp.Name, Emp.Age from Address Addr
INNER JOIN EMP Emp ON Emp.EID = Addr.EID
)
SELECT * FROM CTE1 --Using CTE 
WHERE CTE1.Age > 50
ORDER BY CTE1.NAME

When to use CTE

1. This is used to store result of a complex sub query for further use.
2. This is also used to create a recursive query.

Temporary Tables

In SQL Server, temporary tables are created at run-time and you can do all the operations which you can do on a normal table. These tables are created inside Tempdb database. Based on the scope and behavior temporary tables are of two types as given below-

1. Local Temp Table

   Local temp tables are only available to the SQL Server session or connection (means single user) that created the tables. These are automatically deleted when the session that created the tables has been closed. Local temporary table name is stared with single hash ("#") sign.
   

   
   CREATE TABLE #LocalTemp
   (
    UserID int,
    Name varchar(50), 
    Address varchar(150)
   )
   GO
   insert into #LocalTemp values ( 1, 'Shailendra','Noida');
   GO
   Select * from #LocalTemp
   
   

   
   The scope of Local temp table exist to the current session of current user means to the current query window. If you will close the current query window or open a new query window and will try to find above created temp table, it will give you the error.

2. Global Temp Table

   Global temp tables are available to all SQL Server sessions or connections (means all the user). These can be created by any SQL Server connection user and these are automatically deleted when all the SQL Server connections have been closed. Global temporary table name is stared with double hash ("##") sign.
   

   
   CREATE TABLE ##GlobalTemp
   (
    UserID int,
    Name varchar(50), 
    Address varchar(150)
   )
   GO
   insert into ##GlobalTemp values ( 1, 'Shailendra','Noida');
   GO
   Select * from ##GlobalTemp
    
   
   

   
   Global temporary tables are visible to all SQL Server connections while Local temporary tables are visible to only current SQL Server connection.

Table Variable

This acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. This is also created in the Tempdb database but not the memory. This also allows you to create primary key, identity at the time of Table variable declaration but not non-clustered index.

 GO
 DECLARE @TProduct TABLE
 (
 SNo INT IDENTITY(1,1),
 ProductID INT,
 Qty INT
 ) 
 --Insert data to Table variable @Product 
 INSERT INTO @TProduct(ProductID,Qty)
 SELECT DISTINCT ProductID, Qty FROM ProductsSales ORDER BY ProductID ASC 
 --Select data
 Select * from @TProduct
 
 --Next batch
 GO
 Select * from @TProduct --gives error in next batch
 

Note

1. Temp Tables are physically created in the Tempdb database. These tables act as the normal table and also can have constraints, index like normal tables.
2. CTE is a named temporary result set which is used to manipulate the complex sub-queries data. This exists for the scope of statement. This is created in memory rather than Tempdb database. You cannot create any index on CTE.
3. Table Variable acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. This is also created in the Tempdb database but not the memory.

What do you think?

I hope you will enjoy the tips while programming with SQL Server. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome. 

Calculating Running Total

http://www.dotnet-tricks.com/Tutorial/sqlserver/b4I8120313-Calculate-Running-Total,-Total-of-a-Column-and-Row.html

CREATE TABLE CustomerOrders
(
 OrderID int identity,
 Amount Decimal(8,2),
 OrderDate SmallDatetime default getdate()
 )
 
Go
 
 INSERT INTO CustomerOrders(Amount) Values(120.12)
 INSERT INTO CustomerOrders(Amount) Values(20.12)
 INSERT INTO CustomerOrders(Amount) Values(10.12)
 INSERT INTO CustomerOrders(Amount) Values(30.12)
 INSERT INTO CustomerOrders(Amount) Values(40)
 
GO
 
 SELECT * FROM CustomerOrders

Calculating Running Total

Let's see how to calculate the running total using SQL Query as given below:

 select OrderID, OrderDate, CO.Amount
 ,(select sum(Amount) from CustomerOrders 
 where OrderID <= CO.OrderID)
 'Running Total'
from CustomerOrders CO

Calculating Final Total

Let's see how to calculate the final total using ROLLUP with in SQL Query as given below:

SELECT OrderID, SUM(Amount) AS Amount
FROM CustomerOrders
GROUP BY OrderID WITH ROLLUP

Calculating Total of All Numeric columns in a row

Let's see how to calculate the total of all numeric fields with in a row using SQL Query as given below:

SELECT OrderID, Amount, SUM(OrderID+Amount) AS RowNumericColSum
FROM CustomerOrders
GROUP BY OrderID,Amount
ORDER BY OrderID

Difference between database mirroring and replication

Mirroring:-

The Mirror database is not accessible for read or write access.

Replication:-

The Subscriber Database (backup site) is open to reads and writes.

B.)

Mirroring:-

Information flow will be only one way (from Principal to Mirror Server)

Replication:-

Changes can be merged, bi-directional changes can be made, so the information can flow from Publisher to Subscriber and the other way around.

C.)

Mirroring:-

In case of failure of the Principal Database, the Mirror Database will take over the control and will act as Principal and applications can be redirected automatically to connect to this new Principal Server. Very little downtime. No code change required in the application.

Replication:-

In case of failure on Publisher, applications need to be re-directed to the Subscriber manually (in case you really want to do that), requires code change in the app or the connection string.

D.)

Mirroring:-

Almost everything inside the DB is replicated to the DR site, Schema changes can be replicated easily.

Replication:-

You have the option to replicate selected set of tables/SP/functions inside the DB, Schema changes can give some hiccups.

In Short, Mirroring is a good tool for DR (Disaster Recovery) with very little downtime, but the drawback is that the DR site will *not* be accessible to users, whereas Replication can be used to Merge Data between two Servers, can act as a good tool for Reporting purposes as the backup site is accessible to the users, can also act a DR solution.

It all depends on what you need, what are the business requirements , which will help you to choose the right topology in your environment. You can go through SQL Books Online for more details about Mirroring and Replication.