Ever needed to query a self referencing table to build out a tree of data, such as a menu? Rob Conery presents a very useful and fast way to do so using SQL Server 2005.
Read Recursive Queries in SQL 2005
When most developers develop database code (including tables, views, stored procs and functions, they tend to develop in a development environment and then migrate those changes later to other servers (staging, production, whatever). While some of us are good about keeping track of those changes, the majority of us are not.
Common Table Expressions, or CTE, are a new construct introduced in Microsoft SQL Server 2005 that offer a more readable form of the derived table that can be declared once and referenced multiple times in a query. Moreover, CTEs can be recursively defined, allowing a recursive entity to be enumerated without the need for recursive stored procedures. In this article we'll examine the benefits, uses, and syntax of both recursive and non-recursive CTEs. Read on to learn more!
In this article we'll look at two ways to improve Scott's method. The first approach uses a table variable (just like Scott's), but utilizes the SET ROWCOUNT command to reduce the number of records read and inserted into the table variable. The second technique more cleverly uses SET ROWCOUNT to provide an even more efficient approach than the first.
SQL Server 2005 offers a number of new features over its predecessor, including many features aimed at making working with databases more like writing .NET application code. For example, in SQL Server 2005, stored procedures, triggers, UDFs, and so on can be written using any .NET Framework programming language (such as Visual Basic or C#). Another feature, and the focus of this article, is SQL Server 2005's support for TRY...CATCH blocks.
Looks like service pack 1 of SQL Server 2005 (already?) will include the Management Studio with SQL Server Express.