SQLServer2008数据库创建过程和可配置选项说课材料

1、Good is good, but better carries it.精益求精，善益求善。SQLServer2008数据库创建过程和可配置选项HYPERLINK/xupengnannan20070617/archive/2012/08/26/2655681.htmlSQLServer2008数据库创建过程和可配置选项系统数据库在安装SQLServer2008时，创建了5个系统数据库来存储系统信息和支持数据库操作。在普通的数据库操作中，我们可以看到四个系统数据库master、modle、msdb、tempdb，但是看不到第五个Resource数据库。如果SQLServer实例被配置为用于SQL

2、Server复制的分发服务器，就可以创建分发数据库。用户数据库用户数据库是由具有适当权限的任意服务器登录名创建。数据库规划数据库管理员应该考虑为什么数据库像现在这样运行和如何运行。管理数据库最好时机是在安装之前。无论是内部开发还是从软件供应商那里购买数据应用程序，数据库管理员都必须深入规划和创建支持数据库。规划一个数据库时必须决定的第一件事是需要多大的磁盘空间来支持该数据库。一个比较有效和现实的方法是先取得数据库原型（数据库的测试或开发版本），然后在其中填充适量的测试数据。之后，价差磁盘上数据文件的大小，然后将其乘以1.5.其结果应该足够容纳新数据库的初始数据量，而且还会有空间剩余。一旦数据库

3、投入生产环境，监控数据库文件大小就相当重要，因为这样可以分析增长趋势。例如可以配置为当数据库中填充的数据量达到75%是发出警报，这样可以在需要时增加文件的大小，但同时又能以足够的百分比在增长他们，从而避免经常执行增长。规划事务日志文件的大小更加复杂。要想精确地规划日志大小，必须知道数据库中执行的事务的平均大小、发生的频率以及被修改的表的物理结构。创建数据库新建数据库时可使用命令CREATEDATABASESampleDB，但是这样生成的数据库文件在系统默认的文件夹下，不推荐使用，可以使用图形界面进行创建。在新建数据库界面中“所有者”字段的值为“”，这时创建数据库时使用的登录帐户。这个帐户一般是

4、Sa，这是一个内置的SQLServer系统管理员帐户。数据库所有者应该总是为Sa，这样可以避免任何可能发生的问题。HYPERLINK数据库的所有者可以完全控制该数据库，要修改数据库的所有权，可以使用TSQL语句ALTERAUTHORIZATION指定任意的有效的登录名，如：ALTERAUTHORIZATIONONDATABASE:SampleDBTOSA想要检索数据库信息有两种不同的方法，sp_helpdb存储过程可用于检索所有数据库或特定数据库的信息，非常易于用作快速查看，要检索所有数据库，执行存储过程时不使用参数。对于特定数据库，将数据库名称传递给该存储过程，如：USERMasterGOE

5、XECsp_helpAdventureWorks2008还有一种检索数据库信息的方法是是使用目录视图，他们提供的信息比相应的存储过程多，且允许使用标准的TSQL命令，如WHERE和GROUPBY。如使用sys.database目录视图与sys.server_principals目录视图连接起来，查看服务器上所有数据库的基本信息：SELECTASdatabase_name,ASowner,db.create_date,patibility_level,db.recovery_model_descFROMsys.databasedbINNERJOINsys.server_principalsspO

6、Ndb.owner_sid=sp.sid数据库文件在“新建数据库”对话框中的“数据库文件”部分中，第一个数据文件的逻辑名称和第一个日志文件的逻辑名称已经被自动命名，单击对话框底部的“添加”按钮，就会像数据库文件中添加一个新的文件行，新的文件默认类型为“行数据”，也可以通过下拉列表中选择“日志”或“行数据”，一旦创建好数据库，文件类型不可改变。然后在文件组列中单击下拉列表，选择文件组文件组数据库时基于组织在文件组中的文件创建的。文件组是用来存放为数据库定义的所有数据和数据库对象的数据文件的逻辑分组。唯一必须的文件组是主文件组，主要由主数据文件和其他用户定义的数据文件组成。主数据文件的作用是存储针

7、对数据库的所有系统引用，包括指向Resource数据库中定义的对象的指针。如果作为默认文件组，则主文件组包含用户定义的对象以及系统创建的对象的所有对象定义。除了主文件组意外，还可以创建更多的用户定义文件组。使用用户定义文件组的一个最大好处是控制，如果没有用户定义文件组，那么所有数据都会存储在主文件组中，这样数据库的灵活性和可扩展性都大大削弱。输入新文件组的名称，选中“默认值”复选框，HYPERLINK然后单击“确定”按钮，这会把新的用户定义的文件组设置为默认文件组，用户创建的所有对象都会放在这个新的文件组中。这实际上就把系统数据和用户数据分离开来，从而获得对数据库结构的更多控制。使用文件组的一

8、个不错的功能就是可以将该文件组中的数据标记为“只读”，方法是选择“新建文件组”对话框中的“只读”复选框。当在一个数据库中组织不同的对象时，这个选项相当有用，要改变的对象可以放在一个可更ixnd文件组中，而不会改变的对象放在一个只读的文件组中，分离对象可以减少需要备份和还原的数据量，对于大型数据库来说是一个很有用的选项。文件组带来的性能优势主要有三方面，第一个方面是可以并行读写，这时通过将数据文件分割到多个物理设备实现的。然后，如果把多个物理我呢间放到单个文件组中，可以可获得同样的性能；第二个方面则是将非聚集索引和大型对象数据溢出作为常规数据空间而保留的文件组，将非聚集索引与数据分离可以让数据库

9、引擎同时使用独立的线程从索引中搜搜行位置和从表中检索行，将不经常访问的大型对象数据和事务密集的关系数据分离还可以提升一些实例中的扫描性能；第三个方面，也是最显著的方面，是可以跨多个文件组物理分区大型表。选项在“新建数据库”对话框中的左边点击“选项”按钮，会看到各类组织级别的选项ANSINULL默认值：在CREATETABLE或ALTERTABLE操作中添加至表中的列是否允许空置，默认值为False，也就是除非有显示指定，否则添加的列不允许空值，可以手动进行设置-更改默认值SETANSI_NULL_DELT_ONOFFSETANSI_NULL_DELT_ONON更改数据库的选项值ALTERDAT

10、ABASEAdventureWorks2008SETANSI_NULL_DEFAULTOFFALTERDATABASEAdventureWorks2008SETANSI_NULL_DEFAULTONANSI警告已启用：当设置为True时，只要聚合函数中出现空值，数据库引擎就会发出警告，设定为False时，则不会发出警告，可以在连接级别或者数据库级别设置SETANSI_WARNINGSOFFSETANSI_WARNINGSONALTERDATABASEAdventureWorks2008SETANSI_WARNINGSOFFALTERDATABASEAdventureworks2008SETAN

11、SI_WARNINGSON算术终止启用：当设置为true的时候，任何语句或事务在遇到算术溢出或被零除错误后都将终止，设定为False的时候，会发出一个警告，但不是终止语句。同样可以在连接级别或者数据库级别设置：SETARITHABORTOFFSETARITHABORTONALTERDATABASEAdventureworks2008SETARITHABORTOFFALTERDATABASEAdventureworks2008SETARITHABORTON自动创建统计信息：设置为True的时候，数据库引擎为那些JOIN操作的WHERE子句或ON子句中引用的、缺少统计信息的非索引列生成统计信息AL

12、TERDATABASEAdventureworks2008SETAUTO_CREATE_STATISTICSOFFALTERDATABASEAdventureworks2008SETAUTO_CREATE_STATISTICSON自动更新统计信息：设置为True时，数据库引擎会自动更新列的统计信息，从而保持最有效的查询计划，如果设置为False则需要数据库管理员手动更新统计信息ALTERDATABASEAdventureworks2008SETAUTO_UPDATE_STATISTICSOFFALTERDATABASEAdventureworks2008SETAUTO_UPDATE_STATI

13、STICSON自动异步更新统计信息：设置为True时，查询中发现的国企统计信息将被更新，但发现这些国企统计信息时正在执行的查询不会等待新的统计信息，后续的查询将会利用新的统计信息，当设置为False的时候，只有统计信息更新之后才进行查询编译：ALTERDATABASEAdventureworks2008SETAUTO_UPDATE_STATISTICS_ASYNCOFFALTERDATABASEAdventureworks2008SETAUTO_UPDATE_STATISTICS_ASYNCON默认游标：游标始终基于生命它的连接。当设置为Global时，制定了生命的游标可被统一连接上执行的任意

14、批处理、存储过程和触发器引用。如果设置为Local，游标只能在声明了它的批处理、存储过程或触发器中引用ALTERDATABASEAdventureworks2008SETCURSOR_DEFAULTLOCALALTERDATABASEAdventureworks2008SETCURSOR_DEFAULTGLOBAL验证页：验证页为数据库管理员可以为写页设定不同的选项。可用的选项包括Checksun，Torn_Page_Detection和None。对于性能来说最好的选择的None，但是此设置会使数据库无法检测到数据损坏。Checksun提供了最好的保证，无论是数据写操作还是写操作之后发生的对磁

15、盘数据的任意修改都会被校验和验证检测到，但是会占用最多的CPU周期；Torn_Page_Detection选项是一种检测损坏页的低成本方法，但是只会检测在写操作期间发生的页损坏，推荐设置是ChecksunALTERDATABASEAdventureworks2008SETPAGE_VERITYNONEALTERDATABASEAdventureworks2008SETPAGE_VERITYTORN_PAGE_DETECTIONALTERDATABASEAdventureworks2008SETPAGE_VERITYCHECKSUM参数化：默认情况下数据库引擎将一些查询参数化，这样即使在WHER

16、E子句中定义了不同的值，创建并编译的查询计划也可重用，如果设置为简单，SQLServer会决定参数化哪些查询和不参数化哪些查询；设置为强制时，SQLServer会把所有可参数化的查询参数化，而同样的查询会得到一个参数化查询计划。强制自动参数化优势能改善性能，但是需要仔细监控以确保它对性能没有负面影响ALTERDATABASEAdventureworks2008SETPARAMETERIZATIONSIMPLEALTERDATABASEAdventureworks2008SETPARAMETERIZATIONFORCED允许带引号的标识符：默认情况下SQLServer使用方括号来界定对象，只有在

17、对象名中包含嵌入的空格或保留字时才需要界定对象。ANSI标准定界符是双引号，下面的例子说明了如何使用方括号和双引号创建和引用一个包含嵌入空格的对象：USERAdventureworks2008GOCREATETABLESales.USACustomers(AccuNumberintIDENTITY(1,1)NOTNULL,LastNamevarchar(75)NOTNULL,FirstNamevarchar(75)NOTNULL)SELECFTAcctNumber,LastName,FirstNameFROMSales.USACustomersUSERAdventureworks2008GOC

18、REATETABLESales.USACustomers(AccuNumberintIDENTITY(1,1)NOTNULL,LastNamevarchar(75)NOTNULL,FirstNamevarchar(75)NOTNULL)SELECFTAcctNumber,LastName,FirstNameFROMSales.USACustomers当“允许带引号的标识符”为True的时候，方括号和双引号都可以使用，为False的时候，值有方括号定界符可以使用，要在连接级别或者数据库级别进行设置的时候如下SETQUOTED_IDENTIFIEROFFSETQUOTED_IDENTIFIERON

19、ALTERDATABASEAdventureworks2008SETQUOTED_IDENTIFIEROFFALTERDATABASEAdvetnureworks2008SETQUOTED_IDENTIFIERON递归触发器已启用：递归触发器是一项高级编程技术，它允许统一触发器在同一事务中按顺序执行多次。当设置为False是，这一操作时不允许的。ALTERDATABASEAdventureworks2008SETRECURSIVE_TRIGGERSOFFALTERDATABASEAdventureworks2008SETRECURSIVE_TRIGGERSON限制访问：限制访问选项使数据库管理

20、员可以把对数据库的访问限制为一组已定义的登录名。该选项的默认值是MULTI_USER，允许多个无权限的用户访问数据库，此外还有两个选项SINGLE_USER和RESTRICTED_USER，如果设置为SINGLE_USER，那么一次就只有一个用户帐户可以访问数据库，如果设置为RESTRICTED_USER，那么只有db_owner,dbcreator或者sysadmin的成员可以连接至数据库。要在数据库级别进行设置，可以使用如下命令ALTERDATABASEAdventureworks2008SETMULTI_USERALTERDATABASEAdventureworks2008SETREST

