Common Table Expressions in SQL Server 2005

I initially wrote this article for www.adminprep.com but apparently, I myself am having difficulty trying to access the site every now and then so I decided to mirror the content in my blog.

Have you ever wished that Microsoft created a feature in SQL Server 2000 that combines the capabilities of views and derived tables? How about writing recursive queries in T-SQL that does not need to be very taxing? With SQL Server 2005 Common Table Expressions, you can do both with ease. In their simple form, you can think of CTEs as an improved version of derived tables that more closely resemble a non-persistent type of view. Look at CTEs as your derived tables in SQL Server 2000. A CTE can be used in many of the same ways you use a derived table. CTEs can also contain references to themselves. This allows the developer to write complex queries simpler, like recursive queries. CTEs can also be used in place of views. The use of CTEs provides two main advantages. One is that queries with derived table definitions become more simple and readable. While traditional T-SQL constructs that are used to work with derived tables normally requires a separate definition for the derived data such as a temporary table or a table-valued function, using CTEs make it easier to see the definition of the derived table with the code that uses it. The other thing is that CTEs significantly reduces the amount of code required for a query that traverses recursive hierarchies.

To understand what a CTE is all about, let’s first take a look at the syntax to create it in SQL Server 2005.

Syntax

In general form a CTE has the following syntax:

WITH cte_alias(column_aliases)
AS
(
cte_query
)
SELECT *
FROM cte_alias

You provide the CTE with an alias and an optional list of aliases for its result columns following the keyword WITH which usually defines the derived table based on the query definition; write the body of the CTE; and refer to it from the outer query.

To put this in the right perspective, let’s come up with a very simple example. Using the Northwind database, we want to display the employee details along with a column that displays the number of orders that the employee has written. This is done by creating a table on the fly that summarizes this information from the orders table. We then join with this table in the INNER JOIN clause based on employee ID.

WITH Count_Orders(employee_ID, orderCount)
AS
(
SELECT employeeID, COUNT(OrderID)
FROM Orders
GROUP BY employeeID
)
SELECT employeeID, Firstname, Lastname, orderCount
FROM Employees E INNER JOIN Count_Orders
ON Count_Orders.employee_ID = E.employeeID

Now, a lot of people might say that this is quite simple. You can do this in SQL Server 2000 using the concept of derived tables or temporary tables. If you were to write the same query in SQL Server 2000, this is how it would look like.

SELECT EmployeeID, Firstname, Lastname, orderCount
FROM Employees E INNER JOIN
(
SELECT employeeID, COUNT(OrderID)
FROM Orders
GROUP BY employeeID
)
AS Count_Orders (employee_ID, order_Count)
ON Count_Orders.employee_ID = E.emplyeeID

One might argue that these two don’t have that much of a difference. Now, imagine that you need to refer to the same derived table within the query. You would have to repeat the same definition, create an alias for it before you can use it again. As you increase the number of references, your code becomes pretty long but repetitive. With CTEs, you no longer have to do these things again and your code becomes easier to read. You can define multiple CTEs and incrementally build on the earlier CTEs or define new results that are then used later on.

Recursive Queries

Whenever a CTE refers to itself, it is considered to be a recursive query. Recursive CTEs are constructed from at least two queries. One is a non-recursive query, which is also referred to as the anchor member. The other is the recursive query, also referred to as the recursive member. These queries are separated by the UNION ALL operator.

Let’s take a look at a simplified generic form of a recursive CTE.

WITH RecursiveCTE(column_list)
AS
(
-- Anchor Member:
-- SELECT query that does not refer to RecursiveCTE
SELECT ...
FROM some_table(s)_or_view(s)
...
UNION ALL
-- Recursive Member
-- SELECT query that refers to RecursiveCTE
SELECT ...
FROM some_table(s)_or_view(s)
JOIN RecursiveCTE
...
)
-- Outer Query
SELECT ...
FROM RecursiveCTE
...

