MSSQL教程之计划数据堆栈的注重事项(Part II)

限制,如果WHERE子句的查询条件里有不等号（WHEREcoloum!=），MySQL将无法使用索引。类似地，如果WHERE子句的查询条件里使用了函数（WHEREDAY（column）=），MySQL也将无法使用索引。计划|数据
EndUsers

Theendusersarewhoyouaredesigningandbuildingthedatawarehousefor?yourcustomers.Theyrelyontheinformationtheyextractfromthewarehouseinordertoworkmoreeffectivelyandefficiently.Theyalsoultimatelydeterminewhetherornotyourdatawarehouseprojectmetitsstatedgoals.Forthesereasons,yourprojectmustbebuiltaroundprovidingtheenduserswiththeinformationtheyneedinauser-friendlyfashion.

Theprimaryendusersfordatawarehouseinformationarereportrecipientsandquerydesigners.Theyprovidetheresultsoftheirworktothedecisionmakerswhodeterminethecourseofyourorganization.

ReportRecipients

Reportrecipientsaretheendusersmostdependentupontheinformationextractedfromthedatawarehouse.Theyincludebusinessanalysts,financialanalysts,mid-levelmanagers,seniormanagersandexecutives.Althoughsomeofthereportrecipientswillhaveparticipatedinthedesignprocess,theywillhavedifferentperspectivesasendusers.Theywillhavethegreatestinterestsinreportcontentformatandrequirements,timeframeandupdatefrequency,adhocquerycapabilitiesanddrilldown/drillthroughrequirements.

Reportcontentandformatrequirementsdefinewhatinformationispresentedandarranged.Yourenduserswilleachhaveaspecialperspectiveonwhatinformationisimportantintheperiodicreportstheyrelyon.Manytimestheywillneedsimilarsourcedata,butthereportswillcontaindifferentcalculatedvaluesandextrapolations.Evenwhentheyagreeonwhatinformationmustbepresent,theyeachhaveadifferentsetofrequirementswhenitcomestohowtheinformationisarranged.Eachoftheseviewsmustbetakenintoconsiderationwhendefiningyourprojectrequirements.

QueryDesigners

Querydesignersaretechnicallycompetentpeoplewhoaretaskedwithbuildingthequeriesthatprovidethereports.Theyhavetopleasethereportrecipientswhileworkingunderthoselimitationsimposedbythestructureandcontentofthedatawarehouse.

Thequerydesignersareavaluableresourceinthesameareasasthoserepresentedbythereportrecipients,butfromadifferentperspective.Theyfrequentlyaskyoutoincludecalculatedcolumnsthereportrecipientsdontknowarenecessary.Theyalsoaskforsimplerstructureinmanyareas,soastomaketheirquerydesignsmoremanageableandalsodemandhighlydenormalizedtableswhereverpossible.

Management

Managementwilltypicallyprovidethemostdemandingandleastdetailedsetofrequirementsfromthedatawarehouseproject.Youcanexpectsuchrequestsas,"Ineedtoknowhowprofitableweareonaquarterlybasis."or"Whoareourtop100customers?"Youwillthenhavetoworktodefineprofitabilityandtopcustomerintermsthatcanbeappliedtothedatawarehouse.

Managementrequirementsusuallyfallintothreegeneralcategories:strategicreportingandprojections,tacticalreportingandenterprisestandardscompliance.

StrategicReportingandProjections

Strategicreportingandprojectionarelongtermprocessesaimedatpositioningtheorganizationtoremaincompetitiveinthemarketplace.Strategyisdefinedasaplandesignedtoplaceanorganizationinanadvantageouspositionpriortocompetition.Fromthiswecanseethattheinformationneededtosupportstrategicinitiativesconcernslong-termtrendandmarketpenetrationanalysis,long-termrawmaterialinventoryavailabilityandusage,changesinthetechnicalenvironment,workforceprojectionsandcustomerdemographicinformation.Eachmanagementteamwillhaveitsownlistofspecificinformationtheyneedinordertohelpthemformulateoradjustthecorporatestrategy.

TacticalReporting

