Common Table Expressions (CTE) In SQL 2005

Posted in Labels: | 6:08 AM

Common Table Expressions (CTE) In SQL 2005

      This is one of a good concepts  in SQLServer2005. CTE much useful in Temporary Tables or derived tables.A Common Table Expression returns temporary result set from inside a statement.
Some of the most frequent uses of a Common Table Expression include creating tables on the fly inside a nested select, and doing recursive queries.

Common Table Expressions can be used with DML,DDL

     we have been using TSQL for this long without Common Table Expressions,
Then Why CTE is Required. Although new to SQL Server, Common Table Expressions are part of ANSI SQL 99, or SQL3. Therefore, if ANSI is important to you, this is a step closer.
It is easy to write a query with Good performance.
Easy to understand with simple structure.



SQL Snippet

USE tophat--TOPHAT is my DB
GO

WITH CTE_Name( [Reg No] ,  [Orgin City] ) AS
(
  SELECT reg_no , orig_cnty---Two Columns in my Tophat DB
  FROM prime_reg
)

SELECT *
FROM CTE_Name---This is CTE Name

GO

Read Users' Comments (0)

Subscribe to: Posts (Atom)