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?
