Quick one today:

In our production code, there are several incidents where “SET ROWCOUNT” was implemented. Over the years, code has evolved with involvement from different developers and they might have made decisions to use this as an immediate fix for the issue at hand. Let’s dig a bit deeper into this feature.

Essentially, SET ROWCOUNT tells Sql Server to stop any further processing once the number is reached. For example, if I set

Any SELECT, UPDATE, DELETE statements following this will stop processing any more records once it hits 1800 record count. On the surface this seems like a cool feature, and it is; But it has some limitations going forward.

• Microsoft recommends that we discontinue using this style as they are planning to stop its affect on DML statements (UPDATE/DELETE/INSERT).
  • Microsoft recommends using TOP statement instead
• If both TOP statement and SET ROWCOUNT are used, SET ROWCOUNT over rides (when ROWCOUNT value is smaller than TOP value)
• This setting comes into play during execution and not at parse time.
• If you want to reset it, then use the statement below.

_Sqltimes