Generating 1 million rows in less than a second

If you are a SQL Query developer like me than you must came across the situation where you need an auxiliary table of numbers which contain just one column and rows like 1,2,3…….n
There are so many uses of this table like generating dummy data by cross joining this table to another table etc.
Following is a very quick and efficient way of generating 1 million rows in less than a second by using Common Table Expressions or CTE and Recursion of SQL Server. This logic is actually proposed in the book called SQL Server 2005 TSQL Querying by Microsoft press.

Following is the code:

–Declare a variable to hold the
–count of rows to be generated
Declare @p_NumberOfRows Bigint

–We need 1 million rows
Select @p_NumberOfRows=100000;

With Base As
(
Select 1 as n
Union All
Select n+1 From Base Where n < Ceiling(SQRT(@p_NumberOfRows))
),
Expand As
(
Select 1 as C
From Base as B1, Base as B2
),
Nums As
(
Select Row_Number() OVER(ORDER BY C) As n
From Expand
)
Select n from Nums Where n<=@p_NumberOfRows
–Remove Maximum Recursion level constraint
OPTION (MaxRecursion 0);

Calculate Difference between current and previous rows…..CTE and Row_Number() rocks!

Ever wanted to calculate difference between current row’s column to previous row’s column? It has several usages like calculating trends, monitoring changes etc. Several TSQL solutions are available for this problem but Common Table Expressions and Row_Number function in SQL Server 2005 provides an excellent way to solve this. By using CTE to calculate the difference between current and last row gives you excellent performance and can be used for huge tables. Following is the TSQL code to calculate the difference between CurrentPointValue to PreviousPointValue.

—Declare a test Table variable for our sample
Declare @m_TestTable table
(
DateRecorded datetime,
PointValue int
)

—Insert sample data
Insert into @m_TestTable Values (dateadd(day,1,GetDate()),150)
Insert into @m_TestTable Values (dateadd(day,2,GetDate()),350)
Insert into @m_TestTable Values (dateadd(day,3,GetDate()),500)
Insert into @m_TestTable Values (dateadd(day,4,GetDate()),100)
Insert into @m_TestTable Values (dateadd(day,5,GetDate()),150);

—Create CTE
With tblDifference as
(
Select Row_Number() OVER (Order by DateRecorded) as RowNumber,DateRecorded,PointValue from @m_TestTable
)

—Actual Query
Select convert(varchar, Cur.DateRecorded,103) as CurrentDay, convert(varchar, Prv.DateRecorded,103) as PreviousDay,Cur.PointValue as CurrentValue, Prv.PointValue as PreviousValue,Cur.PointValue-Prv.PointValue as Difference from

tblDifference Cur Left Outer Join tblDifference Prv
On Cur.RowNumber=Prv.RowNumber+1
Order by Cur.DateRecorded

Retrieving Employee Hierarchy through CTE

Retrieving subordinates of a given employee or listing sub-nodes of the given node is a classic TSQL problem. This problem can be solved either iteratively by using cursor or recursively by using UDF. Another recursive approach which is now available in SQL Server 2005 instead of UDF is CTE or Common Table Expressions.

Common Table Expression is a much cleaner approach and is highly recommended in these scenarios. Following is a TSQL stored procedure for AdventureWorks database which have typical
EmployeeId and ManagerId columns table to store hierarchy of employees. Following is the code:

Create Proc usp_GetSubordinates(@p_ManagerId int)
as
Begin

With SubCTE
As
(
Select EmployeeId,P.FirstName+‘ ‘ + P.LastName as FullName, 0 as level
From HumanResources.Employee E Inner Join Person.Contact P
ON E.ContactId=P.ContactId
Where EmployeeId=@p_ManagerId

Union All

Select C.EmployeeId,PP.FirstName+‘ ‘ + PP.LastName as FullName, P.level+1
From SubCTE as P
Inner Join HumanResources.Employee C
ON C.ManagerId=P.EmployeeId
Inner Join Person.Contact PP
ON C.ContactId=PP.ContactId

)

Select * from SubCTE;

End

Caluclating running total

Calculating running total is a typical requirement of most several financial reports. These reports requires a field which displays the running total another field like Salary or Amount etc..

Following is the query to calculate running total for AdventureWorks Purchasing.PurchaseOrderHeader table:

–Actual Query
With
tblItemsWithRow(RowNum,PurchaseOrderID,TotalDue)
as
( Select Row_Number() Over(Order by PurchaseOrderID) as RowNum, PurchaseOrderID,TotalDue
from Purchasing.PurchaseOrderHeader
)
–Final Query to return the running total
Select RowNum,PurchaseOrderID,TotalDue,
( Select Sum(TotalDue) from tblItemsWithRow m where m.RowNum<= s.RowNum) as RunningTotal
from tblItemsWithRow s