CTE (Common Table Expression) in SQL-Server 2005

one of the beautiful feature of SQL-Server2005 is CTE and I am going to throw a little light on that part here.

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

By Ritesh Shah   Popularity  (1476 Views)
Biography - Ritesh Shah
I am Ritesh Shah and currently working as IT Project Leader in one KPO of Environment Laboratory located at NJ, USA area. I have been working with Microsoft technology since last 8+ years and having sound knowledge in SQL-Server, Asp.NET and C#. I have been working as a Project Leader & Pricipal Database Administrator in my current job since 3+ year. You can further read my blogs at SQLHub.Com