SQLserver创建索引的意义.docVIP

索引工作的意义 题目：理解索引的工作意义：创建一个成绩表，在成绩表中插入几万条记录，尝试执行某个关于笔试成绩的查询，计算出执行该查询的执行时间；然后在笔试（字段）建立索引后，再执行相同的查询，比较这两次（索引建立前后的执行时间）来理解索引创建的意义，将中间的执行过程，你的思路、截图？ 一,前期准备 CREATE DATABASE Student GO USE Student GO -- 创建成绩表 create table stu_grade ( stu_id int IDENTITY(1,1) PRIMARY KEY, written_score int not null, lab_score int not null ) go -- 创建记录数据录入所需时间表 create table data_insert_time ( mark int identity(1,1), datavolume int, recrement int, Time_ms int, Time_ss float ) go -- 创建维护索引所需时间表 create table maintain_index_time ( mark int identity(1,1), datavolume int, Time_ms int, Time_ss float ) go --创建记录未创建索引查询所需时间表 create table query_time_unindex ( mark int identity(1,1), datavolume int, Time_ms int, Time_ss float, Result int ) go --创建记录创建索引后查询所需时间表 create table query_time_index ( mark int identity(1,1), datavolume int, Time_ms int, Time_ss float, Result int ) go -- 创建插入数据的存储过程,并计算插入数据所需时间,同时记录所需插入时间 -- 分别创建下列存储过程 -- create proc proc_insert_40000 每插入4 0000 -- create proc proc_insert_200000 每插入20 0000 -- create proc proc_insert_1000000 每插入100 0000 go create proc proc_insert_1000000 as DECLARE @count int,@account int,@start_time datetime,@end_time datetime select @count = 0,@start_time = getdate() while (@count1000000) begin insert into stu_grade (written_score,lab_score) values(floor(100*rand()),floor(100*rand())) set @count = @count + 1 end select @end_time = getdate(),@account=(select count(stu_id) from stu_grade) insert into data_insert_time(datavolume,recrement,Time_ms,Time_ss) values(@account,@count,datediff(ms,@start_time,@end_time),round(convert(float,datediff(ms,@start_time,@end_time))/1000,4)) go -- 创建未建索引时所需查询时间的存储过程,并获取所需查询时间记录新表 go create proc proc_query_time_unindex as declare @start_time datetime, @end_time datetime,@count int,@result int set @start_time = getdate() select * from stu_grade where (written_score between 80 and 90 )and lab_score 90 set @result = @@rowcount select @end_time = getdate(),@count=(select count(stu_id) from stu_grade) insert int