SQL Server 2019 : Set rowcount

Question

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

Answer 1

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.

Answer 2

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