The Baker’s Dozen: 13 Productivity Tips for Transact-SQL 2005 Thinking of upgrading to SQL Server 2005? Or are you using SQL 2005 already and you want to learn more about the language features? Then this article may be for you! Microsoft implemented many new features in SQL Server 2005, including an impressive set of language enhancements. From new language statements for SQL-99 compatibility to new features in response to customer requests, Transact-SQL 2005 helps to increase developer productivity. In this article, I’ll cover most of the new language features by posing a statement/scenario and then provide some code samples to show how you can use T-SQL 2005 to address the problem. At the end of the article, I’ll talk briefly about Visual Studio Team Edition for Database Professionals, a product that helps a development team to manage databases. Finally, I’ll give you a sneak preview of some features in the next scheduled version for SQL Server (SQL Server 2008, “Katmai”). Beginning with the End in Mind I speak at community events (MSDN Code Camp, user groups, etc.) at least once a month. One of the more popular sessions (as well as one of my favorites) is called “T-SQL for Developers”. | " |
Recursive queries are powerful-so powerful that you may not always see all their power at once. Next time you think you need to write code to loop through data, consider a CTE and a recursive query instead.
| " |
I’m a big believer in plenty of code samples to demonstrate functionality, and so my goal is to provide a healthy number of code samples for each of the following: - The PIVOT statement
- Common table expressions (CTEs) and recursive queries (part 1 of 2)
- CTEs and recursive queries (part 2 of 2)
- OUTPUT and OUTPUT INTO, to gain immediate access to the system INSERTED and DELETED tables
- Isolation levels (part 1 of 2)
- Isolation levels (part 2 of 2)
- New XQUERY capabilities to handle variable number of selections
- Variable TOP N
- APPLY and table-valued UDFs
- RANKING and PARTITIONING
- New TRY…CATCH capabilities and RAISING errors
- INTERSECT/EXCEPT
- Flexible UPDATE procedures
- All code samples will work using the AdventureWorks database that comes with SQL Server 2005.
Tip 1: PIVOT “You want to query a table of vendor orders and group the order amounts by quarter for each vendor.” Application developers often need to convert raw data into some type of analytical view, such as sales by month or quarter or the brackets of an aging report. Prior to SQL Server 2005, you would often have to examine each row with a CASE statement to place raw data into a column. SQL Server 2005 introduced the PIVOT statement, arguably the most well-known new language feature. PIVOT allows you to (as the name implies) turn rows of raw data into columns. Listing 1 shows a basic example for PIVOT: a query against the Purchase Order tables in AdventureWorks that summarizes order amounts by quarter. Note the syntax for the PIVOT statement. You essentially need to tell PIVOT three pieces of information: - Which column you are pivoting on (OrderTot).
- Which column you want to examine (OrderQtr, from the Quarter Datepart of the OrderDate), to determine how to pivot.
- The possible values of the column you want to examine (the only possible values of a Quarter DatePart: 1, 2, 3, or 4).
SELECT VendorID,
[1] AS Q1,[2] AS Q2,
[3] AS Q3,[4] AS Q4
FROM OrdCTE
PIVOT (SUM(OrderTot) FOR OrderQtr IN
([1],[2],[3],[4])) AS X
A few additional notes on PIVOT: - The list of values in the IN clause must be static. Microsoft’s implementation of PIVOT does not directly support dynamic queries. If you need to determine these values dynamically at runtime, you MUST construct the entire SQL statement as a string and then use Dynamic SQL. If you frequently need to generate PIVOT tables dynamically, you may want to look at GeckoWare’s SQL CrossTab Builder product (http://www.geckoware.com.au/Content.aspx?Doc_id=1002).
- You must specify the column you are PIVOTING on (Ordertot) as a scalar expression (e.g. MAX(), SUM(), etc.).
Note that Listing 1 contained a new language construct….WITH (name). This is a common table expression (CTE), which you can think of as a dynamic view. I’ll cover CTEs in the next few tips. | & | | 
By: Kevin S Goff
Kevin S. Goff, a Microsoft MVP award recipient for 2007, is the founder and principal consultant of Common Ground Solutions, a consulting group that provides custom Web and desktop software solutions in .NET, VFP, SQL Server, and Crystal Reports. Kevin is the author of Pro VS 2005 Reporting using SQL Server and Crystal Reports, published by Apress. Kevin has been building software applications since 1988. He has received several awards from the U.S. Department of Agriculture for systems automation. He has also received special citations from Fortune 500 Companies for solutions that yielded six-figure returns on investment. He has worked in such industries as insurance, accounting, public health, real estate, publishing, advertising, manufacturing, finance, consumer packaged goods, and trade promotion. In addition, Kevin provides many forms of custom training. Contact Kevin at kgoff@commongroundsolutions.net
kgoff@commongroundsolutions.net | Fast Facts | | SQL Server 2005 offers many language features to improve productivity for database developers. This article is primarily intended for those who are now upgrading to SQL 2005, or have recently upgraded and want to know what’s “under the hood”. | |
|