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 column set
　　DENY SELECT (AllSparseColumns) ON OBJECT::dbo.Customers TO User1
　　-- Execute the code 1
　　-- select statement 1 - will success
　　-- select statement 2 - will fail
　　-- update statement 3 - will fail
　　-- update statement 4 - will fail
　　REVOKE ALL ON OBJECT::dbo.Customers TO User1
　　GO

　　但是当对稀疏列SELECT的权限被拒绝时，它会传播到列集。看代码7。User1将不能访问到列集，即使我们授予了列集上的SELECT权限。

　　代码7拒绝对稀疏列SELECT的权限并授予对列集SELECT的权限给User1并执行代码1。

     -- Deny SELECT permission on sparse columns
　　DENY SELECT (Id, Gender, Telephone, MonthlyIncome, Comments) ON OBJECT::dbo.Customers TO User1
　　-- Grant SELECT permission on 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 fail
　　-- update statement 3 - will fail
　　-- update statement 4 - will fail
　　REVOKE ALL ON OBJECT::dbo.Customers TO User1
　　GO

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16436858/viewspace-512219/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16436858/viewspace-512219/