Moving Forward with SQL 2008


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. 

Published by

Andre

Originally, this was a pretty darn boring post. Kinda like... Well I won't go there. Perhaps its still on the robotic side but... I could say I like music. Safe, generic and non-comittal. Or, I could say that I've been having a blast tuning up my old guitars, getting blisters on my fingers and turning the amp up past 2. Amazaing what a little overdrive and a half pressed wahwah pedal can do for a sound. Get that cool "Money for Nothin" vibe happening. I get a real kick out of reading old Sci Fi. Reading Asimov's vision about the future is really entertaining now. When he wrote much of the material, the items that were futuristic were day to day tools I used in the early part of my career. Microfiche and the like. I also remember that upstart Microsoft and MS DOS...and can you say Lotus 123? So maybe this is a little better than "I like to read and play music". My career is focused on Team Leadership and Technology. Both share the attributes of continual growth and education. Currently, I manage a team of 4 programmers as direct reports. I've been in this role since 08/2007. Prior to that, I was the team lead (on site) for integration with the customer. Customers ranged from local government to manufacturing and medical. Teams ranged in size from one to six additional team members. On the other side is technology. I've been keeping current with .net technologies focusing on C# and Sharepoint (2007/2010). Specialties Team Building and Management Technical Staff Recruiting Microsoft Visual Studio 7 through 2010 (VB.NET and C#.NET) Microsoft SQL Server 6.5-2008R2 (DTS, TSQL, SSIS, SSAS, and SSRS ) SharePoint 2010 (Office Integration, InfoPath, Site Management and planning) Windows Server 2008 R2 AD DS PowerShell Techology analysis Puchasng and working with vendors Microsoft licensing management and compliance Business Systems Analysis Traning plans Mentoring Training coordination.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s