mssql 小技巧 - xcj26－IT工程師數位筆記本

文章出處

代碼1：查看sql的執行時間

SET STATISTICS PROFILE ON 
SET STATISTICS IO ON 
SET STATISTICS TIME ON 

select * from Meeting;

SET STATISTICS PROFILE OFF 
SET STATISTICS IO OFF 
SET STATISTICS TIME OFF


declare @d datetime
set @d=getdate()
/*你的SQL腳本開始*/
select * from Meeting;
/*你的SQL腳本結束*/
select [語句執行花費時間(毫秒)]=datediff(ms,@d,getdate())

代碼2：創建索引

IF EXISTS (SELECT * FROM SYSINDEXES WHERE NAME='Ix_Meeting_ID')
BEGIN
    DROP INDEX Meeting.Ix_Meeting_ID
END
ELSE
BEGIN
 CREATE CLUSTERED INDEX Ix_Meeting_ID
 ON Meeting(id)  
 WITH FILLFACTOR = 30 
END


/*
UNIQUE--建立唯一索引(UNIQUE索引既可以采用聚集索引結構，也可以采用非聚集索引的結構，如果不指明采用的索引結構，則SQL Server系統默認為采用非聚集索引結構)
CLUSTERED--建立聚集索引
NONCLUSTERED--建立非聚集索引

CREATE CLUSTERED INDEX Ix_Meeting_ID
 ON Meeting(id)  
 WITH FILLFACTOR = 30 --填充因子為30%
*/