Let’s extend the example we used to demonstrate how to write recursive CTEs. Looking at the Employees table in the Northwind database, we see that a particular employee reports to another employee. One question we can come up with is, “Who reports to whom?” The Employees table of the Northwind database is designed in such a way that the ReportsTo column is a foreign key field that refers to the primary key field EmployeeID. Thus, we can create a query to answer our question. A sample query using CTE will look something like this.

WITH Managers AS
(
SELECT EmployeeID, ReportsTo
FROM Employees
WHERE ReportsTo IS NULL
UNION ALL
SELECT e.employeeID, e.ReportsTo
FROM Employees e INNER JOIN Managers m ON e.ReportsTo = m.employeeID
)
SELECT * FROM Managers

The sample query contains the elements that a recursive CTE must contain. What’s more is that the code is a lot more readable. To create a similar yet non-recursive query that produces the same result in SQL Server 2000, you might come up with something similar to this code:

DECLARE @rowsAdded int
--table variable to hold accumulated results
DECLARE @managers table
(EmpID int, MgrID int, processed int default(0))

--initialize @managers who do not have managers
INSERT @managers
SELECT EmployeeID, ReportsTo, 0
FROM Employees
WHERE ReportsTo IS NULL

SET @rowsAdded=@@rowcount

--do this while new employees are added in the previous iteration
WHILE @rowsAdded > 0
BEGIN
--mark employee records going to be found in this iteration with
--processed=1
UPDATE @managers SET processed=1 WHERE processed=0

--insert employees who report to employees not yet processed
INSERT @managers
SELECT EmployeeID, ReportsTo, 0
FROM Employees e
INNER JOIN @managers r ON e.ReportsTo = r.EmpID
WHERE ReportsTo <> EmployeeID and r.processed = 1

SET @rowsAdded = @@rowcount

--mark employee records found in this iteration as processed
UPDATE @managers SET processed=2 WHERE processed=1
END

SELECT * FROM @managers

The first thing you will notice is the codes were quite few when using CTEs compared to the usual T-SQL query you will have to create in SQL Server 2000. This enables the developers to write complex queries with ease. You can also use a query hint to stop a statement after a defined number of loops. This can stop a CTE from going into an infinite loop on a poorly coded statement. You do this by including the MAXRECURSION keyword in the SELECT query referring to the CTE. To use it in the previous example

SELECT * FROM Managers OPTION (MAXRECURSION 4)

Given the example above, hierarchical data structures, organizational charts and other parent-child table relationship reports can easily benefit from the use of recursive CTEs. Common Table Expression is just one of those T-SQL enhancements available for SQL Server 2005. CTEs bring us the chance to create much more complex queries while retaining a much simpler syntax. They also can lessen the administrative burden of creating and testing views for situations where the view will not be reused. As our data requirements become more complex, we need the proper tools to deal with them and the new generation of T-SQL is just the right tools that we need. In my next article, I will introduce more T-SQL enhancements in SQL Server 2008 which is similar to how to address problems like this using CTEs. SQL Server 2008 has introduced a new data type called heirarchyid which I will cover soon

Fancy up your reports - SQL Server 2005 Reporting Services:alternating colors on table rows

I'm not a good graphics guy. In fact, I'd hire somebody to do web design if I have a web application project. For me, anything to do with aesthetics is totally out of the picture. I wouldn't want to spend a couple of hours designing and mixing colors and pictures. In reality, anything that has something to do with web requires visual arts. Same thing with reports. You really have to find the right color combination and layout for your reports. I was struggling with how to do this on tables which display results of my dataset queries. All I know is that I can display my resultsets with a single color. I know I can do this in the dataGrid control in ASP.NET 1.1 but I am creating my reports in SQL Server 2005 Reporting Services. After searching thru my favourite (and everybody's probably) search engine, I found the answer. You can use expressions in just about any property in your reports. To do alternating background colors in a table, you can simply use an expression like this: =IFF(RowNumber(Nothing) Mod 2,"color1","color2") where color1 and color2 are the names which you can get from the list of colors I don't guarantee that it will look nice - that depends on your visual aesthetics and how you appreciate color combination. What I did in my case was to extend this expression since I had a different color for my column header to identify the column names. My new expression would be =IIF((RowNumber(Nothing)+1) Mod 2,"color1","color2") so that the new color would start on the second row after the column header instead of the first one. I might be needing this in other functionalities like generating a report using the Matrix control so I better start looking for alternatives as early as now

