SQL Server's ROWCOUNT keyword

Because looking at Oracle, I always thought SQL Server was stupid.

It's rumored that SQL 2005 has something rowid to solve the top sort problem. Unfortunately, there is no chance to experience it. When you write a stored procedure in SQL 2000, you always encounter the place where top is needed, and once you get to top, you can't write the numbers behind top as variables into the precompiled statements, so you can use only the construction SQL, exec. Not to speak of efficiency, the heart also always feel that this approach is very stupid.

In fact, you can use the ROWCOUNT keyword in SQL 2000 to solve this problem.

The use of the ROWCOUNT keyword is described in more detail in the online help, and there is no wordy. Talk about experience.

1, the use of ROWCOUNT query the first few lines of results.

DECLARE @n INT
SET @n = 1000
SET RowCount @n
SELECT * from Table_1

In this way, the query results will be equivalent to

SELECT Top from Table_1

2, the same reason, the use of INSERT INTO ... The select is also valid.

DECLARE @n INT
SET @n = 1000
SET RowCount @n
INSERT into table_2 (colname1)
SELECT colname1=colname2 from Table_1

The results of the execution will be equivalent to

INSERT into table_2 (colname1)
SELECT Top 1000 colname1 = colname2 from Table_1

3, execute update and delete.

Because update and delete cannot use the order by syntax directly, if you use ROWCOUNT, you will follow the primary key sequence in the past.

DECLARE @n INT
SET @n = 1000
SET RowCount @n
DELETE from Table_1

But there are solutions, as long as you can use the order by keyword, for example, by using a clause with an order BY, or by using the order by syntax to save an identity column that requires an action as a temporary table or table variable, and then using the in or EXISTS keyword in the operation statement.

DECLARE @n INT
SET @n = 1000
SET RowCount @n
DECLARE @t TABLE (ID INT)
INSERT into @t
SELECT ID from Table_1 order by colname [Asc/desc]

DELETE from Table_1 WHERE ID in (SELECT ID from @t)

4, for the rowcount setting is related to the session. If a session is used, the corresponding database sessions will use the rowcount of the most recent settings until the end of the sessions or the rowcount is modified.

5, can not use rowcount in user Custom function.

6, Cancel rowcount.

I don't know why in the online help, it should be noted that when writing a stored procedure, you should avoid using rowcount as much as possible and recommend top. Does MS not know that the number following the top key cannot be a variable? Perhaps MS is concerned that developers forget to cancel rowcount and affect the normal implementation.

Use such a statement to cancel the rowcount.

SET ROWCOUNT 0

8, summary

The use of rowcount can be very convenient to solve the top keyword can not take a variable problem, so that the need to sort the value of the problem is easier to solve. It is also convenient to debug stored procedures by avoiding the use of exec to execute a constructed SQL statement. The only inconvenient is that there is no way to get the current rowcount value, but to think about it, the usefulness of getting this value is not great.