How to use a CTE to select the first n rows of a referenced table for every row

The Common Table Expression (CTE) feature introduced in SQL Server 2005 provides some very useful querying extensions (albeit these are obviously not ANSI-92 compliant). A CTE takes the following form:

WITH cte AS
(
SELECT col1
FROM dbo.tbl1
WHERE...
)
SELECT col1
FROM dbo.cte
WHERE...
GO

One useful adjunct to the CTE is the ROW_NUMBER function in combination with the PARTITION BY clause. This allows you to write a query returning one or more rows of a subquery without resorting to complicated correlated subqueries and performance killing TOP n predicates. For example, say you want to pull for every row in a parent table the first row in a child table. You can write this query using a CTE as follows:

WITH firstChild AS
(
SELECT ParentID, ChildID,
ROW_NUMBER() OVER(PARTITION BY ParentID ORDER BY ChildID ASC) AS rn
FROM dbo.tblChildren c
)
SELECT p.ParentID, fc.ChildID
FROM dbo.tblParents p
INNER JOIN firstChild fc ON p.ParentID = fc.ParentID AND fc.rn = 1;
GO

The performance of this query is blindingly fast because ROW_NUMBER is being performed in memory and has no disk IO cost. You can also use this technique to generate high performance crosstab queries.

Searching computed column definitions

Computed columns are not ANSI-92 compliant (i.e. they are a proprietary extension) and are therefore not included in the INFORMATION_SCHEMA views. To search for a string in a computed column in SQL Server 2005 use the syscolumns and syscomments system tables. For example:

SELECT object_name(cl.id) AS [Object Name], name AS [Column Name],
text AS [Definition]
FROM syscolumns cl
INNER JOIN syscomments cm
ON cl.id = cm.id
AND cm.number = cl.colid
WHERE iscomputed = 1
AND text LIKE '%search_string%'


SQL Server 2005 connection problems

If queries on a SQL Server 2005 instance suddenly run very slowly and/or time out, check that the TCP/IP protocol for the instance is enabled and Named Pipes is disabled. (In SQL Server Configuration Manager, select SQL Server 2005 Network Configuration.) The server instance will need to be restarted for the changes to take effect.