t-sql 2012 cte - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

t-sql 2012 cte

Author

Topic

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2015-03-26 : 14:18:44

In t-sql 2012 that I am listing below, I want to know what part of the following statement is a 'CTE' that is being used for the select
statement,
WITH DailyAttendanceDetail_CTE AS
(SELECT P.custtNumber, i.firstname + ' ' + i.lastName
FROM (SELECT P.custtNumber, i.firstname, i.lastName,i.address, i.citysate, i.zip
FROM test1.dbo.customer p
JOIN test1.dbo.identity i
ON p.id = i.i
)

SELECT P.custtNumber, i.firstname, i.lastName
FROM DailyAttendanceDetail_CTE

Also would you let me know what the purpose is for using a cte?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-26 : 14:26:27

Everthing between DailyAttendanceDetail_CTE AS (
and )

one purpose of a CTE is to make a query with one or more subqueries easier to read. That is:


WITH CTE AS
(
    SELECT ..
)

SELECT ...
FROM CTE

is exactly equivalent to:


SELECT ...
FROM 
(
    SELECT ...
) AS CTE

Also CTEs allow for the creation of recursive queries, something not possible otherwise.

A good discussion is found here:

http://www.sqlservercentral.com/articles/Stairway+Series/122606/

Official docs:

https://msdn.microsoft.com/en-CA/library/ms175972.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources