Rowcount keyword of SQL Server

 

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

It is said that SQL 2005 has rowid to solve the top Sorting Problem. Unfortunately, there is no chance to try it out. In SQL 2000, when writing a stored procedure, top is always needed. Once top is encountered, the number following top cannot be written as a variable into the pre-compiled statement, therefore, you can only construct SQL statements and execute them using exec. Without talking about efficiency, I always feel that this method is stupid.

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

The usage of the rowcount keyword is described in detail in online help. Let's talk about the experience.

1. Use rowcount to query the results of the first few rows.

Declare @ n int
Set @ n = 1000
Set rowcount @ n
Select * From table_1
 

In this way, the query result is equivalent

Select Top 100 from table_1

2. In the same way, insert into... 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 execution result is equivalent

Insert into table_2 (colname1)
Select Top 1000 colname1 = colname2 from table_1

3. Execute update and delete.

Because the order by syntax cannot be directly used for update and delete, if rowcount is used, the Operation will follow the primary key sequence before and after.

Declare @ n int
Set @ n = 1000
Set rowcount @ n
Delete from table_1

However, there are also solutions, as long as the order by keyword can be used, for example, directly using a clause containing order, alternatively, you can use the order by syntax to store the ID column to be operated as a temporary table or table variable, and then use 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. rowcount settings are related to sessions. If a session is occupied, the corresponding database session uses the rowcount set last time until the session ends or the rowcount is modified.

5. You cannot use rowcount in user-defined functions.

6. Cancel rowcount.

I don't know why in the online help section, we recommend that you avoid using rowcount when writing a stored procedure. Does Ms know that the number next to the top key cannot be a variable? Ms may be concerned that the developer forgets to cancel rowcount and affects normal implementation.

You can use this statement to cancel rowcount.

Set rowcount 0

8. Summary

With rowcount, you can easily solve the problem that top keywords do not contain variables. In this way, it is easier to solve the problem of sorting values. This avoids the use of exec to execute and construct SQL statements. It is much easier to debug the stored procedure. The only inconvenience is that the method for obtaining the current rowcount value is not found.