SQL Server TOP new uses

/*************** Creating a test table *********************

[Email protected] ***************/

IF not object_id (' [Demo_top] ') is NULL

DROP TABLE [Demo_top]

GO

Create table [Demo_top]

(PID int identity (primary) key NOT NULL

, PName nvarchar (+) null

, Addtime dateTime NULL

, Pguid Nvarchar (40)

)

Go

TRUNCATE TABLE [Demo_top]

/*************** Create 1002 test data *********************

[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 (' A ', DatePart (Ss,getdate ()))

, GETDATE ()

, NewID ()

Set @[email protected]+1

End

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

Declare @percentage Float

Set @percentage =1

Select Top (@percentage) percent PName from [demo_top] ORDER by PName

--Note that it is 11 rows. (one row (s) affected)

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

Select Pname,addtime, Pguid from [Demo_top]

Tablesample System (percent)

--(affected row (s))

Top sub-block modify 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) from [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 (100)

--update TOP (100)

SQL Server TOP new uses