資源描述：
《sql server 創(chuàng)建索引的意義》由會(huì)員上傳分享，免費(fèi)在線閱讀，更多相關(guān)內(nèi)容在工程資料-天天文庫(kù)。
1、索引工作的意義題目：理解索引的工作意義：創(chuàng)建一個(gè)成績(jī)表，在成績(jī)表中插入幾萬(wàn)條記錄，嘗試執(zhí)行某個(gè)關(guān)于筆試成績(jī)的查詢，計(jì)算出執(zhí)行該查詢的執(zhí)行時(shí)間；然后在筆試（字段）建立索引后，再執(zhí)行相同的查詢，比較這兩次（索引建立前后的執(zhí)行時(shí)間）來(lái)理解索引創(chuàng)建的意義，將中間的執(zhí)行過(guò)程，你的思路、截圖？一,前期準(zhǔn)備CREATEDATABASEStudentGOUSEStudentGO--創(chuàng)建成績(jī)表createtablestu_grade(stu_idintIDENTITY(1,1)PRIMARYKEY,written_scoreintnotnull,lab_scoreint
2、notnull)go--創(chuàng)建記錄數(shù)據(jù)錄入所需時(shí)間表createtabledata_insert_time(markintidentity(1,1),datavolumeint,recrementint,Time_msint,Time_ssfloat)go--創(chuàng)建維護(hù)索引所需時(shí)間表createtablemaintain_index_time(markintidentity(1,1),datavolumeint,Time_msint,Time_ssfloat)go--創(chuàng)建記錄未創(chuàng)建索引查詢所需時(shí)間表createtablequery_time_unindex
3、(markintidentity(1,1),datavolumeint,Time_msint,Time_ssfloat,Resultint)go--創(chuàng)建記錄創(chuàng)建索引后查詢所需時(shí)間表createtablequery_time_index(markintidentity(1,1),datavolumeint,Time_msint,Time_ssfloat,Resultint)go--創(chuàng)建插入數(shù)據(jù)的存儲(chǔ)過(guò)程,并計(jì)算插入數(shù)據(jù)所需時(shí)間,同時(shí)記錄所需插入時(shí)間--分別創(chuàng)建下列存儲(chǔ)過(guò)程--createprocproc_insert_40000每插入40000--cre
4、ateprocproc_insert_200000每插入200000--createprocproc_insert_1000000每插入1000000gocreateprocproc_insert_1000000asDECLARE@countint,@accountint,@start_timedatetime,@end_timedatetimeselect@count=0,@start_time=getdate()while(@count<1000000)begininsertintostu_grade(written_score,lab_score
5、)values(floor(100*rand()),floor(100*rand()))set@count=@count+1endselect@end_time=getdate(),@account=(selectcount(stu_id)fromstu_grade)insertintodata_insert_time(datavolume,recrement,Time_ms,Time_ss)values(@account,@count,datediff(ms,@start_time,@end_time),round(convert(float,dat
6、ediff(ms,@start_time,@end_time))/1000,4))go--創(chuàng)建未建索引時(shí)所需查詢時(shí)間的存儲(chǔ)過(guò)程,并獲取所需查詢時(shí)間記錄新表gocreateprocproc_query_time_unindexasdeclare@start_timedatetime,@end_timedatetime,@countint,@resultintset@start_time=getdate()select*fromstu_gradewhere(written_scorebetween80and90)andlab_score>90set@res
7、ult=@@rowcountselect@end_time=getdate(),@count=(selectcount(stu_id)fromstu_grade)insertintoquery_time_unindex(datavolume,Time_ms,Time_ss,Result)values(@count,datediff(ms,@start_time,@end_time),round(convert(float,datediff(ms,@start_time,@end_time))/1000,4),@result)go--創(chuàng)建創(chuàng)建索引器的存儲(chǔ)
8、過(guò)程,并將創(chuàng)建索引器所需的時(shí)間記錄新gocreateprocproc_create_index
