Click here to Skip to main content
Click here to Skip to main content

CTE Query in SQL Server

, 20 Jan 2014 CPOL
Rate this:
Please Sign up or sign in to vote.
CTE Query for optimizing complexity of query in sql server

Introduction

Common Table Expressions (CTE) query is very useful to reduce query length as well complexity.

Generally we are using Joins for retrieving records from multiple tables, It is difficult and complex little bit than CTE queries.

A common table expression (CTE) is a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. CTE query returns us a single query from multiple joins query so we can easily deal with it and get more expected result in less effort.

Using the code

It is very easy to write CTE query.

A Common Table Expression defines with three components.

  1. CTE name after WITH phrase
  2. Column list (It is not mandatory)
  3. Final query (appears within parentheses after the AS keyword)

Let see Format of CTE query

WITH CTEname(Columnlist optional) AS(..sql query..)
SELECT * FROM CTE name

it returns all columns of sql query which has been written in parenthesis..

e.g.

WITH SampleData AS(SELECT e1.column2 AS EmpName,e2.column2 AS Department FROM table1 e1 INNER JOIN table2 e2 ON e1.Column3=e2.column1) 
select * from SampleData  

it returns two columns having name EmpName and Department and it act like single and simple query.

We can use multiple CTE query as well

with samp as
(select 'Nirav' as Text1 ,'Prabtani' as Text2)
    ,samp1 as(
            select * from samp
            union
            select 'Code' as Text1 ,'project' as Text2
         )
select * from samp1  

we can define column name as well like this.

WITH CTEname (columnlist) as (....) 
 with samp as
(select 'Nirav' as Text1 ,'Prabtani' as Text2)

    ,samp1(MyColumn1,MyColumn2) as(
            select * from samp
            union
            select 'Code' as Text1 ,'project' as Text2
         )
select * from samp1  

This query returns output of both queries.. 

Points of Interest

I have retrieved dates between date interval from first date to second date like this..

 WITH CTEQuery AS (
			  SELECT CAST('23 Mar 2014' AS DATETIME) AS dt
			  UNION ALL
			  SELECT DATEADD(dd, 1, dt)
			   FROM CTEQuery s
			   WHERE DATEADD(dd, 1, dt) <= CAST('26 Jun 2014' AS DATETIME)
			   )
select * from CTEQuery 

It returns all the date row wise between 23 Mar 2014 to 26 Jun 2014.... Smile | :)


History

  • 20 Jan 2014, initial level.. 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Nirav Prabtani
Web Developer Satva Infotech
India India



Nirav Prabtani

I am a software engineer at Satva Infotech, Database Architect and Designer /Technical Architect/Analyst
Programmer in Microsoft .NET Technologies & Microsoft SQL Server with more than
2 years of hands on experience.

I love to code....!!! Smile | :)


My recent past includes my work with the education domain as a technical business
requirement analyst, database architect & designer and analyst programmer; just
love my involvement with the world of knowledge, learning and education and I think
I know quite well what I want to do in life & in my career. What do I like? Well,
ideation, brainstorming, coming up with newer and more creative ways of doing things;
each time with an enhanced efficiency. An item in my day's agenda always has a task
to look at what I did yesterday & focus on how I can do it better today




Contact Me

Nirav Prabtani


Mobile : +91 738 308 2188

Email : niravjprabtani@gmail.com

My Blog:
Nirav Prabtani

 
Follow on   Twitter   LinkedIn

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.150327.1 | Last Updated 20 Jan 2014
Article Copyright 2014 by Nirav Prabtani
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid