SQL Server 2019 : Set rowcount

sakuraime 2,321 Reputation points

May I know SQL Server 2019 database CL 150 : Set rowcount still have effect on insert , update and delete ?thanks

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,899 questions
0 comments No comments
{count} votes

Accepted answer
  1. Seeya Xi-MSFT 16,451 Reputation points

    Hi @sakuraime ,

    Quote from this part about TOP,

    Use TOP (or OFFSET and FETCH) instead of SET ROWCOUNT to limit the number of rows returned. These methods are preferred over using SET ROWCOUNT for the following reasons:

    • As a part of a SELECT statement, the query optimizer can consider the value of expression in the TOP or FETCH clauses during query optimization. Because you use SET ROWCOUNT outside of a statement that runs a query, its value can't be considered in a query plan.

    And you can find related dynamic information in Features deprecated in a future version of SQL Server.
    SET ROWCOUNT is in the last row of this table.

    Set rowcount still have effect on insert , update and delete ?

    Before it was deprecated, the answer is yes. But it is still recommended to use TOP instead of it as much as possible.

    Best regards,

    If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 102.3K Reputation points

    Why ask? You would have gotten the answer if you had tested yourself.

    CREATE TABLE #temp(a int NOT NULL)
    INSERT #temp(a)
      SELECT object_id FROM sys.objects
    DROP TABLE #temp