資源描述：
《sqlserver創(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_scor
2、eintnotnull,lab_scoreintnotnull)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)g
3、o--創(chuàng)建記錄未創(chuàng)建索引查詢所需時(shí)間表createtablequery_time_unindex(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ì)算插
4、入數(shù)據(jù)所需時(shí)間,同時(shí)記錄所需插入時(shí)間--分別創(chuàng)建下列存儲(chǔ)過(guò)程--createprocproc_insert_40000每插入40000--createprocproc_insert_200000每插入200000--createprocproc_insert_1000000每插入1000000gocreateprocproc_insert_1000000asDECLARE@countint,@accountint,@start_timedatetime,@end_timedatetimeselect@co
5、unt=0,@start_time=getdate()while(@count<1000000)begininsertintostu_grade(written_score,lab_score)values(floor(100*rand()),floor(100*rand()))set@count=@count+1endselect@end_time=getdate(),@account=(selectcount(stu_id)fromstu_grade)insertintodata_insert_tim
6、e(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--創(chuàng)建未建索引時(shí)所需查詢時(shí)間的存儲(chǔ)過(guò)程,并獲取所需查詢時(shí)間記錄新表gocreateprocproc_query_time_unindexasdeclare@start_timedat
7、etime,@end_timedatetime,@countint,@resultintset@start_time=getdate()select*fromstu_gradewhere(written_scorebetween80and90)andlab_score>90set@result=@@rowcountselect@end_time=getdate(),@count=(selectcount(stu_id)fromstu_grade)insertintoquery_time_unindex(d
8、atavolume,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ǔ)過(guò)程,并將創(chuàng)建索引器所需的時(shí)間記錄新gocreateprocproc_create_index