Tacticalreportingdealswithunderstandinghowday-to-dayoperationsaremeetingstrategicobjectives.Theseshort-termreportsmayconsistofdaily,weekly,monthly,quarterlyorannualanalysesofthecurrentstateofaffairswithintheorganization.Thesereportswillcontainoperationalinformationonsalesstatistics,workforceutilization,inventorymanagementandmeasuresspecifictoeachindustry.Thetimelinessofthesereportsrequiresinformationbemadeavailableasrapidlyaspossibleafteritiscaptured.Itisnotunusualformanagementtorequirethatdailyreportsbeavailablewhentheyarriveforworkonthefollowingday,orthatweeklyreportsbeavailableby9:00AMonMondaymorning.Yourdatawarehousewillhavetobetunedtoprovidetherequiredlevelsofperformance.

EnterpriseStandardsCompliance

Enterprisestandardscomplianceconsistsofinsuringthatallpartsoftheorganizationareworkingintheacceptedmanner.Thereportsthathelpmanagementdeterminetheextenttowhichthisistruecanrangefromverysimple,withonlyafewrows,eachcontainingonlyfourorfivecolumns,tocomplex,multi-pagereports,supportedbypivottablesandcharts.Theextenttowhichyourmanagementteamwishestotrackstandardscompliancewilleffectthelevelofdetailthatyouwillneedtocapture.Forinstance,shouldthemanagementteamwanttoinsurethateachworkgroupiscomplyingwithcorporatestandardsregardingworkhoursandbreaktimes,yourwarehousewillhavetocontainallthepertinentdataoneachemployeesscheduleoveraspecifiedperiodoftime.Youwillalsohavetobuildsummarytablestosupportmoregeneralanalyses.

SystemsStaff

Yoursystemsstaffmemberswillhelpidentifythebesthardware,softwareandconnectivityconfigurationsforyourwarehousingoperation.Theyalsowillhelpyouidentifytherisksandlimitationspresentedbyeachpossibleconfiguration.

Hardware

Hardwareconsistsofalltheprocessing,storageandperipheralcomponentsuponwhichyourdatawarehouseoperationwillreside.Yoursystemsspecialistswillhelpyoumakeintelligentandinformedchoicesregardingmainframe,mid-range,mini-computerorpersonalcomputerprocessing;multipleprocessing,eitherparallelordistributed;distributedorconcentratedstorage;andallthedecisionsthatdeterminethephysicalstructureofyourwarehouseenvironment.

OperatingSystemandSoftware

Insomecases,theoperatingsystem(OS)andsoftwarechoicesavailabletoyouwillbedeterminedbyyourhardwareselections.Inothercases,thehardwarechoiceswillaffect,butnotdictate,theOSandsoftwareoptionsavailabletoyou.Yoursystemsspecialistswillworkwithyoutodeterminewhichcombinationsfityourprojectsneedswhilecomplyingwithcorporatestandards.

Connectivity

Connectivitydealswithallthecomponentsandassociatedcommunicationsthatallowonecomputertosendandreceiveinformationfromanother.Yoursystemteammemberswillprovideyouwithvaluableinformationregardingthechoicesavailable,recommendedconfigurations,currentlyimplementeddesignsandanyenterprisestandardsyourprojectmustcomplyto.

SecurityStaff

Yourorganizationssecurityspecialistsfallintotwomaincategories,information/systemssecurityandfacility/physicalsecurity.Ineachcase,thesespecialistswillprovideyourteamwithinformationvitaltothesuccessofyourproject.

Information/SystemSecurity

Informationandsystemsecurityissuescenteronthreeprimarycategories:authentication,authorizationandusecontrol.

Authenticationistheprocessofinsuringthataparticularindividualisaccuratelyidentified.Authorizationdealswiththosemechanismsthatpreventapersonfromgainingaccesstoapplicationsordatabasestheyshouldnothaveaccessto,whileinsuringaccesstoapplicationsordatabasestheyhasobtainedapprovalfor.Usecontrolisconcernedwithlimitingeachindividualtooperationsthathavebeenidentifiedasappropriatefortheirjobandoperationalfunctions.

Facility/PhysicalSecurity

Facilityandphysicalsecurityissuesareconcernedwithwhereandwhenparticularindividualsmaybeincorporateproperties,whattheycandowhilethereandmakingsurecorporatepropertyissecuredagainstmaliciousdamageandtheft.Threeofthecategoriesoffacilityandphysicalsecurityarethesameasthoseforinformationandsystemsecurity:authentication,authorizationandusecontrolaswellaslossprevention.

Asisthecasewithinformationandsystemsecurity,authenticationistheprocessofinsuringthatanindividualis,infact,whotheypurporttobe.Authorizationconcernstheprocessofallowingonlythosewiththeproperclearancestoenterspecificareaswithincorporatefacilities,whileprovidingcompleteandreadyaccessforthosewhoshouldbethere.Usecontroldealswithinsuringeachfacilityorphysicalstructureisusedinaccordancewithcorporatepolicy,andthosepresentcomplywithallsafetyandregulatoryrequirements.Losspreventioninvolvesprotectingbothcorporateandpersonalpropertyfromtheftorlossthroughothermeans(fire,vandalism,etc.),aswellasprovidingforthesafetyofthoseindividualswhoareworkingatorvisitingatthefacility.

PhysicalMaintenanceStaff

Thephysicalmaintenancestaff?janitors,plumbers,electriciansandfacilitiesplanners?canprovideadditionalinformationtoyourprojectteam.Althoughthisgroupisseldomincludedinprojectrequirementsplanningsessions,theirinputcanbeessential.

Facilities

Assessingthefacilitiesavailabletoyourprojectteamforthedesignanddevelopmentphases,aswellasidentifyingthephysicallocationsforhardwareandforthelong-termwarehouseoperationsteamarevitaltoinsuringtheprojectcanbecompleted.

Ifyouareunabletofindworkspaceandmeetingroomsforyourteammembers,theprojectwillbedelayedatbest.Ifyoufailtoidentifytheroominwhichyourdatabaseserverwillbelocated?andreservetherequiredspaceandutilitiesassoonaspossible?youmayfindyourselfwithacompleteprojectplanandexcellentdesigndocumentation,butwithnoplacetosetthehardware.

Youwillalsoneedtoverifythatteammemberscanaccess,bothphysicallyandthroughtelecommunications,thevariouslocationsassociatedwithyourproject.Theyalsoneedtoknowthedatesandtimesaccessisvalidforeachfacility,andwhat,ifany,priornotificationsandapprovalsarenecessary.

Foreachoftheinvolvedfacilities,youneedatleastsomeparticipationfrompersonnelassignedtothatfacility,primarilythefacilitiesmaintenancestaff.Thesearethepeoplewhocanbestadviseyouonthosefactorsthatwilllimityouroptionsinthisrespect.

MaintenanceSchedules

Thefacilitiesmaintenanceteamswillalsobeabletoadviseyourteamastoscheduledmaintenanceactivitiesthatwillaffectyourprojectschedule.Suchmaintenanceactivitiesinclude:newconstruction,repairs,telecommunicationsupdates,powergridmaintenanceandlargescalepersonnelrelocations.

Thefacilitiesteammemberswillalsobeabletoadviseyourprojectteamonestimateddowntimesforunscheduledmaintenanceactivities.Theseactivitiesaretypicallyassociatedwithexternalproblemscompletelyoutsidethecontrolofyourorganization.Yourorganizationwillmostlikelyhaveaplaninplacefortherestorationofservicesshouldanysuchproblemoccur.Thisistheinformationthatthefacilitiesteammemberscanprovideandthussaveyourteamtheeffortofreconstructingthesupportingestimatesanddisasterrecoveryplans.
由MySQL用来存储数据的文件格式以已经被广泛地测试过，但是总是有外部情况可以导致数据库表被破坏：

原来公司用过MYSQL自己也只是建个表写个SQL

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

where子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。

可能有的朋友会抱怨集成的orderby，其实如果使用ranking函数,Orderby是少不了的。如果担心Orderby会影响效率，可以为orderby的字段建立聚集索引，查询计划会忽略orderby操作（因为本来就是排序的嘛）。

现在是在考虑:如果写到服务器端,我一下搞他个10个存储过程导过去,那久之服务器不就成垃圾箱了吗?即便优化了我的中间层.

习惯敲命令行的朋友可能会爽一些。但是功能有限。适合机器跑不动SQLServerManagementStudio的朋友使用。

大侠们有推荐的书籍和学习方法写下吧。

我是新手，正在学习数据库和操作系统，深感理论的泛广，唯有一步一步来，但是又感觉时间不够，收集了很多资料却总是没能认真的看完，希望有一个讨论板块，大家共同解决，共同分享，共同努力

大家注意一点。如下面的例子：

也可谈一下你是怎么优化存储过程的?