SQL Server 性能优化(2)– Performance - Insert 1 million rows

/*

Alter a table

*/

ALTER TABLE tbl_tem_ccp DROP CONSTRAINT PK__tbl_tem_ccp__7C8480AE-- identity(1,1)

ALTER TABLE tbl_tem_ccp DROP COLUMN ins_id

ALTER TABLE tbl_tem_ccp ADD ins_id int not null primary key identity(1,1)

/*

Create a stored procedure

*/

CREATE PROCEDURE sp_insert_loop

AS

BEGIN

DECLARE @i int

SET @i = 1

WHILE @i <11822

BEGIN

INSERT INTO tbl_tem_ccp VALUES (@i)

SET @i = @i+1

END

END

/*

Results of time spent

*/

EXEC sp_insert_loop --(11821 row(s) inserted) 0:01:37 (100000 row(s)) 0:11:31

DELETE FROM tbl_tem_ccp --(11821 row(s) affected) 0:00:15

TRUNCATE TABLE tbl_tem_ccp --0:00:00

SELECT * FROM tbl_tem_ccp

---------------------------------------------------------------------------------------------------------

SELECT name FROM sysobjects

SELECT * FROM tbl_tem_ccp

SELECT * FROM sysindexes