21、RICTED_USERALTERDATABASEAdventureworks2008SETSINGLE_USER生成数据库创建脚本几乎每一个创建或修改数据库对象的配置屏幕都包括脚本操作选项。单击“脚本”按钮可用的脚本操作选项会显示，单击任何脚本操作都将生成一个脚本，脚本复制您在图形化界面中指定的所有设置。然后通过这个脚本，可以使用同样的选项创建新的数据库，值需要改变数据库和相关文件的逻辑及物理名称即可。另外一种重用脚本的方法是使用变量替代对象和文件的实际名称，接下来只需要更新变量值并执行脚本，创建数据定义语言DataDefinitionLanguageDDL脚本时唯一棘手的部分是必须使用动态S

22、QL，因为在DDL脚本中不能直接使用变量，下面的例子演示了如何使用动态SQL来创建一个新的数据库，并将一个用户定义的文件组标记为默认文件组：DECLAREDatabaseNameASnvarchar(255)DECLAREFileGroupNameASnvarchar(255)SETDatabaseName=NSlateGravelSETFileGroupName=NUserDataEXECUTE(CREATEDATABASE+DatabaseName+ONPRIMARY(NAME=+DatabaseName+,FILENAME=S:SQLDataFiles+DatabaseName+_dat

23、a.mdf,SIZE=20MB,MAXSIZE=100MB,FILEGROWTH=30%),FILEGROUPUserData(NAME=+FileGroupName+,FILENAME=S:SQLDataFiles+DatabaseName+_data.ndf,SIZE=2048KB,FILEGROWTH=20%)LOGON(NAME=+dATABAEnAME+_log,FILENAME=T:SQLLogFiles+DatabaseName+_log.ldf,SIZE=100MB,FILEGROWTH=20%);ALTERDATABASE+DatabaseName+MODIFYFILEGRO

24、UP+FileGroupName+DEFAULT)架构架构是一种对象容器,在SQLServer2008中引用数据库对象是，设定对象引用的上下文是很重要的。每个用于都被指派一个默认的架构，当他们登录到SQLServer应引用数据库对象时，这个默认架构将在该对象的引用方式中发挥独特的作用。例如，假设在AdventureWorks2008数据库中创建了一个名为FredF的用户，并把默认架构Sales指派给他。如果FredF登录并执行SELECT*FROMCreditCard查询，由于其默认架构师Sales，CreditCard将被解析为Adventureworks2008.Sales.CreditC

25、ard。由于Sales.CreditCard表存在，因此查询将返回该表的内容，如果FredF执行SELECT*FROMPerson查询，Person将被解析为Adventureworks2008.Sales.Person，这是一个并不存在的表。因此，SQLServer会返回“无效的对象名称”这个错误。要创建架构唯一需要的信息是指定架构名称，架构的所有权默认属于运行创建脚本的用户，但可以把任何有效的数据库用户指定为所有者，最简单的方法就是把dbo指定为架构所有者USERAdventureWorks2008GOCREATESCHEMAOperationsAUTHORIZATIONdbo该CREAT

26、ESCHEMA语句之后的任何架构作用于的语句都将位于刚才创建的架构的作用于内。记住，架构作用于总是起始于用户的默认架构，如果一个被引用的对象不是作用域限定的，解析将返回到dbo架构如果视图删除一个包含对象的架构，将会产生错误，如果仍然需要架构中的对象，可以使用ALTERSCHEMA语句把它转移到另一个架构中，如把表DeliveryDriver从Operations架构转移到Production架构中。ALTERSCHEMAProductionTRANSFEROperations.DeliveryDriver不能从数据库中删除拥有架构的对象，这就是为什么让dbo用户拥有所有架构的原因之一。要想改

27、变一个架构的所有权，需要改变架构的AUTHORIZATION属性：ALTERAUTHORIZATIONONSCHEMA:OperationsTOFredF表表的排序规则：创建数据库时，可以配置不同于服务器的排序规则支持。对于包含字符数据的表列来说也是如此，如创建一个各地客户可以使用自己的语言浏览器和搜索产品目录的表：USERAdventureWorks2008GOCREATETABLEProduction.GlobalProductDescription(ProductDescriptionIDintIDENTITY(1,1)NOTNULL,EnglishDescriptionnvarchar

28、(400)COLLATESQL_Latin1_General_CP1_CI_ASNULL,FrenchDescriptionnvarchar(400)COLLATEFrench_CI_ASNULL,ChineseDescriptionnvarchar(400)COLLATEChinese_PRC_CI_AINULL,ModifiedDatedatetimeNOTNULL)SQLServer绝对不是使用饭一起，只是提供了一个框架来存放多种语言。您需要准备产品描述的正确翻译并把它们放到合适的列中，另外还要处理任何因为tempdb的排序规则导致的爱需规则不兼容性。表的体系结构：SQLServer使用

29、8KB的数据页春初信息，表中的所有数据都存储在这些数据页中，但数据在页中的组织方式会因表的创建方式和在表创建后所进行的操作有所不同。默认情况下，所有的数据以称为堆的无组织的方式进行存储。SQLServer并不尝试组织或是以任何方式排序数据，也不维护页间的连接。SQLServer将所有数据页存放在分区的逻辑单元中，除非明确分隔，否则表一般存储在定义于单个文件组上的单个分区中。不过，通过跨多个由文件组定义管理的文件横向分区表，SQLServer可以把大型表划分为较小的可管理单元。如下创建实验表并进行差序性能测试：-创建新表USERAdventureWorks2008GOCREATETABLEdbo

30、.Transactions(TransactionIDintNOTNULL,ProductIDintNOTNULL,ReferenceOrderIDintNOTNULL,ReferenceOrderLineIDintNOTNULL,TransactionDatedatetimeNOTNULL,TransactionTypenchar(1)NOTNULL,QuantityintNOTNULL,ActualCostmoneyNOTNULL,ModifiedDatedatetimeNOTNULL)-填充新表USEAdventureWorks2008GOINSERTdbo.TransactionsSE

31、LECT*FROMProduction.TransactionHistoryUNIONALLSELECT*FROMProduction.TransactionHistoryArchive-查看查询性能USERAdventureWorks2008GODBCCDROPCLEANBUFFERSSETSTATISTICSIOONDECLAREBeginDateASdatetime,EndDateASdatetimeSETBeginDate=2002-01-01SETEndDate=2002-12-31SELECTSUM(Quantity)ASTotalQuantity,SUM(ActualCost)A

32、STotalCostFROMdbo.TransactionsWHERETransactionDateBETWEENBeginDateANDEndDate可以看到，为了满足查询要求，SQLServer不得不扫描表，现在对表进行分区，把该物理划分为多个文件，使所有的事务按照年份分开，看看会发生什么情况：-创建四个文件组USERMASTERGOALTERDATABASEAdventureWorks2008ADDFILEGROUPFGPre2002GOALTERDATABASEAdventureWorks2008ADDFILE(NAME=AworksPre2002,FILENAME=E:SQLData

33、AworksPre2002.ndf,SIZE=20MB,FILEGROWTH=20%)TOFILEGROUPFGPre2002GOALTERDATABASEAdventureWorks2008ADDFILEGROUPFG2002GOALTERDATABASEAdventureWorks2008ADDFILE(NAME=Aworks2002,FILENAME=E:SQLDataAworks2002.ndf,SIZE=20MB,FILEGROWTH=20%)TOFILEGROUPFG2002GOALTERDATABASEAdventureWorks2008ADDFILEGROUPFG2003GOA

34、LTERDATABASEAdventureWorks2008ADDFILE(NAME=Aworks2003,FILENAME=E:SQLDataAworks2003.ndf,SIZE=20MB,FILEGROWTH=20%)TOFILEGROUPFG2003GOALTERDATABASEAdventureWorks2008ADDFILEGROUPFG2004AndAfterGOALTERDATABASEAdventureWorks2008ADDFILE(NAME=Aworks2004AndAfter,FILENAME=E:SQLDataAworks2004AndAfter.ndf,SIZE=2

35、0MB,FILEGROWTH=20%)TOFILEGROUPFG2004AndAfterGO-创建分区函数CREATEPARTITIONFUNCTIONYearFunction(datetime)ASRANGERIGHTFORVALUES(1/1/2002,1/1/2003,1/1/2004)-将YearFunction创建的分区映射到之前创建的文件组CREATEPARTITIONSCHEMEYearSchemeASPARTITIONYearFunctionTO(FGPre2002,FG2002,FG2003,FG2004AndAfter)-将数据从原Transactions表中移至分区表中U

36、SERAdventureWorks2008GOCREATETABLEdbo.PartitionedTransactions(TransactionIDintNOTNULL,ProductIDintNOTNULL,ReferenceOrderIDintNOTNULL,ReferenceOrderLineIDintNOTNULL,TransactionDatedatetimeNOTNULL,TransactionTypenchar(1)NOTNULL,QuantityintNOTNULL,ActualCostmoneyNOTNULL,ModifiedDatedatetimeNOTNULL)ONYe

37、arScheme(TransactionDate)GOINSERTINTOdbo.PartitionedTransactionsSELECT*FROMdbo.Transactions-查询性能测试USERAdventureWorks2008GODBCCDROPCLEANBUFFERSSETSTATISTICSIOONDECLAREBeginDateASdatetime,EndDateASdatetimeSETBeginDate=2002-01-01SETEndDate=2002-12-31SELECTSUM(Quantity)ASTotalQuantity,SUM(ActualCost)AST

38、otalCostFROMdbo.PartitionedTransactionsWHERETransactionDateBETWEENBeginDateANDEndDate创建表分区还可以通过右击表选择存储，选择创建分区，通过GUI进行创建，这里略掉索引堆用来存储数据很好，也能有效地处理新纪录，但是要在表中寻找特定数据时就没有那么好用了。这就是索引发挥作用的地方那个。SQLServer支持两种类型的索引，聚集索引和非聚集索引。它还支持XML索引和空间索引等其他类型索引，这这些索引和普通索引的关系索引打不相同，后者将来用在数据库表中定位大多数数据。聚集索引和非聚集索引之间的主要区别在于索引的叶级。

39、在非聚集索引中，叶级包含数据的指针。在聚集索引中，叶级包含实际数据。聚集索引：表的所有数据可存储在堆中或聚集索引中，堆和聚集索引是相互排斥的。堆是一个无组织的表行集合，而聚集索引是一个有组织的表行集合。聚集键在索引中必须是唯一的，但创建索引时不必将此列标记为唯一的。当在未标记的唯一的列上创建聚集索引时，SQLServer会生成一个隐藏列，它保存一个名为唯一标识符的4字节内部值来唯一标识重复的聚集索引键。聚集索引的叶级是实际的数据行，而不是数据指针。非聚集索引：非聚集索引更像是一本书后面的索引。当找到索引值时，并不能找到实际数行，只是获得指定实际数据行的位置的指针。堆上的非聚集索引：当在一个以堆

40、形式组织的表上构建非聚集索引时，索引列根据指向数据实际位置的指针分类，该指针由文件ID，页ID和数据所在的页槽号组成，如数据是第一个文件行总第84593页上的第20条记录，那么SQL将使用指针值1：84593：20，这使得SQLServer能够在索引找到数据之后快速访问数据。聚集索引上的非聚集索引：当在一个聚集索引上构建非聚集索引时，索引中的指针值就是数据行的聚集索引键值。一旦定位到索引值，SQLServer就是用聚集键导航聚集索引来检索所有需要的列。包含列：非聚集索引的功能可以通过向索引的节点添加非键值得到提升，这就可使得索引覆盖更多的查询，减少为检索额外值而历遍聚集索引的次数。包含列可以提

41、高查询覆盖率，而不会导致符合索引键的开销。索引中标记为included的列值出现在索引的页节点中，在行排序中不做考虑。CREATENONCLUSTEREDINDEXIX_Person_LastNameONPerson.Person(LastName)INCLUDE(FirstName)筛选索引：筛选索引时优化非聚集索引，它允许在数据子集上创建索引，使得索引结构更小从而减少了构建索引的时间和索引维护的成本，对于包含大量NULL值或包含数据范围的列上的索引，筛选索引特别有用：CREATENONCLUSTEREDINDEXIX_ListPrice_ProductONProduction.Produc

42、t(ListPrice)WHEREListPrice800.00分层索引：HierarchyId是SQLServer2008中引入的一种新类型数据。为了帮助检索分层数据，可使用两种不同的方法在此类型上的列构建索引，广度优先索引和深度优先索引。广度优先索引将统一级别的所有记录组合到一起，这样SQLServer就可以非常快速地相应具有共同父节点的所有记录的查询。空间索引：SQLServer2008通过两种新的CLR数据类型支持空间索引，geometry和geography，geometry数据类型用于平面空间而geography数据类型用于地形测量空间。创建空间索引需要两个阶段：分解和镶嵌。在分解

