SQL 2005 - Examining Enhancements to the TOP keyword
The addition of the CLR to the SQL Server Engine has no doubt garnered the most attention of the new features of SQL Server 2005. There are some important additions/enhancements to the T-SQL language for SQL Server that are important as well. I will start by examining some of the new features of the TOP keyword.
TOP Enhancements
I am pretty excited about this one. In SQL 2000, you could use this statement in a Select statement to specify either the TOP n rows or the TOP n Percent of the entire Select statement resultset. The first change in SQL 2005 is that the n number of rows or percentage of rows can now be specified by using a parameter or a subquery. So instead of having the following Select statement:
USE AdventureWorks Select TOP 5 * From Sales.SalesOrderHeader Order By TotalDue DESC
I can now specify the number of rows as a parameter.
Select TOP (@N) * From Sales.SalesOrderHeader Order By TotalDue DESC
Similarly, I can do the same and use a parameter with PERCENT as well.
Select TOP (@N) PERCENT * From Sales.SalesOrderHeader Order By TotalDue DESC
In addition, the n number of rows can also be a select query.
Create Table [dbo].[UserPreferences] ([UserName] [varchar] (50), [TopCount] [int])
Insert Into [UserPreferences] VALUES ('Andrew', 12)
Select TOP (Select TopCount From [UserPreferences] where UserName = 'Andrew')
* From Sales.SalesOrderHeader Order By TotalDue DESC
So, “What’s the point”
I, for one, know that I have worked on a number of applications where I need to fulfill the requirement of returning a dynamic number of rows because the number of rows is configurable based on user preferences or it can be specified through the UI. In those applications you could either return the entire result set and then only display the specified number of rows or you could construct a dynamic query specifying the number of TOP rows to return.
With the ability to use a parameter to specify the TOP number of rows to return you just code your Stored Procedure to accept the number of rows to return.
For example:
Create Procedure sproc_TopSales
(@State char(2), @TopNum int)
AS
Select Top @TopNum, CustomerName From Customers Order By YearlySales
By doing this you have now given your Stored Procedure a nice amount of flexibility and reduced the need for dynamic SQL or sending more data back to the client. Talking about the issue of returning a dynamic number of rows, my next post will talk about another new T-SQL feature that will help with this.
You can also use the TOP statement in both Delete and Update Statements. So assuming I have an Orders Table defined as such:
CREATE TABLE Orders (OrderID int, OrderAmount decimal(28,12))
So, I can give a discount on the TOP 5 Orders in my system.
Update TOP (5) Orders Set Amount = (Amount * .95)
Now, this is not a really meaningful statement since you cannot control the Order By in the clause and what SQL Server uses to determine the TOP rows. So if I want to update the TOP 5 rows according to Order Amount I would have to use a subquery like this to make this update more meaningful. Now you can do this same statement in SQL 2000 using the subquery, but in SQL 2005 you can use all the same TOP enhancements that I talked about above such as parameters in the subquery making it much more powerful.
Update Orders Set OrderAmount = (OrderAmount * .95)
From (Select Top 5 Percent OrderID From Orders Order By OrderAmount DESC) AS TopOrders Where Orders.OrderID = TopOrders.OrderID
I am looking forward to posting a bit more frequently, mostly about SQL Server topics, but I am sure I will throw in a few other topics as well.