Embed SQL Server 2005 Reporting Services Reports in your .NET applications using the ReportViewer control in Visual Studio 2005

Picture this - you work with a team, you're in charge of creating the front-end for your .NET applications and you need to have access to SQL Server 2005 Reporting Services reports that have already been developed and currently being used. What would you do? You don't want to recreate those reports, do you? Neither would you want to simlpy create a link from your application to those reports as this might look unprofessional. Well, Microsoft just has the solution to your need. The Microsoft Report Viewer Redistributable 2005 is a freely redistributable control that enables you to embed SQL Server 2005 Reporting Services reports in your .NET 2.0 applications. Whether you are creating ASP.NET or Windows Forms, it's easy to simply drag and drop a control that lets you access an already existing report. This gives you the full capabilities of SQL Server 2005 Reporting Services (well, you are calling it using the tool in the first place) like exporting to PDF, Excel, pictures, etc. You will see this being used by other Microsoft applications like Windows Server Update Services (WSUS) v3.0 where it requires you to have the Report Viewer 2005 Redistributable during deployment. Check out this site for more information on the ReportViewer control

Lost your Show Desktop icon? Try re-creating it back

I always use my Show Dektop icon especially when I've got tons of application window opened. But I never had to recreate the icon until now. A friend of mine asked me how to re-create the Show Desktop icon as he accidentally deleted his permanently. My first instinct was to simply look at the properties of the shortcut on the Quick Launch toolbar. But that didn't give me enough information to recreate the shortcut. All I found out is that it's just a variation of the Explorer.exe command. To re-create your Show Desktop icon, open your favourite text editor (Notepad, TextPad, etc.) Then, type the following text

[Shell]
Command=2
IconFile=explorer.exe,3
[Taskbar]
Command=ToggleDesktop

Save the file with a filename of Show Desktop.scf (now you can name it anything you want like I Wanna See My Desktop as long as the extension is scf). This Microsoft KB article explains in detail how to do it.

Importing Word and Excel as InfoPath 2007 Forms

If you have been working with business forms, you probably have created a few in either Microsoft Word or Excel. One thing to make managing business forms a lot easy is to upload them in your Sharepoint portal. This makes it easy to have a standardized form easily available to the rest of the organization. You can also port them over as InfoPath forms. In Sharepoint 2007, you have the option to use Forms Services instead of individual forms. This makes it easy for users to have a unified and standardized format plus the advantage of binding the form to a backend database for data collection. If you have existing Word or Excel forms, you can easily import and convert them in InfoPath. Just run the Import Form Wizard in InfoPath to convert your existing Word and Excel forms to InfoPath forms - no need to re-create those forms. For more information, check out the Introduction to importing and exporting form data and form templates

When SQL Server backups can't be restored

We always think that having a backup is more than enough to keep us afloat during a disaster. I always say that any backup will be useless unless it is tested. I came across one case where a SQL Server 2000 backup for the master database cannot be restored because it was generated using a different patch set for SQL Server 2000. The backups were generated before applying SQL Server 2000 hotfix 2191. After the patches were applied, a restore was attempted but failed. An error was encountered which specifies that the backup was generated by an earlier version of SQL Server. A best practice approach is to generate backups before and after a patch will be applied. This makes sure that you have valid backups in case something happened. It may be a bit costly as far as disk space is concerned but it sure beats not having a valid backup. It may even save you from spending sleepless nights trying to rollback to the patchset which generated the backups.