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