MSSQL编程：创立自界说模板 Building Custom Templ...

对于update操作，只需要把event中的旧行和新行值对调即可。创立|模板December23,2002
UsingandBuildingQueryAnalyzerTemplates
ByGregoryA.Larsen



BuildingCustomTemplates
Bynowyoushouldbesomewhatfamiliarwithusingpre-existingtemplatestobuildyourSQLcode.Itisnowtimetolookathowyoucanleveragetemplatestostreamlineyouradministration.Notonlycanyouuseexistingtemplates,butyoucanalsomodifytheexistingtemplatesandbuildyourownhomegrowntemplates.

SQLServercomeswithasetoftemplateswhenyouinstallSQLServer.TheMicrosoftsuppliedtemplatesarestored(ifyouusedthedefaultinstallation)inadirectorycalled"C:ProgramFilesMicrosoftSQLServer80ToolsTemplatesQLQueryAnalyzer."Inthisdirectorythereareanumberofsubdirectories,oneforeachfolderyouseeontheQATemplatepane.IfyoudidnttakethedefaultwheninstallingSQLServer,youcanfindthetemplatedirectorybysearchingforfilesthathavea"tql"extension.

ForexamplepurposesIamgoingtocreateanewtemplatetosupportcreatingadatabasebackup.Toensurethatmyhomegrowntemplatesarestoredseparatelyfromthestandardones,Iwillbuildanewtemplatefoldertoplacemycustomtemplates.Alltemplatesyoucreatemusthavea"tql"extentioninordertoberecognizedasatemplate.

BeforeIbuildmydatabasebackuptemplate,lcreateanewdirectoryforallmyhomegrowntemplatescalled"MyTemplates."ThisnewdirectoryiscreatedunderthelocationwhereallthestandardMicrosofttemplatesarestored.Inmycase,IwillcreateanewtemplatedirectorycalledC:ProgramFilesMicrosoftSQLServer80ToolsTemplatesQLQueryAnalyzerMyTemplates."NowIamreadytobuildatemplate.

ThetemplateIamgoingtocreateiscalled"DatabaseBackupToDiskFile.tql."WhateverInamethetemplateiswhatwillbedisplayedinthe"Template"paneinQA,minusthe"tql"extention.IwilluseNOTEPADtocreatethisnewtemplatethatwillbuildasimple"BACKUPDATABASE"command.Thetemplatebuiltwilllooklikethis:

--=========================================================--Backupdatabasetodiskfile--=========================================================backupdatabasetodisk=<disk_name,varchar(300),C:mssqlackup><db_name,varchar(128),dba>_<version,varvhar(100),ADHOC>.bak



Thistemplatecontainsthreedifferentparameters.Thefirstparameterisdb_nameandisusedtoidentifythedatabasethatwillbebackedup.Asyoucansee,thisparameterisdefinedasavarchar(128),anddefaultsto"DBA."Thesecondparameterisdisk_name,whichdefaultstothestandardplacefordatabasebackupsonmymachine.Thethirdparameterprovidesawaytospecifytheversionnameforthebackup.

NowIwillputthefinaltouchesonthescriptIambuilding.SofarIhaveusedtwostandardtemplatestocreatemyscript,whichcontainsacreatedatabaseandacreatetablestatement.NowIamgoingtousemycustombuilt"DatabaseBackupToDiskFile"templatetoadda"DATABASEBACKUP"commandtotheendofmyscript.

WhenIdisplaytheQA"Template"pane,Inowseeanewfoldercalled"MyTemplates."IfforsomereasonIdontseethenewtemplate,Irightclickonthe"Templates"folderandchoosethe"Refresh"option.Noticebelowthenewlyaddedtemplatefolder"MyTemplates."





NextIclickonthe"+"signnexttothe"MyTemplate"foldertoexpand.Intheexpandedviewmynewtemplate"DatabaseBackupToDiskFile"willbedisplayed,asinthescreenshotbelow.





NowIclickontheDatabaseBackupToDiskFiletemplate,dragittotheQApane,anddropitattheendofmyQAscript.AfterIdraganddropmytemplate,myscriptlookslikethis:

--=============================================--BasicCreateDatabaseTemplate--=============================================IFEXISTS(SELECT*FROMmaster..sysdatabasesWHEREname=NDemo_DB)DROPDATABASEDemo_DBGOCREATEDATABASEDemo_DBGO--=============================================--Createtablebasictemplate--=============================================IFEXISTS(SELECTnameFROMsysobjectsWHEREname=NMyTableANDtype=U)DROPTABLEMyTableGOCREATETABLEMyTable(IDintNULL,Descriptionvarchar(50)NOTNULL)GO--=========================================================--Backupdatabasetodiskfile--=========================================================backupdatabase<db_name,varchar(128),DBA>todisk=<disk_name,varchar(300),C:mssqlackup><db_name,varchar(128),dba>_version,varvhar(100),ADHOC>.bak



Nowallthatislefttocompletemyscriptistoreplacetheparameters.OnceagainIusetheReplaceTemplateParametermenutoreplaceparameters.ThistimeIonlyneedtoenteravalueforthedb_nameparameter;Ienter"Demo_DB."FortherestoftheparametersIwilltakethedefaultvalues.Myfinalscriptlooklikethis:

--=============================================--BasicCreateDatabaseTemplate--=============================================IFEXISTS(SELECT*FROMmaster..sysdatabasesWHEREname=NDemo_DB)DROPDATABASEDemo_DBGOCREATEDATABASEDemo_DBGO--=============================================--Createtablebasictemplate--=============================================IFEXISTS(SELECTnameFROMsysobjectsWHEREname=NMyTableANDtype=U)DROPTABLEMyTableGOCREATETABLEMyTable(IDintNULL,Descriptionvarchar(50)NOTNULL)GO--=========================================================--Backupdatabasetodiskfile--=========================================================backupdatabaseDemo_DBtodisk=C:mssqlackupDemo_DB_ADHOC.bak



Conclusion
Asyoucansee,templatesareusefulforbuildingscripts.Templatescansaveyouvaluabletimewhendevelopingcode.Bybuildingyourowntemplatesandcustomizingtheexistingtemplates,youcanstreamlineyourdevelopmentprocessandeliminatesyntaxerrors.Nexttimeyouwritesomecodethatmightbeusedoverandoveragain,considerusingitasabaseforbuildingaQAtemplate.
MySQL最初的开发者的意图是用mSQL和他们自己的快速低级例程(ISAM)去连接表格。经过一些测试后，开发者得出结论：mSQL并没有他们需要的那么快和灵活。

无法深入到数据库系统层面去了解和探究

每天坚持做不一样的是，认真做笔录，定时复习。一个月你就可以有一定的收获。当然如果你想在sql方面有一定的造诣，你少不了需要看很多很多的书籍了。

SP4是一个累积性的ServicePack，包含自以前的ServicePack发布以来所有的修补程序（包括MS03-031安全公告）。

而SQLServer如果能像Oracle一样可以为登陆分配如：5%的cpu，10%的内存。就可以解决这个漏洞。

连做梦都在想页面结构是怎么样的，绝非虚言

如果是将来做数据库的开发设计，就应该详细学习T-SQL的各种细节，包括T-SQL的程序设计、存储过程、触发器以及具体使用某个开发语言来访问数据库。

从项目平台的选择上讲，我们关心的，应该是一款产品能不能满足任务需求，而不是网上怎么说。