43、阶段，SQLServer将一个有限区域划分为一个网格结构，在镶嵌阶段，表中的每个空间值都映射到每个网格级别。XML索引：凭借XML上建立索引的功能，能够帮助定位和检索XML文本中的特定数据。XML数据早SQLServer数据库中存储为BinaryLargeObject（BLOB，二进制大型对象）。要在XML中搜索特定元素，属性或者值，SQLServer必须首先打开BLOB，然后将其内容拆解开。SQLServer通过拆解操作创建一个自己可以导航的XML对象的集合。它实质上是提取了XML的数据结构，然后将其存储在临时的关系结构中。XML主索引：主XML索引实际并不是构建在XML列上，而是构建在内部

44、表（在索引创建过程中创建）之上的聚集索引。这个内部表称为节点表。节点表直接连接至在其中创建XML索引的表的聚集索引。要创建一个XML索引，具有XML列的表必须在其逐渐上有一个聚集索引。创建语法如下：USEAdventureWorks2008GOCREATEPRIMARYXMLINDEXXML_IX_IllustrationONProduction.Illustration(Diagram)PATH辅助XML索引：PATH辅助XML索引可以提升对XML列指定路径表达式的XML查询的性能。PATH辅助索引建立爱主XML索引提供的节点之上，语法如下：USERAdventureWorks2008GOC

45、REATEXMLINDEXIXML_MyPerson_AdditionalContactInfo_PathONdbo.MyPerson(AdditionalContactInfo)USINGXMLINDEXPXML_MyPerson_AdditionalContactInfoFORPATHVALUE辅助XML索引：VALUE辅助索引用来支持没有完全指定路径的或采用通配符来搜索值的XML查询。语法如下：CREATEXMLINDEXIXML_MyPerson_AdditionalContactInfo_ValueONdbo.MyPerson(AdditionalContactInfo)USINGX

46、MLINDEXPXML_MyPerson_AdditionalContactInfoFORVALUEPROPERTY辅助XML索引：PROPERTY辅助索引用于优化通过制定节点的完整路径检索节点值的查询。语法如下：CREATEXMLINDEX_IXML_MyPerson_AdditionalContactInfo_PropertyONdbo.MyPerson(AdditionalContactInfo)USINGXMLINDEXPXML_MyPerson_AdditionalContactInfoFORPROPERTY维护表我们已经深入了解了数据在表中的组织方式和优化数据检索的方法，接下来套路

47、如何对这个环境进行维护，表的维护基本可以分为两种：索引的维护和索引统计信息的创建和维护索引在更新时会变得支离破碎，这是因为索引时一个连续的、排序的数据集合。要维护索引的排序顺序，SQLServer必须分割完整的数据页以容纳更多的数据。这些也拆分导致产生了索引碎片，索引的碎片化将最终导致SQLServer在检索数据时执行过量的读取，从而造成性能低下。要检查表上的所有索引或一些特定索引的碎片，可以使用动态管理函数sys.dm_db_index_physical_stats，此函数会返回表的索引的大量信息，包括每个数据页上的数据量、索引的叶级和非叶级上的碎片量，以及索引中记录的平均大小。当擦和讯这个

48、表值函数时，最感兴趣的是碎片级别和每一页填充的平均百分比。SELECT*columnlistFROMsys.dm_db_index_physical_stats(databae_id|NULL,object_id|NULL,index_id|NULL,partition_number|NULL,mode|NULL|DEFAULT)DECLAREdbIDsmallint,objectIDintSETDbID=DB_ID(AdventureWorks2008)SETobjectID=OBJECT_ID(dbo.MyPersons)SELECTindex_id,avg_gragmentation_i

49、n_percent,avg_page_space_used_in_percentFROMsys.dm_db_index_physical_stats(DbID,ObjectID,NULL,NULL,DETAILED)WHEREindex_level=0为减少页拆分产生的碎片，数据库管理员可以设计或重新生成索引，使数据页不会完全填充。为此，可以使用填充因此，当建立或重新生成索引时，可以指定填充因子百分比，如果一个索引页只有90%被填充，那么只有插入更多的索引时才会导致页拆分，虽因产生碎片的时间会久些。个人认为，索引的填充因子最好不要低于90%，在更新和查询都很频繁的表上，这个比例可以低至85%。

50、要去除碎片可以有三种方法：删除并重新生成索引、原地重新生成索引或重新组织索引。-使用DROP_EXISTING选项创建索引CREATEUNIQUECLUSTEREDINDEXPK_Address_AddressIDONPerson.Address(AddressID)WITH(fillfactor=90,DROP_EXISTING=ON)-重新生成索引USEAdventureWorks2008GOALTERINDEXAK_Product_ProductNumberONPerson.ProductREBUILDWITH(FILLFACTOR=90,ONLINE=ON)USEAdventureWo

