CTE (Common Table Expression) SQL-Server 2005

One of the beautiful features of MS SQL-Server 2005 is CTE and it is mainly used for recursive query. In recursive query, query executes itself; it is the same concept we used to use in C or C# etc programming language for recursive function. In real world we often need recursive hierarchical data for list of category and subcategory resides in one single table. Let’s have a look at it.

Step 1:

Simply create one table.

USE AdventureWorks

GO

Create Table VehicleCategory

(

ID Int Constraint PK_VehicleCategoryID Primary Key,

BikeCategory VarChar(100),

ParentID Int Constraint FK_VehicleCategory_ParentID References VehicleCategory(ID),

Description varchar(50)

)

GO

Step 2:

Insert few records in above created table:

Insert Into VehicleCategory(ID,BikeCategory,ParentID,Description) Values(1,‘Bike’,Null,‘Main Category’)

Insert Into VehicleCategory(ID,BikeCategory,ParentID,Description) Values(2,‘Scooter’,Null,‘Main Category’)

Insert Into VehicleCategory(ID,BikeCategory,ParentID,Description) Values(3,‘Yamaha RX 100’,1,‘125 CC bike of Yamaha’)

Insert Into VehicleCategory(ID,BikeCategory,ParentID,Description) Values(4,‘Hero Honda CBZ’,1,‘150 CC bike of hero honda’)

Insert Into VehicleCategory(ID,BikeCategory,ParentID,Description) Values(5,‘Honda Activa’,2,‘125 CC Scooter of hero honda’)

Insert Into VehicleCategory(ID,BikeCategory,ParentID,Description) Values(6,‘TVS Scooty’,2,’75 CC Scooter of TVS’)

Step 3:

Now, if I want all the category and subcategory of “Bike” than what I suppose to do? There may be different ways to fulfill this requirement but I would like to go for new feature introduce in SQL-Server 2005, which is CTE (Common Table Expression)

WITH cte_VehicleCategory AS

(

SELECT ID,BikeCategory,ParentID,Description FROM VehicleCategory Where ID=1

UNION ALL

SELECT v.ID,v.BikeCategory,v.ParentID,v.Description FROM VehicleCategory AS v

INNER JOIN

cte_VehicleCategory AS cv ON v.ParentID=cv.ID

)

Select * from cte_VehicleCategory

Let’s study little bit more about how this magical code worked???

Our CTE name is cte_VehicleCategory, which will get its base records from the first query above the UNION ALL. It will iterate to seek ID’s value in parentid of query below the UNION ALL. Now, as you know that this is recursive query so if you want to create infinite query 😉 than change statement after “INNER JOIN” to “cte_VehicleCategory AS cv ON v.ID=cv.ID”

Reference: Ritesh Shah
Advertisements

2 Responses to “CTE (Common Table Expression) SQL-Server 2005”

  1. Ravi Jethva Says:

    Thanks, This is really helpful article

  2. Ravi Jethva Says:

    Thanks, This is really helpful article


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: