Tip 10: SQL Server 2005 TOP N
Anyone who has written SQL
TOP N queries where
N is a variable knows that in SQL 2000, you had to either use dynamic SQL, or set the
ROWCOUNT (and reset it afterwards). Why? Because SQL 2000 treated the
N as a literal.
-- Dynamic SQL to implement variable TOP N
DECLARE @nTop int
SET @nTop = 5
DECLARE @cSQL nvarchar(100)
SET @cSQL = N'SELECT TOP ' +
CAST(@nTop AS VARCHAR(4)) +
' * FROM ORDERS ORDER BY Freight DESC'
EXECUTE SP_EXECUTESQL @cSQL
-- Setting ROWCOUNT to implement variable TOP N
DECLARE @nTop int
SET @nTop = 5
SET ROWCOUNT @nTop
-- Get the top 5 with the most Freight
SELECT * FROM Orders ORDER BY Freight DESC
-- Set back to 0
SET ROWCOUNT 0
Fortunately, Microsoft enhanced the implementation of
TOP N in SQL Server 2005 so that developers can treat the
N as a variable, directly in the SQL statement.
-- New implementation in SQL Server 2005
DECLARE @nTop int
SET @nTop = 5
SELECT TOP (@nTop) * FROM Orders
ORDER BY Freight DESC
-- The N can even be the result
-- of another function
SELECT TOP( SELECT COUNT(*) FROM SHIPPERS)
* FROM ORDERS
Developers can also use variable
TOP N in
UPDATE,
INSERT, and
DELETE statements!
DECLARE @nTop int
SET @nTop = 100
UPDATE TOP (@nTOP) Orders
SET Freight = Freight * 1.1
Tip 11: SQL Server 2005 PIVOT
In the March/April 2005 issue of
CoDe Magazine, I devoted a Baker's Dozen article to productivity tips for T-SQL 2000. One of the tips presented code to build a result set for an accounting aging report, by using a
CASE statement to place overdue invoice rows into aging bracket columns based on date range.
In essence, the example turned, or
pivoted, rows into columns based on some criteria-in this instance, date range. While using
CASE statements remains a valid approach, SQL Server 2005 provides new
PIVOT capabilities to simplify the process.
Listing 1 demonstrates a full example of the new
PIVOT capability. In the example, you calculate the number of overdue days between each invoice and an "as-of" date, and place the invoice amount into the corresponding bracket with the
PIVOT statement.
Tip 12: Recursive Queries and Common Table Expressions in SQL Server 2005
One of the more interesting new capabilities in SQL Server 2005 is the ability to build recursive queries. As the name implies, recursive queries actually query against their own results as part of the entire process. For example, you might query against hierarchical data to extract a variable number of parent-child relationships.
Let me demonstrate recursive queries and common table expressions (CTEs) with a simple example.
Listing 2 shows two simple examples of querying up and down a hierarchy. The process involves two parts. The main or "anchor" query pulls the initial result set into a CTE. The CTE in many ways resembles a derived table. The second part recursively queries the CTE for either parents or children, depending on how you write the query. By default, SQL Server 2005 allows 100 levels of recursion, though you can configure this threshold.
Tip 13: APPLY the Results of a Table-Valued UDF in SQL Server 2005
I have to admit, this is one of my favorite new features in T-SQL 2005. The name perfectly describes what it does-it allows a developer to directly
APPLY the results of a table-valued UDF in a SQL
SELECT statement, without need for temporary tables.
While T-SQL doesn't lend itself to the level of program modularity that C# or Visual Basic developers enjoy, the new
APPLY operator improves the integration of reusable table-valued UDFs with the different stored procedures that call them.
Let's consider the following table-valued UDF, which returns a table variable of the TOP N orders for a given customer, based on Order Amount.
CREATE FUNCTION [dbo].[GetTopNOrders]
(@CustomerID AS varchar(10), @nTOP AS INT)
RETURNS TABLE
AS
RETURN
SELECT TOP(@N) OH.OrderID, CustomerID, OrderDate,
(UnitPrice * Quantity) as Orderamount
FROM Orders OH
JOIN [dbo].[Order Details] OD
ON OH.OrderID = OD.OrderID
WHERE CustomerID = @CustomerID
ORDER BY ORDERAMOUNT DESC
GO
Now suppose you want to query against the entire customer database, and for each customer, run the UDF to return the TOP N orders. SQL Server 2005 allows you to APPLY the results of the UDF directly against a query into the customer file.
DECLARE @nTopCount int
SET @nTopCount = 5
SELECT TOPOrd.CustomerID, TOPOrd.OrderID,
TOPOrd.OrderDate, TOPOrd.OrderAmount
FROM Customers
CROSS APPLY
DBO.TopNOrders(Customers.CustomerID,
@nTopCount) AS TOPOrd
ORDER BY TOPOrd.CustomerID,TOPOrd.OrderAmount DESC
On the Menu Next
As stated at the beginning of the article, this is the first of a two-part series on data handling capabilities. In the next issue, part two will cover .NET generics as well as the new ObjectDataSource tool in ASP.NET, and some more examples in T-SQL 2005. Stay tuned!
Closing Thoughts
You can find the entire source code on
my website. For additional information, check out
my blog.
Have you ever submitted something (an article, a paper, some code, etc.) and thought of some good ideas
after the fact? Well, I'm the king of thinking of things afterwards. Fortunately, that's the type of thing that makes blogs valuable. Check my blog (www.TheBakersDozen.net) for follow-up tips and notes on Baker's Dozen articles…and maybe a few additional treats!
Previous Page
