SQL Server 2008 Enhancement II: Top new uses

First, top alternative set RowCount

In traditional SQL statements prior to SQL Server 2005, the top statement was not supported for local variables. See http://www.cnblogs.com/downmoon/archive/2007/12/29/1019686.html

You can use set RowCount at this point, but in SQL Server 2005/2008, top usually executes faster, so you should replace set RowCount with the top keyword.

/*Create a test table ********************* ****************downmoo [email protected] ***************/
IF Not object_id(‘[Demo_top]‘)Is Null DROP TABLE [Demo_top] GO Create Table [Demo_top](PIDInt Identity(1,1)Primary Key Not Null, PNamenvarchar(100)Null, AddtimeDatetime Null, PguidNvarchar(40) )Go
Truncate Table [Demo_top]
/*Create 1002 test Data ********************* ****************downmoo [email protected] ***************/
Declare @d Datetime Set @d=GetDate()
Declare @i Int Set @i=1 While @i<=1002 Begin Insert Into [Demo_top] Select Cast(DatePart(MS,GetDate())As nvarchar(3))+replicate ( '

--note that the top keyword can be used in select,update and DELETE statements

Declare @percentage Float Set @percentage=1select top ( @percentage ) PName from[demo_top] order by PName -- Note that it is 11 rows. (one row (s) affected)

Invite the Month Note: If you just need some samples, you can also use Tablesample, the following statement returns a percentage of the table Demo_top random rows

Select pname,addtime, Pguid from [demo_top] tablesample System (ten Percent) --(affected row (s) )

Note that this percentage is the percentage of the table data page, not the percentage of the number of records, so the number of records is indeterminate.

Second, top sub-block modification data

The second key improvement to top is the block operation that supports the data. In other words, avoid doing very large operations in a single statement and splitting the changes into smaller chunks, which greatly improves the concurrency of large data volumes, large-volume tables, and can be used in large reports or data warehouse applications. In addition, the block operation avoids the rapid growth of the log because the log space may be reused after the previous operation has completed. If there is a transaction in the operation, the modified data that has already been completed can already be used for the query without waiting for all modifications to complete.

Still the above table is an example:

While(Select Count(1)From [Demo_top])>0 Begin delete top (202 ) [demo_top] end
/* (202 row (s) Affected)
(202 row (s) affected)
(202 row (s) affected)
(202 row (s) affected)
(194 row (s) affected) */

Note that 202 data is deleted per batch, and top can also be used for select and UPDATE statements, where the latter is more practical.

--select Top (+)--update Top (100)

SQL Server 2008 Enhancement II: Top new uses