MSSQL网页编程之Processing Sequentially Through a Se...

在JOIN操作中（需要从多个数据表提取数据时），MySQL只有在主键和外键的数据类型相同时才能使用索引。November19,2003
T-SQLProgrammingPart3-ProcessingSequentiallyThroughaSetofRecords
ByGregoryA.Larsen


Atsomepointyouwillhavesomebusinesslogicthatwillrequireyoutoprocesssequentiallythroughasetofrecordsonerecordatatime.Forexampleyoumayhavealistofdatabases,andforeachdatabaseyoumaywanttobuildacommandthatwillperformsomeprocessagainsteachdatabase.Oryoumighthaveasetofrecordswhereyouwanttoprocessthrougheachrecordoneatatime,soyoucanselectadditionalinformationfromanothertablebasedontheinformationcontainedineachrecord.Thisarticlewilldiscusstwodifferentwaystoprocessthroughasetofrecordsonerecordatatime.
UsingaCursor
ThefirstmethodIwilldiscussusesacursortoprocessthroughasetofrecordsonerecordatatime.Acursorisbasicallyasetofrowsthatyoudefinebasedonarecordsetreturnedfromaquery.Acursorallowsapplicationsamechanismtoprocessthrougharesultsetonerowatatime.Withacursoranapplicationisallowedtopositionitselftoaspecificrow,scrollbackandforth,andanumberofotherthings.Itwouldtakeaseriesofarticlestodescribeallthefunctionalityofacursor.ForthepurposeofthisarticleImonlygoingtofocusonhowtousethedefaultscrollingfunctionalityofacursor.Thisdefaultfunctionalitywillonlyreadfromthefirstrowtothelastrowinacursor,onerowatatime.Iwillleaveadditionalcursortopicstoanotherarticleseries.

TodefineacursortheDECLARECURSORstatementisused.HereisthebasicformatforthesimplecursortopicIwillbediscussinginthisarticle.

DECLAREcursor_nameCURSORFORselect_statement

Thecursor_nameisthenameyouwanttoassociatewiththecursor.Theselect_statementisthequerythatwilldeterminetherowsthatmakeupthecursor.Notethereareotherparameters/optionsassociatedwiththeDECLARECURSORstatementthathelpdefinemorecomplicatedcursorprocessingthanIwillbecoveringinthisarticle.FortheseadditionaloptionspleasereadMicrosoftSQLServerBooksOnline.

Letsreviewafairlysimplecursorexample.Thisexamplewilldefineacursorthatcontainsthetop5Customer_IdsintheCustomertableintheNorthwinddatabase.ItwillthenprocessthrougheachrecorddisplayingarownumberandtheCustomerIDforeach.Hereisthecodetodothis.

declare@CustIdnchar(5)declare@RowNumintdeclareCustListcursorforselecttop5CustomerIDfromNorthwind.dbo.CustomersOPENCustListFETCHNEXTFROMCustListINTO@CustIdset@RowNum=0WHILE@@FETCH_STATUS=0BEGINset@RowNum=@RowNum+1printcast(@RowNumaschar(1))++@CustIdFETCHNEXTFROMCustListINTO@CustIdENDCLOSECustListDEALLOCATECustList

HerearetheresultsthataregeneratedfromtheprintstatementwhenIrunitagainstmyNorthwindDatabase.

1ALFKI2ANATR3ANTON4AROUT5BERGS

Letslookattheabovecodeinalittlemoredetail.Ifirstdeclaredacursorcalled"CustList".The"CustList"cursorispopulatedusingaSELECTstatementthatusestheTOPclausetoreturnonlythetop5CustomerIds.Nextthecursorisopened.Eachrecordinthe"CustList"cursorisretrieved,onerecordatatime,usingthe"FETCHNEXT"nextstatement.The"FETCHNEXT"statementpopulatesthelocalvariable@CustIDwiththeCustomerIDofthecurrentrecordbeingfetched.The@@FETCH_STATUSvariablecontrolswhethertheWHILEloopisexecuted.@@FETCH_STATUSissettozerowhenarecordissuccessfullyretrievedfromthecursor"CustList".InsidetheWHILEloopthe@RowNumvariableisincrementedby1foreachrecordprocessed.ThecalculatedRowNumberand@CustIdarethenprintedout.Lastly,a"FETCHNEXT"statementisusedtoretrievethenextrowbeforethenextcycleoftheWHILEloop.Thisprocesscontinuesonerecordatatimeuntilallrecordsincursor"CustList"havebeenprocessed.
UsingaSelectStatement
YoucanalsouseaSELECTstatementtoprocessthroughasetofrecordsonerecordatatime.TodothisIwillissueaninitialSELECTstatementthatwillreturnthefirstrow,thenaseriesoffollowonSELECTstatementswhereeachSELECTstatementretrievesthenextrow.Thisisdonebyusingthe"TOP1"clauseoftheSELECTstatement,andaWHEREstatement.

Iwillusethesameexampleasaboveandonlyreturnthetop5CustomerIDsfromtheNorthwinddatabaseCustomerstable.InthiscodeIwillusetwodifferent"SELECTTOP1"statementsandaWHILElooptoreturnall5records.Eachrecordwillbeprocessedoneatatime.

declare@CustIdnchar(5)declare@RowNumintselecttop1@CustId=CustomerIDfromNorthwind.dbo.Customersset@RowNum=0WHILE@RowNum<5BEGINset@RowNum=@RowNum+1printcast(@RowNumaschar(1))++@CustIdselecttop1@CustId=CustomerIDfromNorthwind.dbo.CustomerswhereCustomerId>@CustIDEND

HereyoucanseethefirstSELECTstatementselectsonlythefirstCustomerID.ThisIDisplacedinthelocalvariable@CustID.TheWHILEloopiscontroledbythelocalvariable@RowNum.EachtimethroughtheWHILEloop,theRowNumberandCustomerIDareprintedout.PriortoreturningtothetopoftheWHILEloopIusedanother"SELECTTOP1"statementtoselectthenextCustomerID.ThisSELECTstatementusesaWHEREclauseontheSELECTstatementtoselectthefirstCustomerIDthatisgreaterthantheCustomerIDthatwasjustprinted.TheWHILEloopisprocess5times,allowingtheSELECTTOP1methodtoretrievethetop5CustomerIDsonerecordsatatime.ThisexampleproducesthesameprintedoutputasmypriorCURSORexample.
Conclusion
HopefullythisarticlehasgivenyousomeideasonhowtouseaCURSOR,andaSELECTstatementtoprocessthroughasetofrecords.Iusebothofthesemethods,althoughIfindusingaSELECTstatementtobealittlesimplertocode.Youwillneedtodecidewhichsolutionmakesthemostsenseinyourenvironment.
如果某个数据列里包含许多重复的值，就算为它建立了索引也不会有很好的效果。比如说，如果某个数据列里包含的净是些诸如“0/1”或“Y/N”等值，就没有必要为它创建一个索引。

索引视图2k就有。但是2005对其效率作了一些改进但是schema.viewname的作用域真是太限制了它的应用面。还有一大堆的环境参数和种种限制都让人对它有点却步。

从底层原理到表层引用，书籍多的很。个人认为没有什么那本书好？这样的说法。主要看和个人的学习方法是否适合。

你觉得我的非分区索引无法对起子分区，你可以提醒我一下呀！没有任何的提醒，直接就变成了非分区表。不知道这算不算一个bug。大家也可以试试。

SQL语言是学习所有数据库产品的基础，无论你是做数据库管理还是做数据库开发都是这样。不过具体学习的侧重点要看你将来做哪一块，如果是做数据库管理（DBA），侧重点应该放在SQLServer的系统管理上.

你觉得我的非分区索引无法对起子分区，你可以提醒我一下呀！没有任何的提醒，直接就变成了非分区表。不知道这算不算一个bug。大家也可以试试。

但换公司用MSSQL2K感觉自己好像根本就不了解MSSQL。什么DTS触发器以前根本没用过。