SQL_server_创建索引意义.docVIP

索引工作的意义题目：理解索引的工作意义：创建一个成绩表，在成绩表中插入几万条记录，尝试执行某个关于笔试成绩的查询，计算出执行该查询的执行时间；然后在笔试（字段）建立索引后，再执行相同的查询，比较这两次（索引建立前后的执行时间）来理解索引创建的意义，将中间的执行过程，你的思路、截图？一,前期准备CREATE DATABASE StudentGOUSE StudentGO-- 创建成绩表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 0000gocreate proc proc_insert_1000000asDECLARE @count int,@account int,@start_time datetime,@end_time datetimeselect @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 + 1endselect @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-- 创建未建索引时所需查询时间的存储过程,并获取所需查询时间记录新表gocreate proc proc_query_time_unindexasdeclare @start_time datetime, @end_time datetime,@count int,@result intset @start_time = getdate()select * from stu_grade where (written_score between 80 and 90 )and lab_score 90set @result = @@rowcountselect @end_time = getdate(),@count=(select count(stu_id) from stu_grade)insert into query_time_unindex (datavolume,Time_ms,Time_ss,Result)values(@count,datediff(ms,