Archive

Posts Tagged ‘T-SQL’

Moving Forward with SQL 2008

December 2, 2011 Leave a comment

Who hasn’t messed around with Temporary Tables?  Ok, maybe you haven’t but I have.  Sometimes, programming away in SQL you need that temporary data set that needs to go away after everything is done and gone.  You just need it for the life of the proc and once it has run its course… bye bye.

Sure there’s some overhead and code using create or insert.  Then at the end, making sure to let your objects go.  Well another way to do this is with Common Table Expressions or CTE’s.  Pretty cool.

OverView

Start off with something like:

WITH tmpCustIdName(CustomerID, CompanyName)
as
(
    SELECT     CustomerID, CompanyName
    FROM         Customers
)

select * from tmpCustIdName

The CTE is tmpCustIdName(CustomerID, CompanyName)

Didn’t have to define the column types or really much of anything.

The resulting query from good old Northwind returns

image

Yea, I know.  Folks use adventureworks a lot but it’s what I happened to have put on this server.

Because it is scoped to the procedure, other procs won’t find it.  For example

exec sp_help Customers Returns….

Column_name    Type    Computed    Length    Prec    Scale    Nullable    TrimTrailingBlanks    FixedLenNullInSource    Collation
CustomerID    nchar    no    10                      no    (n/a)    (n/a)    SQL_Latin1_General_CP1_CI_AS
CompanyName    nvarchar    no    80                      no    (n/a)    (n/a)    SQL_Latin1_General_CP1_CI_AS

exec sp_help tmpCustIdName Returns…

The object ‘tmpCustIdName’ does not exist in database ‘Northwind’ or is invalid for this operation.

Derived columns work just as well

WITH tmpOrderById
As
(
    SELECT     OrderID, CustomerID, Year(OrderDate)as OrderYear
    FROM         Orders
)

Select CustomerID, count(*) as OrdersByYear from  tmpOrderById
Group By CustomerID, OrderYear

Returns:

image

 

Recursive

Starting out with an employee table, you want the list returned by employee name and who they report to.  We start out with this:

SELECT [EmployeeID]
      ,[LastName]
      ,[FirstName]
      ,[ReportsTo]
  FROM [Northwind].[dbo].[Employees]

Which Returns:

image

First grab the anchor member.  In this case, the first record works since Nancy reports to Andrew and Andrew is the top of the chain.

Select  EmployeeID, FirstName + ‘ ‘ + LastName as EmployeeName, ReportsTo
From Employees where EmployeeID = 1

Returns

image

Add in the CTE  the query looks like:

With recReportingTree(EmployeeID, EmployeeName, ReportsTo)
AS
(
Select  EmployeeID, FirstName + ‘ ‘ + LastName as EmployeeName, ReportsTo
From Employees
Union All
Select  e.EmployeeID, e.FirstName + ‘ ‘ + e.LastName as EmployeeName, e.ReportsTo
From Employees as e
Inner Join recReportingTree r on e.EmployeeID = e.ReportsTo
)
Select * from recReportingTree

Which returns:

image

Almost there… using the CTE in the final Query you get:

With recReportingTree(EmployeeID, EmployeeName, ReportsTo)
AS
(
Select  EmployeeID, FirstName + ‘ ‘ + LastName as EmployeeName, ReportsTo
From Employees
Union All
Select  e.EmployeeID, e.FirstName + ‘ ‘ + e.LastName as EmployeeName, e.ReportsTo
From Employees as e
Inner Join recReportingTree r on e.EmployeeID = e.ReportsTo
)

Select r.EmployeeName as Employee,  e.FirstName + ‘ ‘ + e.LastName  as Boss
From recReportingTree as r Inner Join
Employees as e on r.ReportsTo = e.EmployeeID;

Which then returns:

image

For something this small, I would not use this approach.  The query execution plan for this looks like:

image

Hmmmmm….

To get the same results, I could write the same query as:

SELECT     e.EmployeeID, e.FirstName + ‘ ‘ + e.LastName AS EmployeeName, e1.FirstName + ‘ ‘ + e1.LastName AS BossName
FROM         Employees AS e INNER JOIN
                      Employees AS e1 ON e.ReportsTo = e1.EmployeeID

Which Returns:
image

and it’s execution plan looks like:
image

Summary:

For just doing a simple recursive query like the above, it is much more efficient to just write out the SQL.  Now, if I had a longer process where the need to have objects in memory and pass those collections into later steps in the procedure I would use it. 

Advertisements
Categories: Microsoft, Programming, SQL Server Tags: