SQLServer2008数据库创建过程和可配置选项

系统数据库

　　在安装SQL Server 2008时，创建了5个系统数据库来存储系统信息和支持数据库操作。在普通的数据库操作中，我们可以看到四个系统数据库master、modle、msdb、tempdb，但是看不到第五个Resource数据库。如果SQL Server实例被配置为用于SQL Server复制的分发服务器，就可以创建分发数据库。

用户数据库

　　用户数据库是由具有适当权限的任意服务器登录名创建。

数据库规划

　　数据库管理员应该考虑为什么数据库像现在这样运行和如何运行。管理数据库最好时机是在安装之前。无论是内部开发还是从软件供应商那里购买数据应用程序，数据库管理员都必须深入规划和创建支持数据库。

　　规划一个数据库时必须决定的第一件事是需要多大的磁盘空间来支持该数据库。一个比较有效和现实的方法是先取得数据库原型（数据库的测试或开发版本），然后在其中填充适量的测试数据。之后，价差磁盘上数据文件的大小，然后将其乘以1.5.其结果应该足够容纳新数据库的初始数据量，而且还会有空间剩余。一旦数据库投入生产环境，监控数据库文件大小就相当重要，因为这样可以分析增长趋势。例如可以配置为当数据库中填充的数据量达到75%是发出警报，这样可以在需要时增加文件的大小，但同时又能以足够的百分比在增长他们，从而避免经常执行增长。

　　规划事务日志文件的大小更加复杂。要想精确地规划日志大小，必须知道数据库中执行的事务的平均大小、发生的频率以及被修改的表的物理结构。

创建数据库

　　新建数据库时可使用命令CREATE DATABASE SampleDB，但是这样生成的数据库文件在系统默认的文件夹下，不推荐使用，可以使用图形界面进行创建。在新建数据库界面中“所有者”字段的值为“<默认值>”，这时创建数据库时使用的登录帐户。这个帐户一般是Sa，这是一个内置的SQL Server系统管理员帐户。数据库所有者应该总是为Sa，这样可以避免任何可能发生的问题。数据库的所有者可以完全控制该数据库，要修改数据库的所有权，可以使用TSQL语句ALTER AUTHORIZATION指定任意的有效的登录名，如：ALTER AUTHORIZATION ON DATABASE::SampleDB TO SA

　　想要检索数据库信息有两种不同的方法，sp_helpdb存储过程可用于检索所有数据库或特定数据库的信息，非常易于用作快速查看，要检索所有数据库，执行存储过程时不使用参数。对于特定数据库，将数据库名称传递给该存储过程，如：

USER MasterGOEXEC sp_help AdventureWorks2008

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

SELECT db.name AS database_name,sp.name AS owner,db.create_date,db.compatibility_level,db.recovery_model_desc FROM sys.database db INNER JOIN sys.server_principals sp ON db.owner_sid=sp.sid

数据库文件

　　在“新建数据库”对话框中的“数据库文件”部分中，第一个数据文件的逻辑名称和第一个日志文件的逻辑名称已经被自动命名，单击对话框底部的“添加”按钮，就会像数据库文件中添加一个新的文件行，新的文件默认类型为“行数据”，也可以通过下拉列表中选择“日志”或“行数据”，一旦创建好数据库，文件类型不可改变。然后在文件组列中单击下拉列表，选择文件组

文件组

　　数据库时基于组织在文件组中的文件创建的。文件组是用来存放为数据库定义的所有数据和数据库对象的数据文件的逻辑分组。

　　唯一必须的文件组是主文件组，主要由主数据文件和其他用户定义的数据文件组成。主数据文件的作用是存储针对数据库的所有系统引用，包括指向Resource数据库中定义的对象的指针。如果作为默认文件组，则主文件组包含用户定义的对象以及系统创建的对象的所有对象定义。除了主文件组意外，还可以创建更多的用户定义文件组。

　　使用用户定义文件组的一个最大好处是控制，如果没有用户定义文件组，那么所有数据都会存储在主文件组中，这样数据库的灵活性和可扩展性都大大削弱。输入新文件组的名称，选中“默认值”复选框，然后单击“确定”按钮，这会把新的用户定义的文件组设置为默认文件组，用户创建的所有对象都会放在这个新的文件组中。这实际上就把系统数据和用户数据分离开来，从而获得对数据库结构的更多控制。使用文件组的一个不错的功能就是可以将该文件组中的数据标记为“只读”，方法是选择“新建文件组”对话框中的“只读”复选框。当在一个数据库中组织不同的对象时，这个选项相当有用，要改变的对象可以放在一个可更ixnd文件组中，而不会改变的对象放在一个只读的文件组中，分离对象可以减少需要备份和还原的数据量，对于大型数据库来说是一个很有用的选项。

　　文件组带来的性能优势主要有三方面，第一个方面是可以并行读写，这时通过将数据文件分割到多个物理设备实现的。然后，如果把多个物理我呢间放到单个文件组中，可以可获得同样的性能；第二个方面则是将非聚集索引和大型对象数据溢出作为常规数据空间而保留的文件组，将非聚集索引与数据分离可以让数据库引擎同时使用独立的线程从索引中搜搜行位置和从表中检索行，将不经常访问的大型对象数据和事务密集的关系数据分离还可以提升一些实例中的扫描性能；第三个方面，也是最显著的方面，是可以跨多个文件组物理分区大型表。

选项

　　在“新建数据库”对话框中的左边点击“选项”按钮，会看到各类组织级别的选项

　　ANSI NULL默认值：在CREATE TABLE或ALTER TABLE操作中添加至表中的列是否允许空置，默认值为False，也就是除非有显示指定，否则添加的列不允许空值，可以手动进行设置

--更改默认值SET ANSI_NULL_DELT_ON OFFSET ANSI_NULL_DELT_ON ON更改数据库的选项值ALTER DATABASE AdventureWorks2008 SET ANSI_NULL_DEFAULT OFFALTER DATABASE AdventureWorks2008 SET ANSI_NULL_DEFAULT ON

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

SET ANSI_WARNINGS OFFSET ANSI_WARNINGS ONALTER DATABASE AdventureWorks2008 SET ANSI_WARNINGS OFFALTER DATABASE Adventureworks2008 SET ANSI_WARNINGS ON

　　算术终止启用：当设置为true的时候，任何语句或事务在遇到算术溢出或被零除错误后都将终止，设定为False的时候，会发出一个警告，但不是终止语句。同样可以在连接级别或者数据库级别设置：

SET ARITHABORT OFFSET ARITHABORT ONALTER DATABASE Adventureworks2008 SET ARITHABORT OFFALTER DATABASE Adventureworks2008 SET ARITHABORT ON

　　自动创建统计信息：设置为True的时候，数据库引擎为那些JOIN操作的WHERE子句或ON子句中引用的、缺少统计信息的非索引列生成统计信息

ALTER DATABASE Adventureworks2008 SET AUTO_CREATE_STATISTICS OFFALTER DATABASE Adventureworks2008 SET AUTO_CREATE_STATISTICS ON

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

ALTER DATABASE Adventureworks2008 SET AUTO_UPDATE_STATISTICS OFFALTER DATABASE Adventureworks2008 SET AUTO_UPDATE_STATISTICS ON

　　自动异步更新统计信息：设置为True时，查询中发现的国企统计信息将被更新，但发现这些国企统计信息时正在执行的查询不会等待新的统计信息，后续的查询将会利用新的统计信息，当设置为False的时候，只有统计信息更新之后才进行查询编译：

ALTER DATABASE Adventureworks2008 SET AUTO_UPDATE_STATISTICS_ASYNC OFFALTER DATABASE Adventureworks2008 SET AUTO_UPDATE_STATISTICS_ASYNC ON

　　默认游标：游标始终基于生命它的连接。当设置为Global时，制定了生命的游标可被统一连接上执行的任意批处理、存储过程和触发器引用。如果设置为Local，游标只能在声明了它的批处理、存储过程或触发器中引用

ALTER DATABASE Adventureworks2008 SET CURSOR_DEFAULT LOCALALTER DATABASE Adventureworks2008 SET CURSOR_DEFAULT GLOBAL

　　验证页：验证页为数据库管理员可以为写页设定不同的选项。可用的选项包括Checksun，Torn_Page_Detection和None。对于性能来说最好的选择的None，但是此设置会使数据库无法检测到数据损坏。Checksun提供了最好的保证，无论是数据写操作还是写操作之后发生的对磁盘数据的任意修改都会被校验和验证检测到，但是会占用最多的CPU周期；Torn_Page_Detection选项是一种检测损坏页的低成本方法，但是只会检测在写操作期间发生的页损坏，推荐设置是Checksun

ALTER DATABASE Adventureworks2008 SET PAGE_VERITY NONEALTER DATABASE Adventureworks2008 SET PAGE_VERITY TORN_PAGE_DETECTIONALTER DATABASE Adventureworks2008 SET PAGE_VERITY CHECKSUM

　　参数化：默认情况下数据库引擎将一些查询参数化，这样即使在WHERE子句中定义了不同的值，创建并编译的查询计划也可重用，如果设置为简单，SQL Server会决定参数化哪些查询和不参数化哪些查询；设置为强制时，SQL Server会把所有可参数化的查询参数化，而同样的查询会得到一个参数化查询计划。强制自动参数化优势能改善性能，但是需要仔细监控以确保它对性能没有负面影响

ALTER DATABASE Adventureworks2008 SET PARAMETERIZATION SIMPLEALTER DATABASE Adventureworks2008 SET PARAMETERIZATION FORCED

　　允许带引号的标识符：默认情况下SQL Server使用方括号来界定对象，只有在对象名中包含嵌入的空格或保留字时才需要界定对象。ANSI标准定界符是双引号，下面的例子说明了如何使用方括号和双引号创建和引用一个包含嵌入空格的对象：

USER Adventureworks2008GOCREATE TABLE "Sales.USA Customers"(AccuNumber int IDENTITY(1,1) NOT NULL,"Last Name" varchar(75) NOT NULL,"First Name" varchar(75) NOT NULL)SELECFT AcctNumber,"Last Name","First Name" FROM "Sales.USA Customers"USER Adventureworks2008GOCREATE TABLE [Sales.USA Customers](AccuNumber int IDENTITY(1,1) NOT NULL,[Last Name] varchar(75) NOT NULL,[First Name] varchar(75) NOT NULL)SELECFT AcctNumber,[Last Name],[First Name] FROM [Sales.USA Customers]

　　当“允许带引号的标识符”为True的时候，方括号和双引号都可以使用，为False的时候，值有方括号定界符可以使用，要在连接级别或者数据库级别进行设置的时候如下

SET QUOTED_IDENTIFIER OFFSET QUOTED_IDENTIFIER ONALTER DATABASE Adventureworks2008 SET QUOTED_IDENTIFIER OFFALTER DATABASE Advetnureworks2008 SET QUOTED_IDENTIFIER ON

　　递归触发器已启用：递归触发器是一项高级编程技术，它允许统一触发器在同一事务中按顺序执行多次。当设置为False是，这一操作时不允许的。

ALTER DATABASE Adventureworks2008 SET RECURSIVE_TRIGGERS OFFALTER DATABASE Adventureworks2008 SET RECURSIVE_TRIGGERS ON

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

ALTER DATABASE Adventureworks2008 SET MULTI_USERALTER DATABASE Adventureworks2008 SET RESTRICTED_USERALTER DATABASE Adventureworks2008 SET SINGLE_USER

生成数据库创建脚本

　　几乎每一个创建或修改数据库对象的配置屏幕都包括脚本操作选项。单击“脚本”按钮可用的脚本操作选项会显示，单击任何脚本操作都将生成一个脚本，脚本复制您在图形化界面中指定的所有设置。然后通过这个脚本，可以使用同样的选项创建新的数据库，值需要改变数据库和相关文件的逻辑及物理名称即可。

　　另外一种重用脚本的方法是使用变量替代对象和文件的实际名称，接下来只需要更新变量值并执行脚本，创建数据定义语言Data Definition LanguageDDL脚本时唯一棘手的部分是必须使用动态SQL，因为在DDL脚本中不能直接使用变量，下面的例子演示了如何使用动态SQL来创建一个新的数据库，并将一个用户定义的文件组标记为默认文件组：

DECLARE @DatabaseName AS nvarchar(255)DECLARE @FileGroupName AS nvarchar(255)SET @DatabaseName=N'SlateGravel'SET @FileGroupName=N'UserData'EXECUTE('CREATE DATABASE '+@DatabaseName+' ON PRIMARY( NAME="'+@DatabaseName+'",FILENAME="S:SQLDataFiles'+@DatabaseName+'_data.mdf",SIZE=20MB,MAXSIZE=100MB,FILEGROWTH=30%),FILEGROUP UserData(NAME="'+@FileGroupName+'",FILENAME="S:SQLDataFiles'+DatabaseName+'_data.ndf",SIZE=2048KB,FILEGROWTH=20%)LOG ON(NAME="'+@dATABAEnAME+'_log",FILENAME="T:SQLLogFiles'+@DatabaseName+'_log.ldf",SIZE=100MB,FILEGROWTH=20%);ALTER DATABASE '+@DatabaseName+'MODIFY FILEGROUP '+@FileGroupName+' DEFAULT')

架构

　　架构是一种对象容器,在SQL Server 2008中引用数据库对象是，设定对象引用的上下文是很重要的。每个用于都被指派一个默认的架构，当他们登录到SQL Server应引用数据库对象时，这个默认架构将在该对象的引用方式中发挥独特的作用。例如，假设在AdventureWorks2008数据库中创建了一个名为FredF的用户，并把默认架构Sales指派给他。如果FredF登录并执行SELECT * FROM CreditCard查询，由于其默认架构师Sales，CreditCard将被解析为Adventureworks2008.Sales.CreditCard。由于Sales.CreditCard表存在，因此查询将返回该表的内容，如果FredF执行SELECT * FROM Person查询，Person将被解析为Adventureworks2008.Sales.Person，这是一个并不存在的表。因此，SQL Server会返回“无效的对象名称”这个错误。

　　要创建架构唯一需要的信息是指定架构名称，架构的所有权默认属于运行创建脚本的用户，但可以把任何有效的数据库用户指定为所有者，最简单的方法就是把dbo指定为架构所有者

USER AdventureWorks2008GOCREATE SCHEMA Operations AUTHORIZATION dbo

　　该CREATE SCHEMA语句之后的任何架构作用于的语句都将位于刚才创建的架构的作用于内。记住，架构作用于总是起始于用户的默认架构，如果一个被引用的对象不是作用域限定的，解析将返回到dbo架构

　　如果视图删除一个包含对象的架构，将会产生错误，如果仍然需要架构中的对象，可以使用ALTER SCHEMA语句把它转移到另一个架构中，如把表DeliveryDriver从Operations架构转移到Production架构中。

ALTER SCHEMA Production TRANSFER Operations.DeliveryDriver

　　不能从数据库中删除拥有架构的对象，这就是为什么让dbo用户拥有所有架构的原因之一。要想改变一个架构的所有权，需要改变架构的AUTHORIZATION属性：

ALTER AUTHORIZATION ON SCHEMA::Operations TO FredF

表

　　表的排序规则：创建数据库时，可以配置不同于服务器的排序规则支持。对于包含字符数据的表列来说也是如此，如创建一个各地客户可以使用自己的语言浏览器和搜索产品目录的表：

USER AdventureWorks2008GOCREATE TABLE Production.GlobalProductDescription(ProductDescriptionID int IDENTITY(1,1) NOT NULL,EnglishDescription nvarchar(400) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,FrenchDescription nvarchar(400) COLLATE French_CI_AS NULL,ChineseDescription nvarchar(400) COLLATE Chinese_PRC_CI_AI NULL,ModifiedDate datetime NOT NULL)

　　SQL Server绝对不是使用饭一起，只是提供了一个框架来存放多种语言。您需要准备产品描述的正确翻译并把它们放到合适的列中，另外还要处理任何因为tempdb的排序规则导致的爱需规则不兼容性。

　　表的体系结构：SQL Server使用8KB的数据页春初信息，表中的所有数据都存储在这些数据页中，但数据在页中的组织方式会因表的创建方式和在表创建后所进行的操作有所不同。默认情况下，所有的数据以称为堆的无组织的方式进行存储。SQL Server并不尝试组织或是以任何方式排序数据，也不维护页间的连接。

　　SQL Server将所有数据页存放在分区的逻辑单元中，除非明确分隔，否则表一般存储在定义于单个文件组上的单个分区中。不过，通过跨多个由文件组定义管理的文件横向分区表，SQL Server可以把大型表划分为较小的可管理单元。如下创建实验表并进行差序性能测试：

--创建新表USER AdventureWorks2008GOCREATE TABLE dbo.Transactions(TransactionID int NOT NULL,ProductID int NOT NULL,ReferenceOrderID int NOT NULL,ReferenceOrderLineID int NOT NULL,TransactionDate datetime NOT NULL,TransactionType nchar(1) NOT NULL,Quantity int NOT NULL,ActualCost money NOT NULL,ModifiedDate datetime NOT NULL)--填充新表USE AdventureWorks2008GOINSERT dbo.TransactionsSELECT * FROM Production.TransactionHistory UNION ALL SELECT * FROM Production.TransactionHistoryArchive--查看查询性能USER AdventureWorks2008GODBCC DROPCLEANBUFFERSSET STATISTICS IO ONDECLARE @BeginDate AS datetime,@EndDate AS datetimeSET @BeginDate='2002-01-01'SET @EndDate='2002-12-31'SELECT SUM(Quantity) AS TotalQuantity,SUM(ActualCost) AS TotalCost FROM dbo.Transactions WHERE TransactionDate BETWEEN @BeginDate AND @EndDate

