How to parse the Top function of SQLServer2005

How to analyze the Top function of SQLServer2005, I believe that many inexperienced people are at a loss about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Everyone knows how to use select top, but many people don't know how to use update top and delete top. The previous practice is to specify set rowcount, but in fact, the enhancements to Top statements in SQL2005 include support for update and delete in addition to parameterization, but unfortunately, custom order by columns are not supported. If you want to customize the pie sequence, you can use CTE. Any changes to CTE affect the original table.

Let's look at the test code below.

The code is as follows:

Set nocount on use tempdb go if (object_id ('tb') is not null) drop table tb go create table tb (id int identity (1,1), name varchar (10), tag int default 0) insert into tb (name) select 'a'insert into tb (name)

Select 'b'insert into tb (name) select 'c'insert into tb (name) select 'd'insert into tb (name) select 'e' / *-- Update the first two lines

Id name tag-1 a 1 2 b 1 3 c 0 4 d 0 5 e 0 * / update top (2) tb set tag = 1 select * from tb / *-the last two lines are updated

Id name tag-1 a 1 2 b 1 3 c 0 4 d 1 5 e 1 * /; with t as (select top (2) * from tb order by id desc) update t set tag = 1 select * from tb / *-Delete the first two lines

Id name tag-3 c 0 4 d 1 5 e 1 * / delete top (2) from tb select * from tb / *-Delete the last two lines

Id name tag-3 c 0 * /; with t as (select top (2) * from tb order by id desc) delete from t select * from tb set nocount off

Let me give you a hint: SQLServer2005 has a keyword Output, which can output the changed and inserted data, and we can simulate a relatively efficient exclusive query with update top. This feature is suitable for parallel task processing or consumption.

After reading the above, do you know how to parse the Top function of SQLServer2005? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!