SQL Server 2019 : Set rowcount

sakuraime 2,321 Reputation points
2021-08-06T13:27:38.297+00:00

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
    2021-08-09T02:49:51.323+00:00

    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,
    Seeya


    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
    2021-08-06T13:43:23.137+00:00

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

    CREATE TABLE #temp(a int NOT NULL)
    go
    SET ROWCOUNT 4
    INSERT #temp(a)
      SELECT object_id FROM sys.objects
    go
    SET ROWCOUNT 0
    DROP TABLE #temp