　　可以看到，为了满足查询要求，SQL Server不得不扫描表，现在对表进行分区，把该物理划分为多个文件，使所有的事务按照年份分开，看看会发生什么情况：

--创建四个文件组USER MASTERGOALTER DATABASE AdventureWorks2008 ADD FILEGROUP FGPre2002GOALTER DATABASE AdventureWorks2008 ADD FILE(NAME='AworksPre2002',FILENAME='E:SQLDataAworksPre2002.ndf',SIZE=20MB,FILEGROWTH=20%) TO FILEGROUP FGPre2002GOALTER DATABASE AdventureWorks2008 ADD FILEGROUP FG2002GOALTER DATABASE AdventureWorks2008 ADD FILE(NAME='Aworks2002',FILENAME='E:SQLDataAworks2002.ndf',SIZE=20MB,FILEGROWTH=20%) TO FILEGROUP FG2002GOALTER DATABASE AdventureWorks2008 ADD FILEGROUP FG2003GOALTER DATABASE AdventureWorks2008 ADD FILE(NAME='Aworks2003',FILENAME='E:SQLDataAworks2003.ndf',SIZE=20MB,FILEGROWTH=20%) TO FILEGROUP FG2003GOALTER DATABASE AdventureWorks2008 ADD FILEGROUP FG2004AndAfterGOALTER DATABASE AdventureWorks2008 ADD FILE(NAME='Aworks2004AndAfter',FILENAME='E:SQLDataAworks2004AndAfter.ndf',SIZE=20MB,FILEGROWTH=20%) TO FILEGROUP FG2004AndAfterGO--创建分区函数CREATE PARTITION FUNCTION YearFunction(datetime) AS RANGE RIGHT FOR VALUES('1/1/2002','1/1/2003','1/1/2004')--将YearFunction创建的分区映射到之前创建的文件组CREATE PARTITION SCHEME YearSchemeAS PARTITION YearFunctionTO (FGPre2002,FG2002,FG2003,FG2004AndAfter)--将数据从原Transactions表中移至分区表中USER AdventureWorks2008GOCREATE TABLE dbo.PartitionedTransactions(TransactionID int NOT NULL,ProductID int NOT NULL,ReferenceOrderID int NOT NULL,ReferenceOrderLineID int NOT NULL,TransactionDate datetime NOT NULL,TransactionType nchar(1) NOT NULL,Quantity int NOT NULL,ActualCost money NOT NULL,ModifiedDate datetime NOT NULL) ON YearScheme(TransactionDate)GOINSERT INTO dbo.PartitionedTransactions SELECT * FROM dbo.Transactions--查询性能测试USER AdventureWorks2008GODBCC DROPCLEANBUFFERSSET STATISTICS IO ONDECLARE @BeginDate AS datetime,@EndDate AS datetimeSET @BeginDate='2002-01-01'SET @EndDate='2002-12-31'SELECT SUM(Quantity) AS TotalQuantity,SUM(ActualCost) AS TotalCost FROM dbo.PartitionedTransactions WHERE TransactionDate BETWEEN @BeginDate AND @EndDate

　　创建表分区还可以通过右击表选择存储，选择创建分区，通过GUI进行创建，这里略掉

索引

　　堆用来存储数据很好，也能有效地处理新纪录，但是要在表中寻找特定数据时就没有那么好用了。这就是索引发挥作用的地方那个。SQL Server支持两种类型的索引，聚集索引和非聚集索引。它还支持XML索引和空间索引等其他类型索引，这这些索引和普通索引的关系索引打不相同，后者将来用在数据库表中定位大多数数据。聚集索引和非聚集索引之间的主要区别在于索引的叶级。在非聚集索引中，叶级包含数据的指针。在聚集索引中，叶级包含实际数据。

　　聚集索引：表的所有数据可存储在堆中或聚集索引中，堆和聚集索引是相互排斥的。堆是一个无组织的表行集合，而聚集索引是一个有组织的表行集合。聚集键在索引中必须是唯一的，但创建索引时不必将此列标记为唯一的。当在未标记的唯一的列上创建聚集索引时，SQL Server会生成一个隐藏列，它保存一个名为唯一标识符的4字节内部值来唯一标识重复的聚集索引键。聚集索引的叶级是实际的数据行，而不是数据指针。

　　非聚集索引：非聚集索引更像是一本书后面的索引。当找到索引值时，并不能找到实际数行，只是获得指定实际数据行的位置的指针。

