Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

TOP in SQL Server 2005

By Andy Warren,

Recently someone asked me about what had become my feature in SQL 2005 and if it was the feature I thought it would be. That's really a pretty good question since it's easy to get excited about the 'wow' and overlook the more mundane. Going into the release my favorite feature that I was looking forward to was the ability to modify replicated columns (if you've done replication I'm sure you share my enthusiasm for that feature!). My path has taken a twist and turn since then so that I don't use replication daily. Instead I find that out of all the new things the one I seem to use most is Top N in updates and deletes (followed closely by the output clause). No, not exciting, but definitely practical.

Assuming you've been using SQL 2000 for a while you know that you can use Top N in selects, either as a number or a percentage. For example, you might write this:

select top 100 * from sometable

What you couldn't do was parameterize it. It only worked with selects too. If you wanted to restrict the number of rows affected by a select at runtime, or restrict the rows affected by an update or delete you had to explicitly describe the rows using a join or where clause, or you could cheat a bit by using ROWCOUNT, like this:

set rowcount 1000
delete from table where somefield=1
set rowcount 0

It worked well enough (and still does in SQL 2005). The downside was a bit of a clunky syntax and it created the risk that somehow rowcount did not get reset back to 0, not a good thing since it is a session based setting. Forgetting to change it back would mean that all the statements would be restricted to 100 rows. Not the best thing if you're trying to select all employees that need a paycheck. SQL 2005 let's you use Top N in selects, plus updates and deletes - but the syntax has some quirks as you'll see in a second.

For select statements you must specify parentheses if you are passing a parameter, otherwise they are optional

Select Top (@TopN) * from employees
Select Top 10 * from employees

When doing an update or delete, you have to use the parentheses in both cases:

Delete Top (@TopN) from employees
Delete Top (10) from employees
update Top (@TopN) employees set payrate = payrate * 1.1
update Top (10) from employees set payrate = payrate * 1.1

So a simple rule to follow would be to always wrap the 'N' in parentheses!

The most common reason I've had to do restrict the number of rows has been when doing operations against millions of records. Updating millions of records can not only cause blocking, but it can bloat your transaction log even if you're in simple recovery mode (because it has to log the entire transaction). Doing the update in batches doesn't guarantee no blocking of course, but it should reduce the time the query takes to complete. It often takes some experimenting to figure out the optimum batch size. I suggest making sure that the query will complete in less than 30 seconds, as that is typically the default timeout for a database action. Even if you're in full recovery mode there is an advantage to doing chunks. If the size of your transaction won't fit into the current virtual log segment the log will grow (or you'll come to a halt!). By doing batches and running log backups you can easily keep the log from growing to a huge size.

I've shared my favorite feature and an application for it as well - how about writing about your favorite feature and submitting it for the SQL world to see?

Total article views: 17026 | Views in the last 30 days: 16
 
Related Articles
FORUM

select case Insert, Update

Have problem using select case with update and delete

FORUM

select [Employee_ID] = EmpID from dbo.Employee GROUP BY [Employee_ID]

select [Employee_ID] = EmpID from dbo.Employee group by [Employee_ID] --Group by error

FORUM

Trigger - accessing updated and deleted

Can't bind to updated and deleted tables

ARTICLE

Selecting from hierarchies like Managers and Employees

Chuck Hoffman shows a technique using sets for selecting records from hierarchies such as Manager / ...

FORUM

Trigger - accessing updated and deleted

Can't bind to updated and deleted tables

Tags
sql server 2005    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones