TOP Enhancements in Yukon
In previous versions of SQL Server TOP allowed you to limit the number of rows returned as a number or percentage in Select Queries. I use this all the time. It has gotten more flexible in Yukon where TOP can be used in Delete, Update and Insert queries. What is even cooler is that Yukon will allow you to specify the number of rows (or percent) by using variables or subqueries. I love the ability to dynamically set the TOP, so much that I have started writing Stored Procedures that accept a NumberofRows parameter like so:
Create Procedure usp_SEL_ReturnTopOrders
@NumberofRows int
As
Select TOP (@NumberofRows) OrderID
From Orders
Order By OrderID
Executing the stored procedure is easy, just pass in how many records that you want (in this case it is 100):
usp_SEL_ReturnTopOrders 100
Using a subquery can be very powerful when you are doing things on the fly. A real simple example to demonstrate the concept is show here, we are getting the TOP n customers based on how many records we have in our Employees table:
Select TOP (Select Count(*) from Employees) *
See my article on SQLJunkies.com.
