SQL Server 2008中使用稀疏列和列集的方法.doc

SQL Server 2008中使用稀疏列和列集的方法尽管存储的成本已经很低了，但是我们仍然需要考虑使用多种技术(例如压缩和存档)来节省空间。当你思考怎样节省空间时，你第一个想到的是文件系统，但是空间节省也可以用在数据库。当我们创建一个数据库时，我们确保数据文件具有合适的大小和增长速度。我们定期分析我们的数据库规模并执行缩小操作。我们可能执行这些任务用于不同的目的，但是有一个方面是相同的，这些任务帮助我们确保我们的数据库具有最佳的存储。Microsoft SQL Server为我们提供了用于降低数据库所用空间的各种技术。SQL Server 2008推出了一个用于定位可为空字段的技术，它为可为空字段提供了最佳的存储。在SQL Server 2008中的这个新特性就是稀疏列。这篇文章不会讲述很多关于稀疏列的特性，它介绍了具有列集的稀疏列的使用，以及在使用它们时你需要了解和考虑的事情。这篇文章描述：什么是稀疏列?什么是列集?在一个列集中插入和更新数据。使用触发器跟踪变更。对列集实施安全。什么是稀疏列?稀疏列是一个普通字段，就像其它字段一样，但是它降低了对空值的存储要求。一个可为空字段可以在表创建或修改时添加SPARSE关键字来成为稀疏列。如果一个列是稀疏列，那么SQL Server不会为空值分配空间。注意，在使用这个特性时它会增加对非空值数据提取的花费。因此你需要计算可以节省的空间来仔细地对字段应用这个特性。推荐只在空间至少可以节省20%至40%时使字段成为稀疏列。BLO提供了一个包含字段中每个数据类型所需空值百分比的表，以便使这些字段成为稀疏列。什么是列集?列集是一个显示所有稀疏列的字段，它作为一个XML类型的字段添加到表中。它不是物理上存在于这个表中的，它只像是一个计算出来的字段，但是它允许你对它进行修改。推荐你只在有很多稀疏列时使用列集，因为如果使用了列集而不是使用各个稀疏列，那么它会加快修改和提取。下面的代码显示了为一个表创建一个列集的方法。代码1：创建一个具有稀疏列和一个列集的表。CREATE TABLE dbo.Customers ( Id int PRIMARY KEY, FirstName varchar(50) NOT NULL, LastName varchar(50) NOT NULL, Gender bit SPARSE NULL, - 1 = male, 2 = female Telephone varchar(15) SPARSE NULL, MonthlyIncome money SPARSE NULL, Comments varchar(1000) SPARSE NULL AllSparseColumns xml COLUMN_SET FOR ALL_SPARSE_COLUMNS )我为所有可为空字段添加了SPARSE关键字，但是如同我前面提到的，应该在使它们成为稀疏列之前分析空值所占百分比。注意，当你创建这个表时你需要添加这个字段。SQL Server 不允许你没有稀疏列的情况下拥有列集字段。之后添加为稀疏列的字段可以使用添加的列集，看下面的代码：代码2：创建具有一个列集的表，不使任何字段成为稀疏列。- adding column set without sparse columns CREATE TABLE dbo.Customers_1 ( Id int PRIMARY KEY, FirstName varchar(50) NOT NULL, LastName varchar(50) NOT NULL, Gender bit NULL, - 1 = male, 2 = female Telephone varchar(15) NULL, MonthlyIncome money NULL, Comments varchar(1000) NULL, AllSparseColumns xml COLUMN_SET FOR ALL_SPARSE_COLUMNS ) - inserting a record INSERT INTO dbo.Customers_1 (Id, FirstName, LastName, Gender, Telephone, MonthlyIncome, Comments) VALUES (1, Dinesh, Priyankara, 1, 777395871, 20000, no comments) - this returns null SELECT AllSparseColumns FROM dbo.Customers_1 - Make the Gender column as a sparse column ALTER TABLE dbo.Customers_1 ALTER COLUMN Gender bit SPARSE NULL GO - Make the Telephone column as a sparse column ALTER TABLE dbo.Customers_1 ALTER COLUMN Telephone varchar(15) SPARSE NULL - Now it returns values of sparse columns as a xml SELECT AllSparseColumns FROM dbo.Customers_1在一个列集中插入和更新数据可以对稀疏列插入记录而不使用列集，但是一旦插入了，那么记录就可以使用列集来获得。代码1：对列集插入一个记录而不插入任何值。- Insert a record to the table. INSERT INTO dbo.Customers (Id, FirstName, LastName, Gender, Telephone, MonthlyIncome, Comments) VALUES (1, Dinesh, Priyankara, 1, 777395871, 20000, no comments) - Retrieve the record and see SELECT Id, FirstName, LastName, Gender, Telephone, MonthlyIncome, Comments, AllSparseColumns FROM dbo.Customers /* Result: 177739587120000.0000no comments */可以使用这个列集来插入和更新记录。代码2显示了通过列集来插入一个记录和更新一个记录的方法。代码2：插入和更新这个列集。- Inserting a new record. Note that the statement uses the column set to - insert values for Comments and Telephone columns INSERT INTO dbo.Customers (Id, FirstName, LastName, AllSparseColumns) VALUES (3, Yeshan, Santhush, No comments777225656) - Update the record. - This makes Comments column NULL because xml string does not contain a node for Comments column. - This updates the Telephone column with the new value. UPDATE dbo.Customers SET AllSparseColumns = 7774546321 WHERE Id = 3使用触发器跟踪变更这是一个小技巧。一般我们使用UPDATE()函数来查看一个特定列是否更新了。如果你在一个与具有稀疏列和列集的表所关联的触发器中执行了它，那么UPDATE()函数返回的值不会是你所预期的。下面的代码测试了这一点。代码1：对Customers表创建一个UPDATE触发器。- Creating a update trigger on Customers table. CREATE TRIGGER tr_Customers_Update ON dbo.Customers FOR UPDATE AS BEGIN IF UPDATE(Gender) print Gender column updated. IF UPDATE(Telephone) print Telephone column updated. IF UPDATE(Comments) print Comments column updated. IF UPDATE(AllSparseColumns) print AllSparseColumns column updated. END当你显式更新列集时，UPDATE()函数对这个列集返回true。不只这样，写给所有稀疏列的UPDATE()函数都返回true。当一个稀疏列被显式更新时，UPDATE()函数对稀疏列和列集返回true。代码2：在触发器中更新这个表，并测试UPDATE()函数。- Update the column set. - This update makes all UPDATE() functions - to return true. UPDATE dbo.Customers SET AllSparseColumns = 4455 WHERE Id = 3 /* Result: Gender column updated. Telephone column updated. Comments column updated. AllSparseColumns column updated. (1 row(s) affected) */ - Update the Gender column. - This update makes UPDATE() function of - Gender column and column set to return true. UPDATE dbo.Customers SET Gender = 1 WHERE Id = 3 /* Result: Gender column updated. AllSparseColumns column updated. (1 row(s) affected) */如果你为INSERT 语句写了相同的触发器，那么你将看到INSERT操作出现相同的行为。当对一个稀疏列插入一个值并且你使得其它为NULL时，UPDATE()函数对稀疏列和列集返回true。当对列集插入值时，UPDATE()函数对列集和所有稀疏列返回true。对列集实施安全对列集实施安全就像对其它字段实施安全一样，但是稀疏列的权限可能会影响从列集获取数据。让我们做些测试。首先，让我们授予对所有稀疏列的SELECT权限，并试图从列集获取数据。你需要有一个用于这个测试的单独账户。如果你没有额外的账户，那么创建一个登录和一个用户为User1。让我们使用User1权限来试着获取数据。代码1：使用User1的帐户获取和更新数据。-Set the execution context to the user User1 EXECUTE AS USER = User1 - select statement 1 SELECT Gender, Telephone, MonthlyIncome, Comments FROM Customers - select statement 2 SELECT AllSparseColumns FROM Customers - select statement 3 UPDATE dbo.Customers SET Gender = 1 WHERE Id = 3 - select statement 4 UPDATE dbo.Customers SET AllSparseColumns = 777225656Test msg1 WHERE Id = 3 REVERT代码2：将稀疏列的SELECT权限授予User1并执行代码1。 - Grant select permission to all sparse columns GRANT SELECT (Gender, Telephone, MonthlyIncome, Comments) ON OBJECT:dbo.Customers TO User1 - Execute the code 1: - select statement 1 - will success - select statement 2 - will fail - select statement 3 - will fail - select statement 4 - will fail - Remove SELECT permission from User1 REVOKE SELECT (Id, Gender, Telephone, MonthlyIncome, Comments) ON OBJECT:dbo.Customers TO User1尽管我们授予了对所有稀疏列的SELECT权限，但是用户却不能从列集获取数据。它要求显式的SELECT权限。但是如果我们授予稀疏列上的SELECT和UPDATE权限，User1就将可以访问这个列集。但是User1不能更新这个列集。代码3：授予稀疏列上的SELECT和UPDATE权限给User1并执行代码1。 - Grant select permission to all sparse columns GRANT SELECT, UPDATE (Gender, Telephone, MonthlyIncome, Comments) ON OBJECT:dbo.Customers TO User1 - Execute the code 1 - select statement 1 - will success - select statement 2 - will success - update statement 3 - will success - update statement 4 - will fail - Remove SELECT, and UPDATE permissions from User1 REVOKE SELECT, UPDATE (Id, Gender, Telephone, MonthlyIncome, Comments) ON OBJECT:dbo.Customers TO User1现在让我们授予对列集的SELECT权限，并尝试访问稀疏列。代码4授予列集上的SELECT权限给User1并执行代码1。- Grant select permission to the column set GRANT SELECT (AllSparseColumns) ON OBJECT:dbo.Customers TO User1 - Execute the code 1 - select statement 1 - will fail - select statement 2 - will success - update statement 3 - will fail - update statement 4 - will fail - Remove SELECT permission from User1 REVOKE SELECT (AllSparseColumnss) ON OBJECT:dbo.Customers TO User1就像代码3中的代码一样，如果我们授予对列集的SELECT和UPDATE权限给User1，那么SELECT语句2将会成功。此外，User1将可以对列集执行UPDATE语句，但不能对稀疏列执行UPDATE语句。看下面的代码5。代码5：授予对列集的SELECT和UPDATE权限给User1并执行代码1。 - Grant select and update permissions to the column set GRANT SELECT, UPDATE (AllSparseColumns) ON OBJECT:dbo.Customers TO User1 - Execute the code 1 - select statement 1 - will success - select statement 2 - will success - update statement 3 - will fail - update statement 4 - will success - Remove SELECT and UPDATE permission from User1 REVOKE SELECT, UPDATE (AllSparseColumnss) ON OBJECT:dbo.Customers TO User1现在让我们测试DENY权限是怎样传播的。让我们授予对稀疏列的SELECT权限并拒绝对列集SELECT的权限。正如你所预料的，User1将可以访问所有的稀疏列，但不能访问列集。拒绝对列集SELECT的权限不会影响稀疏列。代码6：授予对稀疏列SELECT的权限并拒绝列集的SELECT权限给User1并执行代码1。 - Grant SELECT permission on sparse columns GRANT SELECT (Id, Gender, Telephone, MonthlyIncome, Comments) ON OBJECT:dbo.Customers TO User1 - Deny SELECT permission on the co