　　堆上的非聚集索引：当在一个以堆形式组织的表上构建非聚集索引时，索引列根据指向数据实际位置的指针分类，该指针由文件ID，页ID和数据所在的页槽号组成，如数据是第一个文件行总第84593页上的第20条记录，那么SQL将使用指针值1：84593：20，这使得SQL Server能够在索引找到数据之后快速访问数据。

　　聚集索引上的非聚集索引：当在一个聚集索引上构建非聚集索引时，索引中的指针值就是数据行的聚集索引键值。一旦定位到索引值，SQL Server就是用聚集键导航聚集索引来检索所有需要的列。

　　包含列：非聚集索引的功能可以通过向索引的节点添加非键值得到提升，这就可使得索引覆盖更多的查询，减少为检索额外值而历遍聚集索引的次数。包含列可以提高查询覆盖率，而不会导致符合索引键的开销。索引中标记为included的列值出现在索引的页节点中，在行排序中不做考虑。

CREATE NONCLUSTERED INDEX IX_Person_LastName ON Person.Person(LastName) INCLUDE (FirstName)

　　筛选索引：筛选索引时优化非聚集索引，它允许在数据子集上创建索引，使得索引结构更小从而减少了构建索引的时间和索引维护的成本，对于包含大量NULL值或包含数据范围的列上的索引，筛选索引特别有用：

CREATE NONCLUSTERED INDEX IX_ListPrice_Product ON Production.Product(ListPrice) WHERE ListPrice>800.00

　　分层索引：HierarchyId是SQL Server 2008中引入的一种新类型数据。为了帮助检索分层数据，可使用两种不同的方法在此类型上的列构建索引，广度优先索引和深度优先索引。广度优先索引将统一级别的所有记录组合到一起，这样SQL Server就可以非常快速地相应具有共同父节点的所有记录的查询。

　　空间索引：SQL Server 2008通过两种新的CLR数据类型支持空间索引，geometry和geography，geometry数据类型用于平面空间而geography数据类型用于地形测量空间。创建空间索引需要两个阶段：分解和镶嵌。在分解阶段，SQL Server将一个有限区域划分为一个网格结构，在镶嵌阶段，表中的每个空间值都映射到每个网格级别。

　　XML索引：凭借XML上建立索引的功能，能够帮助定位和检索XML文本中的特定数据。XML数据早SQL Server数据库中存储为Binary Large Object（BLOB，二进制大型对象）。要在XML中搜索特定元素，属性或者值，SQL Server必须首先打开BLOB，然后将其内容拆解开。SQL Server通过拆解操作创建一个自己可以导航的XML对象的集合。它实质上是提取了XML的数据结构，然后将其存储在临时的关系结构中。

　　XML主索引：主XML索引实际并不是构建在XML列上，而是构建在内部表（在索引创建过程中创建）之上的聚集索引。这个内部表称为节点表。节点表直接连接至在其中创建XML索引的表的聚集索引。要创建一个XML索引，具有XML列的表必须在其逐渐上有一个聚集索引。创建语法如下：

USE AdventureWorks2008GOCREATE PRIMARY XML INDEX XML_IX_IllustrationON Production.Illustration(Diagram)

　　PATH辅助XML索引：PATH辅助XML索引可以提升对XML列指定路径表达式的XML查询的性能。PATH辅助索引建立爱主XML索引提供的节点之上，语法如下：

USER AdventureWorks2008GOCREATE XML INDEX IXML_MyPerson_AdditionalContactInfo_Path ON dbo.MyPerson(AdditionalContactInfo)USING XML INDEX PXML_MyPerson_AdditionalContactInfo FOR PATH

　　VALUE辅助XML索引：VALUE辅助索引用来支持没有完全指定路径的或采用通配符来搜索值的XML查询。语法如下：

CREATE XML INDEX IXML_MyPerson_AdditionalContactInfo_Value ON dbo.MyPerson(AdditionalContactInfo) USING XML INDEX PXML_MyPerson_AdditionalContactInfo FOR VALUE

　　PROPERTY辅助XML索引：PROPERTY辅助索引用于优化通过制定节点的完整路径检索节点值的查询。语法如下：

CREATE XML INDEX_IXML_MyPerson_AdditionalContactInfo_Property ON dbo.MyPerson(AdditionalContactInfo) USING XML INDEX PXML_MyPerson_AdditionalContactInfo FOR PROPERTY

维护表

　　我们已经深入了解了数据在表中的组织方式和优化数据检索的方法，接下来套路如何对这个环境进行维护，表的维护基本可以分为两种：索引的维护和索引统计信息的创建和维护

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

