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 ' %形要案局这霜不你平他要要睛地要% '