SQLServer2008中使用稀疏列和列集的方法

SQLServer2008中使用稀疏列和列集的方法尽管存储的成本已经很低了，但是我们仍然需要考虑使用多种技术(例如压缩和存档)来节省空间。当你思考怎样节省空间时，你第一个想到的是文件系统，但是空间节省也可以用在数据库。当我们创建一个数据库时，我们确保数据文件具有合适的大小和增长速度。我们定期分析我们的数据库规模并执行缩小操作。我们可能执行这些任务用于不同的目的，但是有一个方面是相同的，这些任务帮助我们确保我们的数据库具有最佳的存储。MicrosoftSQLServer为我们提供了用于降低数据库所用空间的各种技术。SQLServer2008推出了一个用于定位可为空字段的技术，它为可为空字段提供了最佳的存储。在SQLServer2008中的这个新特性就是稀疏列。这篇文章不会讲述很多关于稀疏列的特性，它介绍了具有列集的稀疏列的使用，以及在使用它们时你需要了解和考虑的事情。这篇文章描述：◆什么是稀疏列?◆什么是列集?◆在一个列集中插入和更新数据。◆使用触发器跟踪变更。◆对列集实施安全。什么是稀疏列?稀疏列是一个普通字段，就像其它字段一样，但是它降低了对空值的存储要求。一个可为空字段可以在表创建或修改时添加SPARSE关键字来成为稀疏列。如果一个列是稀疏列，那么SQLServer不会为空值分配空间。注意，在使用这个特性时它会增加对非空值数据提取的花费。因此你需要计算可以节省的空间来仔细地对字段应用这个特性。推荐只在空间至少可以节省20%至40%时使字段成为稀疏列。BLO提供了一个包含字段中每个数据类型所需空值百分比的表，以便使这些字段成为稀疏列。什么是列集?列集是一个显示所有稀疏列的字段，它作为一个XML类型的字段添加到表中。它不是物理上存在于这个表中的，它只像是一个计算出来的字段，但是它允许你对它进行修改。推荐你只在有很多稀疏列时使用列集，因为如果使用了列集而不是使用各个稀疏列，那么它会加快修改和提取。下面的代码显示了为一个表创建一个列集的方法。代码1：创建一个具有稀疏列和一个列集的表。CREATETABLE[dbo].[Customers]([Id]intPRIMARYKEY,[FirstName]varchar(50)NOTNULL,[LastName]varchar(50)NOTNULL,[Gender]bitSPARSENULL,--1=male,2=female[Telephone]varchar(15)SPARSENULL,[MonthlyIncome]moneySPARSENULL,[Comments]varchar(1000)SPARSENULL[AllSparseColumns]xmlCOLUMN_SETFORALL_SPARSE_COLUMNS)我为所有可为空字段添加了SPARSE关键字，但是如同我前面提到的，应该在使它们成为稀疏列之前分析空值所占百分比。注意，当你创建这个表时你需要添加这个字段。SQLServer不允许你没有稀疏列的情况下拥有列集字段。之后添加为稀疏列的字段可以使用添加的列集，看下面的代码：代码2：创建具有一个列集的表，不使任何字段成为稀疏列。--addingcolumnsetwithoutsparsecolumnsCREATETABLE[dbo].[Customers_1]([Id]intPRIMARYKEY,[FirstName]varchar(50)NOTNULL,[LastName]varchar(50)NOTNULL,[Gender]bitNULL,--1=male,2=female[Telephone]varchar(15)NULL,[MonthlyIncome]moneyNULL,[Comments]varchar(1000)NULL,[AllSparseColumns]xmlCOLUMN_SETFORALL_SPARSE_COLUMNS)--insertingarecordINSERTINTOdbo.Customers_1([Id],[FirstName],[LastName],[Gender],[Telephone],[MonthlyIncome],[Comments])VALUES(1,'Dinesh','Priyankara',1,'777395871',20000,'nocomments')--thisreturnsnullSELECTAllSparseColumnsFROMdbo.Customers_1--MaketheGendercolumnasasparsecolumnALTERTABLE[dbo].[Customers_1]ALTERCOLUMN[Gender]bitSPARSENULLGO--MaketheTelephonecolumnasasparsecolumnALTERTABLE[dbo].[Customers_1]ALTERCOLUMN[Telephone]varchar(15)SPARSENULL--NowitreturnsvaluesofsparsecolumnsasaxmlSELECTAllSparseColumnsFROMdbo.Customers_1在一个列集中插入和更新数据可以对稀疏列插入记录而不使用列集，但是一旦插入了，那么记录就可以使用列集来获得。代码1：对列集插入一个记录而不插入任何值。--Insertarecordtothetable.INSERTINTOdbo.Customers([Id],[FirstName],[LastName],[Gender],[Telephone],[MonthlyIncome],[Comments])VALUES(1,'Dinesh','Priyankara',1,'777395871',20000,'nocomments')--RetrievetherecordandseeSELECT[Id],[FirstName],[LastName],[Gender],[Telephone],[MonthlyIncome],[Comments],[AllSparseColumns]FROMdbo.Customers/*Result:177739587120000.0000nocomments*/可以使用这个列集来插入和更新记录。代码2显示了通过列集来插入一个记录和更新一个记录的方法。代码2：插入和更新这个列集。--Insertinganewrecord.Notethatthestatementusesthecolumnsetto--insertvaluesforCommentsandTelephonecolumnsINSERTINTOdbo.Customers([Id],[FirstName],[LastName],[AllSparseColumns])VALUES(3,'Yeshan','Santhush','Nocomments777225656')--Updatetherecord.--ThismakesCommentscolumnNULLbecausexmlstringdoesnotcontainanodeforCommentscolumn.--ThisupdatestheTelephonecolumnwiththenewvalue.UPDATEdbo.CustomersSET[AllSparseColumns]='7774546321'WHEREId=3使用触发器跟踪变更这是一个小技巧。一般我们使用UPDATE()函数来查看一个特定列是否更新了。如果你在一个与具有稀疏列和列集的表所关联的触发器中执行了它，那么UPDATE()函数返回的值不会是你所预期的。下面的代码测试了这一点。代码1：对Customers表创建一个UPDATE触发器。--CreatingaupdatetriggeronCustomerstable.CREATETRIGGERtr_Customers_UpdateONdbo.CustomersFORUPDATEASBEGINIFUPDATE(Gender)print'Gendercolumnupdated.'IFUPDATE(Telephone)print'Telephonecolumnupdated.'IFUPDATE(Comments)print'Commentscolumnupdated.'IFUPDATE(AllSparseColumns)print'AllSparseColumnscolumnupdated.'END当你显式更新列集时，UPDATE()函数对这个列集返回true。不只这样，写给所有稀疏列的UPDATE()函数都返回true。当一个稀疏列被显式更新时，UPDATE()函数对稀疏列和列集返回true。代码2：在触发器中更新这个表，并测试UPDATE()函数。--Updatethecolumnset.--ThisupdatemakesallUPDATE()functions--toreturntrue.UPDATEdbo.CustomersSET[AllSparseColumns]='4455'WHEREId=3/*Result:Gendercolumnupdated.Telephonecolumnupdated.Commentscolumnupdated.AllSparseColumnscolumnupdated.(1row(s)affected)*/--UpdatetheGendercolumn.--ThisupdatemakesUPDATE()functionof--Gendercolumnandcolumnsettoreturntrue.UPDATEdbo.CustomersSETGender=1WHEREId=3/*Result:Gendercolumnupdated.AllSparseColumnscolumnupdated.(1row(s)affected)*/如果你为INSERT语句写了相同的触发器，那么你将看到INSERT操作出现相同的行为。当对一个稀疏列插入一个值并且你使得其它为NULL时，UPDATE()函数对稀疏列和列集返回true。当对列集插入值时，UPDATE()函数对列集和所有稀疏列返回true。对列集实施安全对列集实施安全就像对其它字段实施安全一样，但是稀疏列的权限可能会影响从列集获取数据。让我们做些测试。首先，让我们授予对所有稀疏列的SELECT权限，并试图从列集获取数据。你需要有一个用于这个测试的单独账户。如果你没有额外的账户，那么创建一个登录和一个用户为User1。让我们使用User1权限来试着获取数据。代码1：使用User1的帐户获取和更新数据。--SettheexecutioncontexttotheuserUser1EXECUTEASUSER='User1'--selectstatement1SELECTGender,Telephone,MonthlyIncome,CommentsFROMCustomers--selectstatement2SELECTAllSparseColumnsFROMCustomers--selectstatement3UPDATEdbo.CustomersSETGender=1WHEREId=3--selectstatement4UPDATEdbo.CustomersSET[AllSparseColumns]='777225656Testmsg1'WHEREId=3REVERT代码2：将稀疏列的SELECT权限授予User1并执行代码1。--GrantselectpermissiontoallsparsecolumnsGRANTSELECT(Gender,Telephone,MonthlyIncome,Comments)ONOBJECT::dbo.CustomersTOUser1--Executethecode1:--selectstatement1-willsuccess--sel