SELECT {*  column list} FROM sys.dm_db_index_physical_stats ({databae_id | NULL},{object_id | NULL},{index_id | NULL},{partition_number | NULL},{mode | NULL | DEFAULT}) 

DECLARE @dbID  smallint,@objectID intSET @DbID=DB_ID('AdventureWorks2008')SET objectID=OBJECT_ID('dbo.MyPersons')SELECT index_id,avg_gragmentation_in_percent,avg_page_space_used_in_percent FROM sys.dm_db_index_physical_stats(@DbID,@ObjectID,NULL,NULL,'DETAILED') WHERE index_level=0

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

　　要去除碎片可以有三种方法：删除并重新生成索引、原地重新生成索引或重新组织索引。

--使用DROP_EXISTING选项创建索引CREATE UNIQUE CLUSTERED INDEX PK_Address_AddressID ON Person.Address(AddressID) WITH (fillfactor=90,DROP_EXISTING=ON)--重新生成索引USE AdventureWorks2008GOALTER INDEX AK_Product_ProductNumber ON Person.Product REBUILD WITH (FILLFACTOR=90,ONLINE=ON)USE AdventureWorks2008GOALTER INDEX ALL ON Person.Product REBUILD WITH (FILLFACTOR=90,ONLINE=ON)

　　重新组织索引消耗的系统资源最少，但不如重新生成索引来的彻底。

强制数据完整性

　　主键约束：一个表有且只能有一个主键约束。它用来唯一标识表中的每一行。主键约束可定义在单个列或者列组合上。SQL Server使用方法是在参与主键的列上创建唯一的索引。在不排序的情况下来强制唯一性会非常低效。SQL Server这方面的问题是，如果聚集索引不存在，他会默认一个唯一的聚集索引。决定什么列参与主键和决定什么列定义表数据的物理结构是完全不同的。不应假定一个主键同时也是表的聚集键。记住，表的所有聚集索引将包含聚集索引键作为指向数据行的指针。如果主键很长，使用聚集索引支持主键对于非聚集索引的存储和检索是非常不利的。

USER AdventureWorks2008GOCREATE TABLE dbo.CreditCards(CreditCardID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_CreditCardID PRIMARY KEY NONCLUSTERED (CreditCardID),CardType nvarchar(50) NOT NULL,CardNumber nvarchar(25) NOT NULL,ExpMonth tinyint NOT NULL,ExpYear smallint NOT NULL,ModifiedDate datetime NOT NULL)USER AdventureWorks2008GOCREATE TABLE dbo.CreditCards(CreditCardID int IDENTITY(1,1) NOT NULL,CardType nvarchar(50) NOT NULL,CardNumber nvarchar(25) NOT NULL,ExpMonth tinyint NOT NULL,ExpYear smallint NOT NULL,ModifiedDate datetime NOT NULL,CONSTRAINT PK_CreditCardID PRIMARY KEY NONCLUSTERED (CreditCardID))ALTER TABLE dbo.CreditCardsADD CONSTRAINT PK_CreditCardID PRIMARY KEY NONCLUSTERED (CreditCardID)

　　唯一约束：虽然一个表只能有一个主键约束，但是它可以有很多唯一约束。要创建唯一约束，有两个选择，在表上创建唯一索引或唯一约束。唯一索引和唯一约束的行为相似，而SQL Server将创建唯一索引来强制唯一约束。

ALTER TABLE dbo.Driver ADD CONSTRAINT UX_LincenseNum UNIQUE NONCLUSTERED(LicenseNum)CREATE UNIQUE NONCLUSTERED INDEX INDEX UX_LincenseNum ON dbo.Driver(LicenseNum)

　　外键约束：外键约束用来保证表之间的完整性，要在表上创建一个外键约束，定义在外键中的列必须映射到主键表中的列，这些列被指定为主键或者有唯一约束（唯一约束和唯一索引都可以）。外键约束有一些高级选项，他们可以在创建中和创建后改变外键约束的行为。

--WITH CHECK添加外键约束时的默认设置，设置指定了应对外键表中任何现有数据进行验证ALTER TABLE dbo.DriverRecord WITH CHECKADD CONSTRAINT FK_DriverRecord_Driver FOREIGN KEY (DriverID) REFERENCES dbo.Driver (DriverID)--WITH NOCHECK，设定了部队现有数据进行验证来使其符合新的约束ALTER TABLE dbo.DriverRecord WITH NOCHECKADD CONSTRAINT FK_DriverRecord_Driver FOREIGN KEY (DriverID) REFERENCES dbo.Driver (DriverID)

　　级联约束：外键默认组织更新或删除父值，然而有些时候这种行为并不合适。SQL Server提供了指定在福记录被删除或更新的情况下对子记录采取何种行为的选项。ON DELETE NO ACTION和ON UPDATE NO ACTION是外键的默认设置。这些设定指定，任何试图删除或更新由其他表中现有行的外键所引用的键值的行为都会失败。此外还有CASCADE,SET NULL和SET DEFAULT选项，他们允许删除或更新键值，以定义的方式级联至定义为拥有外键关系的表

--ON DELETE CASCADE如果父行被删除，子记录也删除ALTER TABLE dbo.DriverRecord WITH NOCHECKADD CONSTRAINT FK_DriverRecord_Driver FOREIGN KEY (DriverID) REFERENCES dbo.Driver (DriverID) ON DELETE CASCADE--ON UPDATE CASCADE如果父键被更新，子记录也更新ALTER TABLE dbo.DriverRecord WITH NOCHECKADD CONSTRAINT FK_DriverRecord_Driver FOREIGN KEY (DriverID) REFERENCES dbo.Driver (DriverID) ON UPDATE CASCADE--ON DELETE SET NULL如果父行删除，子记录设置为NULLALTER TABLE dbo.DriverRecord WITH NOCHECKADD CONSTRAINT FK_DriverRecord_Driver FOREIGN KEY (DriverID) REFERENCES dbo.Driver (DriverID) ON DELETE SET NULL--ON UPDATE SET NULL如果父键更新，子记录设置为NULLALTER TABLE dbo.DriverRecord WITH NOCHECKADD CONSTRAINT FK_DriverRecord_Driver FOREIGN KEY (DriverID) REFERENCES dbo.Driver (DriverID) ON UPDATE SET NULL--ON DELETE SET DEFAULT父记录删除，子记录设置为DEFAULT约束值ALTER TABLE dbo.DriverRecord WITH NOCHECKADD CONSTRAINT FK_DriverRecord_Driver FOREIGN KEY (DriverID) REFERENCES dbo.Driver (DriverID) ON DELETE SET  DEFAULT--ON UPDATE SET DEFAULT父键更新时，子记录设置为DEFAULT约束值ALTER TABLE dbo.DriverRecord WITH NOCHECKADD CONSTRAINT FK_DriverRecord_Driver FOREIGN KEY (DriverID) REFERENCES dbo.Driver (DriverID) ON UPDATE SET DEFAULT

　　CHEKC约束：CHEK约束用来确保字段中的数据符合一个以定义的表达式，如

ALTER TABLE dbo.Driver ADD CONSTRAINT CK_DriverSocialSecurityNumber CHECK (SocSecNum LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')GO

　　默认约束：在插入的时候如果没有指定任何值，默认约束将指定一个值插入到表中

ALTER TABLE dbo.Driver ADD CONSTRAINT DF_Driver_SocSecNum DEFAULT '00000000' FOR SocSecNum

数据库关系图

　　一旦创建了数据库及其对象，就可以很方便地创建和底层结构链接的实体关系图。这样，任何必要改动，尤其是创建外键约束都可以通过一个方便的图形环境应用到数据库中。通过SQL Server Management Studio的对象资源管理器中的用户数据库节点可访问数据库关系图功能。在使用时前，需要安装关系图支持对象。方法是右击“数据库关系图”节点并选择“安装关系图支持程序”命令。安装支持对象会使SQL Server创建一个名为dbo.sysdiagrams的由系统所有的表，它将包括创建的任意关系图的定义。

视图

　　视图时保存的命名查询，可以独立于他们引用的表进行管理。视图和他们所引用的表非常相似，只是他们默认是逻辑对象而非物理对象。创建视图时为了抽象复杂的数据库设计，通过允许访问视图而不是多个表来简化权限，以及安排将数据导出到其他数据存储器中。具体这里不做介绍

　　系统视图时数据库管理员的系统对象视图，因为太多的系统视图，这里不一一叙述，可以查阅SQL Server 2008联机丛书获取相关信息，系统视图主要分为四类：信息架构视图、目录视图、动态管理视图、兼容性目录视图

同义词

　　所谓同义词，是指SQL Server架构范围内的数据对象指定一个名称，数据库应用程序可以使用该名称来太低其由两部分、三部分或四部分组成的名称：

生活不易，码农辛苦
如果您觉得本网站对您的学习有所帮助,可以手机扫描二维码进行捐赠