SQL Server 2005 CTE (Common Table Expression)

SQL Server 2005 CTE (Common Table Expression)

By Bihag Thaker

CTE is a new feature provided by Microsoft SQL Server 2005. In real world, we often need to query hierarchical data from the database. For example, to get a list of hierarchical list of all the employees, list of product categories etc. CTE fulfills this requirement and let us query the database recursively.

Demo Builder - Create Flash Presentations
Create interactive Flash movies that allow you to show how applications and systems work. Download a FREE trial now.

Let’s us do this practically. Assume that we store different categories of computer books and any category can have sub-categories. For this, we will create a table named tblCategories with the following structure and insert some categories into this table as shown below:

Create Table tblCategories
(
CategoryID Int Constraint PK_tblCategories_CategoryID Primary Key,
CategoryName VarChar(100),
ParentCategoryID Int Constraint FK_tblCategories_ParentCategoryID References tblCategories(CategoryID)
)

GO

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(1,'Languages',Null)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(2,'Networking',Null)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(3,'Databases',Null)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(4,'Visual Basic',1)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(5,'C#',1)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(6,'Java',1)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(7,'VB.Net',4)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(8,'VB 6.0',4)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(9,'Desktop Application Development with VB.Net',7)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(10,'Web Application Development with VB.Net',7)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(11,'ActiveX Objects and VB 6.0',8)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(12,'Network Security',2)

Now if you query the database with the following SELECT command,

Select * From tblCategories Where CategoryID = 1

You will get the following result:

CategoryID CategoryName ParentCategoryID
----------- ---------------- ----------------
1 Languages NULL

(1 row(s) affected)

No surprise! But what, if you want to get the list of all the categories/sub-categories falling under the root category ‘Languages’? To do this, you will need to perform a recursive query and to do that we use CTE.

Let’s do this with the help of CTE. To create CTE we will use the following syntax:

With cteCategories
AS (
Select CategoryID,CategoryName,ParentCategoryID
From tblCategories
Where CategoryID=1

Union All

Select C.CategoryID,C.CategoryName,C.ParentCategoryID
From tblCategories As C Inner Join cteCategories As P On C.ParentCategoryID = P.CategoryID

)

Select CategoryID,CategoryName,ParentCategoryID From cteCategories

Run the above query and see the result as shown below:

CategoryID CategoryName ParentCategoryID
----------- ---------------------------------------------------- ----------------
1 Languages NULL
4 Visual Basic 1
5 C# 1
6 Java 1
7 VB.Net 4
8 VB 6.0 4
11 ActiveX Objects and VB 6.0 8
9 Desktop Application Development with VB.Net 7
10 Web Application Development with VB.Net 7

(9 row(s) affected)

SQL Server 2005 Paging Performance Tip

SQL Server 2005 Paging Performance Tip

By Robbe Morris

This quick tip demonstrates how to get the total rows as part of the paging query as well as how avoid a common coding error with joins that can harm performance.

Demo Builder - Create Flash Presentations
Create interactive Flash movies that allow you to show how applications and systems work. Download a FREE trial now.

I've seen the following technique in several beginner code samples for demonstrating SQL Server 2005's ability to return paged results.

I've added the TotalRows = Count(*) OVER() line to demonstrate how return the total rows returned above and beyond the row count for the paged set. This removes the need for a second query to get the total rows available for paging techniques in your application. In your application, just check to make sure your resultset has records, then just grab the first record and retrieve its TotalRows column value.

Notice that in this query, the JOIN between the Orders table and the Users table is being run across all records that are found NOT just the records returned in the paged set.

declare @StartRow int
declare @MaxRows int

select @StartRow = 1
select @MaxRows = 10

select *
from
(select o.*,u.FirstName,u.LastName,
TotalRows=Count(*) OVER(),
ROW_NUMBER() OVER(ORDER BY o.CreateDateTime desc) as RowNum
from Orders o , Users u
WHERE o.CreateDateTime > getdate() -30
AND (o.UserID = u.UserID)
)
WHERE RowNum BETWEEN @StartRow AND (@StartRow + @MaxRows) -1

If you adjust your query as follows, you will see a substantial boost in performance. Notice this query only performs the join on the returned resultset which is much, much smaller.

SELECT MyTable.*,u.FirstName,u.LastName
FROM
(SELECT o.*,
TotalRows=Count(*) OVER(),
ROW_NUMBER() OVER(ORDER BY o.CreateDateTime desc) as RowNum
FROM Orders o
WHERE o.CreateDateTime > getdate() -30
) as MyTable, Users u
WHERE RowNum BETWEEN @StartRow AND (@StartRow + @MaxRows) -1
and (MyTable.UserID = u.UserID)