One, top instead of SET ROWCOUNT
In traditional SQL statements prior to SQL Server 2005, the top statement does not support local variables. See
Http://www.jb51.net/article/27089.htm
Set ROWCOUNT can be used at this time, but in SQL Server 2005/2008, top usually executes faster, so you should replace the SET rowcount with the top keyword.
Copy Code code as follows:
/*************** Create a test table *********************
Downmoo 3w@live.cn ***************/
IF not object_id (' [Demo_top] ') is NULL
DROP TABLE [Demo_top]
Go
Create table [Demo_top]
(PID int identity (1,1) primary key NOT NULL
, PName nvarchar (MB) null
, Addtime dateTime NULL
, Pguid Nvarchar (40)
)
Go
TRUNCATE TABLE [Demo_top]
/*************** Create 1002 test data *********************
Downmoo 3w@live.cn ***************/
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 @i=@i+1
End
--note that the top keyword can be used in select,update and DELETE statements
Copy Code code as follows:
Declare @percentage Float
Set @percentage =1
Select Top (@percentage) percent pname to [demo_top] ORDER by pname
--Note that 11 lines. (one row (s) affected)
Invitation Note: If you only need some samples, you can also use Tablesample, which returns a certain percentage of random rows of table Demo_top
Copy Code code as follows:
Select Pname,addtime, Pguid from [Demo_top]
Tablesample System (percent)
--(affected row (s))
Note that this percentage is a percentage of the table data page, not a percentage of the number of records, so the number of records is indeterminate.
Second, top block modification data
The second key improvement for top is to support chunking of data. In other words, avoiding a very large operation in one statement and splitting the modifications into smaller chunks greatly improves the concurrency of large data volumes, large access tables, and can be used in large reports or data warehouse applications. In addition, block operations can prevent the rapid growth of logs, because log space may be reused when the previous operation completes. If there is a transaction in the operation, the modified data that has been completed can already be used for the query without waiting for all the modifications to complete.
The above table is still an example:
Copy Code code as follows:
while (select COUNT (1) from [Demo_top]) >0
Begin
Delete Top (a) from [Demo_top]
End
/*
(Affected row (s))
(Affected row (s))
(Affected row (s))
(Affected row (s))
(194 row (s) affected)
*/
Note that each batch deletes 202 data, and top can also be used for select and UPDATE statements, the latter being more practical.
--select Top (100)
--update Top (100)
Invite the Month Note: This article copyright by invite month and the blog Garden Common All, reprint please indicate the source.
