1
2
3 --索引测试
4 DROP TABLE Bank
5
6 CREATE TABLE Bank
7 (
8 AccountNo VARCHAR(20) PRIMARY KEY ,
9 AccountName VARCHAR(10),
10 [Address] VARCHAR(100),
11 CurrentMoney DECIMAL,
12 Remark TEXT
13 )
14 ALTER TABLE Bank
15 ADD CONSTRAINT DF_Remark DEFAULT('他什么说明也没有写') FOR Remark
16
17 --添加测试数据
18 DECLARE @n INT,@i INT,@AccountNo VARCHAR(20),@AccountName VARCHAR(10),@Address VARCHAR(100),@CurrentMoney DECIMAL,@ScrString VARCHAR(100)
19 SET @ScrString='你这他逐步形成档霜要个不是要地睛档案局要地本平村表决权一相'
20 SET @n=0
21
22 WHILE @n<1000000
23 BEGIN
24 SET @i=0
25 SET @AccountName=''
26 SET @Address=''
27 SET @AccountNo=CONVERT(VARCHAR(4), CONVERT(INT, RAND()*8999+1000))+CONVERT(VARCHAR(4), CONVERT(INT, RAND()*8999+1000))
28 WHILE @i<2
29 BEGIN
30 SET @AccountName=@AccountName+SUBSTRING(@ScrString,CONVERT(INT, RAND()*25),1)
31
32 SET @i=@i+1
33 END
34 SET @AccountName='名：'+ @AccountName
35
36 SET @i=0
37 WHILE @i<15
38 BEGIN
39 SET @Address=@Address+SUBSTRING(@ScrString,CONVERT(INT, RAND()*25),1)
40 SET @i=@i+1
41 END
42
43 SET @CurrentMoney=RAND()*10000
44 INSERT Bank VALUES(@AccountNo,@AccountName,@Address,@CurrentMoney,DEFAULT)
45 SET @n=@n+1
46 END
47
48 DELETE Bank
49
50 TRUNCATE TABLE BANK
51
52 --添加索引
53 CREATE INDEX IX_Bank_AccountName ON Bank(AccountName)
54 WITH PAD_INDEX ,FILLFACTOR = 30
55
56 CREATE INDEX IX_Bank_Address ON Bank([Address])
57 WITH PAD_INDEX ,FILLFACTOR = 30
58
59 --删除索引
60 DROP INDEX IX_Bank_AccountName ON Bank
61 DROP INDEX IX_Bank_Address ON Bank
62
63
64 --清楚缓存
65 DBCC FREEPROCCACHE
66 DBCC DROPCLEANBUFFERS
67
68 --这一列比较有规律
69 SELECT * FROM Bank WHERE AccountName='名：霜形'
70
71 --这一列规律比较差，字段文本比较长，添加索引后效率提高不大
72 SELECT * FROM Bank WHERE [Address] LIKE '%形要案局这霜不你平他要要睛地要%'
2
3 --索引测试
4 DROP TABLE Bank
5
6 CREATE TABLE Bank
7 (
8 AccountNo VARCHAR(20) PRIMARY KEY ,
9 AccountName VARCHAR(10),
10 [Address] VARCHAR(100),
11 CurrentMoney DECIMAL,
12 Remark TEXT
13 )
14 ALTER TABLE Bank
15 ADD CONSTRAINT DF_Remark DEFAULT('他什么说明也没有写') FOR Remark
16
17 --添加测试数据
18 DECLARE @n INT,@i INT,@AccountNo VARCHAR(20),@AccountName VARCHAR(10),@Address VARCHAR(100),@CurrentMoney DECIMAL,@ScrString VARCHAR(100)
19 SET @ScrString='你这他逐步形成档霜要个不是要地睛档案局要地本平村表决权一相'
20 SET @n=0
21
22 WHILE @n<1000000
23 BEGIN
24 SET @i=0
25 SET @AccountName=''
26 SET @Address=''
27 SET @AccountNo=CONVERT(VARCHAR(4), CONVERT(INT, RAND()*8999+1000))+CONVERT(VARCHAR(4), CONVERT(INT, RAND()*8999+1000))
28 WHILE @i<2
29 BEGIN
30 SET @AccountName=@AccountName+SUBSTRING(@ScrString,CONVERT(INT, RAND()*25),1)
31
32 SET @i=@i+1
33 END
34 SET @AccountName='名：'+ @AccountName
35
36 SET @i=0
37 WHILE @i<15
38 BEGIN
39 SET @Address=@Address+SUBSTRING(@ScrString,CONVERT(INT, RAND()*25),1)
40 SET @i=@i+1
41 END
42
43 SET @CurrentMoney=RAND()*10000
44 INSERT Bank VALUES(@AccountNo,@AccountName,@Address,@CurrentMoney,DEFAULT)
45 SET @n=@n+1
46 END
47
48 DELETE Bank
49
50 TRUNCATE TABLE BANK
51
52 --添加索引
53 CREATE INDEX IX_Bank_AccountName ON Bank(AccountName)
54 WITH PAD_INDEX ,FILLFACTOR = 30
55
56 CREATE INDEX IX_Bank_Address ON Bank([Address])
57 WITH PAD_INDEX ,FILLFACTOR = 30
58
59 --删除索引
60 DROP INDEX IX_Bank_AccountName ON Bank
61 DROP INDEX IX_Bank_Address ON Bank
62
63
64 --清楚缓存
65 DBCC FREEPROCCACHE
66 DBCC DROPCLEANBUFFERS
67
68 --这一列比较有规律
69 SELECT * FROM Bank WHERE AccountName='名：霜形'
70
71 --这一列规律比较差，字段文本比较长，添加索引后效率提高不大
72 SELECT * FROM Bank WHERE [Address] LIKE '%形要案局这霜不你平他要要睛地要%'