SQL Server's ROWCOUNT keyword

Source: Internet
Author: User
Tags rowcount sessions sort

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.