51、rks2008GOALTERINDEXALLONPerson.ProductREBUILDWITH(FILLFACTOR=90,ONLINE=ON)重新组织索引消耗的系统资源最少，但不如重新生成索引来的彻底。强制数据完整性主键约束：一个表有且只能有一个主键约束。它用来唯一标识表中的每一行。主键约束可定义在单个列或者列组合上。SQLServer使用方法是在参与主键的列上创建唯一的索引。在不排序的情况下来强制唯一性会非常低效。SQLServer这方面的问题是，如果聚集索引不存在，他会默认一个唯一的聚集索引。决定什么列参与主键和决定什么列定义表数据的物理结构是完全不同的。不应假定一个主键同时也是表的

52、聚集键。记住，表的所有聚集索引将包含聚集索引键作为指向数据行的指针。如果主键很长，使用聚集索引支持主键对于非聚集索引的存储和检索是非常不利的。USERAdventureWorks2008GOCREATETABLEdbo.CreditCards(CreditCardIDintIDENTITY(1,1)NOTNULLCONSTRAINTPK_CreditCardIDPRIMARYKEYNONCLUSTERED(CreditCardID),CardTypenvarchar(50)NOTNULL,CardNumbernvarchar(25)NOTNULL,ExpMonthtinyintNOTNULL,

53、ExpYearsmallintNOTNULL,ModifiedDatedatetimeNOTNULL)USERAdventureWorks2008GOCREATETABLEdbo.CreditCards(CreditCardIDintIDENTITY(1,1)NOTNULL,CardTypenvarchar(50)NOTNULL,CardNumbernvarchar(25)NOTNULL,ExpMonthtinyintNOTNULL,ExpYearsmallintNOTNULL,ModifiedDatedatetimeNOTNULL,CONSTRAINTPK_CreditCardIDPRIMA

54、RYKEYNONCLUSTERED(CreditCardID)ALTERTABLEdbo.CreditCardsADDCONSTRAINTPK_CreditCardIDPRIMARYKEYNONCLUSTERED(CreditCardID)唯一约束：虽然一个表只能有一个主键约束，但是它可以有很多唯一约束。要创建唯一约束，有两个选择，在表上创建唯一索引或唯一约束。唯一索引和唯一约束的行为相似，而SQLServer将创建唯一索引来强制唯一约束。ALTERTABLEdbo.DriverADDCONSTRAINTUX_LincenseNumUNIQUENONCLUSTERED(LicenseNum)C

55、REATEUNIQUENONCLUSTEREDINDEXINDEXUX_LincenseNumONdbo.Driver(LicenseNum)外键约束：外键约束用来保证表之间的完整性，要在表上创建一个外键约束，定义在外键中的列必须映射到主键表中的列，这些列被指定为主键或者有唯一约束（唯一约束和唯一索引都可以）。外键约束有一些高级选项，他们可以在创建中和创建后改变外键约束的行为。-WITHCHECK添加外键约束时的默认设置，设置指定了应对外键表中任何现有数据进行验证ALTERTABLEdbo.DriverRecordWITHCHECKADDCONSTRAINTFK_DriverRecord_Dr

56、iverFOREIGNKEY(DriverID)REFERENCESdbo.Driver(DriverID)-WITHNOCHECK，设定了部队现有数据进行验证来使其符合新的约束ALTERTABLEdbo.DriverRecordWITHNOCHECKADDCONSTRAINTFK_DriverRecord_DriverFOREIGNKEY(DriverID)REFERENCESdbo.Driver(DriverID)级联约束：外键默认组织更新或删除父值，然而有些时候这种行为并不合适。SQLServer提供了指定在福记录被删除或更新的情况下对子记录采取何种行为的选项。ONDELETENOACTION和ONUPDATENOACTION是外键的默认设置。这些设定指定，任何试图删除或更新由其他表中现有行的外键所引用的键值的行为都会失败。此外还有CASCADE,SETNULL和SETDEFAULT选项，他们允许删除或更新键值，以定义的方式级联至定义为拥有外键关系的表-ONDELETECASCADE如果父行被删除，子记录也删除ALTERTABLEdbo.DriverRecordWITHNOCHECKADDCONSTRAINTFK_DriverRecord_DriverFOREIGNKEY(DriverID)REFERENCES