Previously I wrote a post about CTE (Common Table Expressions). And Common Table Expressions are valid for a single statement only. But a single statement can use many CTEs. And statement also should be within the same batch.
nn
USE AdventureWorks2012nGOnn--Defining CTEnWITH ctePerson AS(n SELECT * n FROM Person.Person)n --First Statement will run n SELECT FirstName FROM ctePerson n --Second statement will occur an errorn SELECT FirstName FROM ctePerson;nnn
And in my previous post, I have mentioned about many CTEs for one statement. And there is another important note that is to be careful when you are naming CTEs. Take a look on below example.
nn
USE tempdbnGOnn--Creating two tablesnCREATE TABLE cteSampletbl1(id int,name varchar(50)n)nGOnnCREATE TABLE cteSampletbl2(id int,name varchar(50)n)nGOnn--Populating tablesnINSERT INTO cteSampletbl1 VALUES(1,'Satheeq'),n(2,'Preethi'),n(3,'Dinesh'),n(4,'Angelo')nGOnnINSERT INTO cteSampletbl2 VALUES(1,'Shamil'),n(2,'Hasitha'),n(3,'Abhinandana'),n(4,'Susantha')nGOnn/*n This CTE is defined with existing table name without any errorn*/nWITH cteSampletbl2AS(n SELECT * FROM cteSampletbl1)n --This statement uses CTEn SELECT * FROM cteSampletbl2n --This statement uses existing tablen SELECT * FROM cteSampletbl2n--Dropping tablesnDROP TABLE cteSampletbl1nDROP TABLE cteSampletbl2nGOnnn
Below example it lets us create a CTE with a name of an existing table without any warnings or errors. And it gives priority to CTE in the first statement. Then it uses the existing table in the second statement. So it would be a best practice to use a separate prefix for CTEs. Then take a look into the next sample which is taken from Dinesh’s presentation for SQL Server Universe Group.
nn
USE tempdbnGOnn--Creating two tablesnCREATE TABLE cteSampletbl1(id int,name varchar(50))nGOnn--Populating tablesnINSERT INTO cteSampletbl1 VALUES(1,'Satheeq'),n(2,'Preethi'),n(3,'Dinesh'),n(4,'Angelo')nGOnn--This CTE got errornWITH cteSampletbl1AS(n --Same name could not be used as name and in CTEn SELECT * FROM cteSampletbl1) nSELECT * FROM cteSampletbl1n --This CTE don’t have any errornnWITH cteSampletbl1AS(n --full name of object should providen SELECT * FROM dbo.cteSampletbl1)nSELECT * FROM cteSampletbl1nn--Dropping tablesnDROP TABLE cteSampletbl1GOnnn
You can’t use the same table name as the name of the CTE and inside the CTE.
nn
Tags
nn
- n
- SQL
- CTE
- T-SQL
- TSQL
- SQL Server
n
n
n
n
n
n