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.

ADO/ODBC connection string for SQL Server 2008

If upgrading SQL Server from 2005 to 2008, one thing to note is that the provider name has changed for SQL Server 2008. The provider portion of the string in both ADO and ODBC connection strings should be either

Provider=SQLNCLI10

or

Provider=SQLNCLI10.1

depending on which version of SQL Native Client is installed.

http://blogs.msdn.com/b/mattm/archive/2007/06/07/sql-native-client-10-provider-name-change.aspx

http://connectionstringexamples.com/staticpages/index.php?page=20081229061112484

.NET applications will be unaffected as you do not need to provide the provider name in the connection string when using the .NET provider.