SET ROWCOUNT functionality changes

Lately it seems to me like I need to put some time and effort into actually reading the updated versions of the Books Online to learn about what I have missed, either as changes coming, or changes that have occured. Take for instance SET ROWCOUNT. I learned how to use this in SQL 2000, and it was by someone else, so I have never actually read the BOL entries for it, until recently.

If you look back at my first post on the Transaction Log - Managing Size, one of the examples I posted for batched deletes uses SET ROWCOUNT to limit the size of the delete batches. This is actually a commonly posted example on the forums, and other places online, and in answering a question on the Newsgroups, I posted this same example as a response. Not long after another person on the Newsgroups, Plamen Ratchev, who pointed out that SET ROWCOUNT will cease to function for INSERT, DELETE, and UPDATE in a future release of SQL Server.

No problem, although this was news to me, so I took a peak at the SQL Server 2008 BOL topic for SET ROWCOUNT and sure enough it is documented in there:

Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server. Do not use SET ROWCOUNT with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. Also, for DELETE, INSERT, and UPDATE statements that currently use SET ROWCOUNT, we recommend that you rewrite them to use the TOP syntax.

So I looked back in time to see where this was introduced, and it actually is listed in the SQL Server 2000 BOL topic as well. So it must be being carried forward due to compatibility issues and the amount of code that could be potentially impacted because this really is a common thing to find online or in code. I did a quick search of the code in some of our vendors databases, and almost every one that does a purge job uses SET ROWCOUNT in them. Not to worry, I was nice and emailed a few of them that I had contacts with the DBA support/development staff members to let them know about it so they can update it in a future service pack if they choose.