SQL SERVER – Common Table Expression (CTE)

  • Common Table Expression (CTE)

               CTE  can be used as a temporary storage like temp tables,variables.A CTE is able provides a facility to refer itself.A recursive CTE is one which an intial CTE is executed repeatedly to return the subset of data until complete resultset is obtained.

  • Structure of the CTE

A recursive CTE consists of three elements(As per MSDN):

  1. Invocation of the routine.The first invocation of the recursive CTE consists of one or more CTE_query_definitions joined by UNION ALL, UNION, EXCEPT, or INTERSECT operators. Because these query definitions form the base result set of the CTE structure, they are referred to as anchor members.CTE_query_definitions are considered anchor members unless they reference the CTE itself. All anchor-member query definitions must be positioned before the first recursive member definition, and a UNION ALL operator must be used to join the last anchor member with the first recursive member.
  2. Recursive invocation of the routine.The recursive invocation includes one or more CTE_query_definitions joined by UNION ALL operators that reference the CTE itself. These query definitions are referred to as recursive members.
  3. Termination check.The termination check is implicit; recursion stops when no rows are returned from the previous invocation.

Example for CTE:

Consider a table containing EmployeeDetails with EmpID,ManagerID.EmployeeName,Designation.Here I want to get the result based on Manager Herirachy.Now we can create a table with the following T-SQL code.


CREATE TABLE [dbo].[EmployeeDetails]

  (

     [EmpID]        [INT] IDENTITY(1, 1) NOT NULL,

     [EmployeeName] [VARCHAR](50) NULL,

     [ManagerID]    [VARCHAR](50) NULL,

     [Designation]  [VARCHAR](50) NULL,

     CONSTRAINT [PK_EmployeeDetails] PRIMARY KEY CLUSTERED ( [EmpID] ASC )WITH (

     pad_index = OFF, statistics_norecompute = OFF, ignore_dup_key = OFF,

     allow_row_locks = on, allow_page_locks = on) ON [PRIMARY]

  )

ON [PRIMARY]

GO 

We can insert some data into the table,here for example I am using an IT company Herirachy

 

INSERT INTO dbo.employeedetails

VALUES      (‘E1′,NULL, ‘CheifTechnologyOfficer’),

            (‘E2′,1, ‘ProjectManager’),

            (‘E3′, 2,  ‘TeamLead’),

            (‘E4′,1,‘Sr.NetworkAdministrator’),

            (‘E5′, 2, ‘DBA’),

            (‘E6′,3, ‘Developer’) 

I am issuing  Select * from dbo.employeedetails


 

 

 

 

 

we  can use CTE to return the hierarchical structure of  company.just look into this.

WITH cte(empid, employeename, designation, managerid, [Level] )

     AS (

        –Defining Anchor Member

        SELECT empid,

               employeename,

               designation,

               managerid,

               0 AS [Level]

        FROM   dbo.employeedetails

        WHERE  managerid IS NULL

         

         UNION ALL

         –Recursive Member Defenition

         SELECT e.empid,

                e.employeename,

                e.designation,

                e.managerid,

                cte.[Level] + 1

         FROM   dbo.employeedetails AS e

                INNER JOIN cte

                  ON e.managerid = cte.empid)

SELECT * FROM   cte

SQL SERVER – DIFFERENCE BETWEEN CAST AND CONVERT(Cast vs Convert)

Both cast and covert serves the same purpose i.e. convert a data type to another.

  • Cast
  1. Cast is  ANSII Standard
  2. Cast cannot be used for Formatting Purposes.
  3. Cast cannot convert a datetime to specific format
  • Convert
  1. Convert is Specific to SQL SERVER
  2. Convert can be used for Formatting Purposes.For example Select convert (varchar, datetime, 101)
  3. Convert can be used to convert a datetime to specific format

SQL SERVER – DIFFERENCE BETWEEN IN AND EXISTS

During one of my interviews, I faced this question “what is the difference Between IN and EXISTS? Answer: Consider this example I have two tables TableA and TableB, where TableA contains  Name of an Employees and TableB Contains the address code of the   Employees,both can be joined by ID and ID2.

  • IN
  1. Returns true if specified value matches any value in the sub query or a list.
  2. The sub query will run first and then only outer query.

Example:

SELECT id,
[Name]
FROM dbo.tablea
WHERE id IN (SELECT id
FROM dbo.tableb)

  • EXISTS
  1. Return true if sub query contain any rows.
  2. The Outer query will ran first and then only sub query.

Example:

SELECT id,
[Name]
FROM dbo.tablea AS a
WHERE EXISTS (SELECT id2
FROM dbo.tableb
WHERE id2 = a.id)

Hope everyone like this this article.Please give views and suggestions