Common Table Expressions (CTEs) can help improve the readability (and thus the maintainability) of SQL statements without compromising performance. They also make writing recursive code in T-SQL significantly easier than it was in previous versions of SQL Server.

In the October issue of MSDN Magazine, John Papa begins by describing how CTEs work and what scenarios they can be used to address, then discusses the advantages of using CTEs versus using traditional T-SQL constructs, such as derived tables, views, and custom procedures.

Explore the sample code online, or grab the code download to explore on your computer. You can also explore SQL Server development and management in the MSDN SQL Server 2005 Virtual Labs.

You can get more Data Points in the MSDN Magazine archives, or subscribe to the RSS feed for notification of new columns.

Enjoy!

Terrence Dorsey