当前位置：首页 > 系统运维 > 正文内容

SQL Server 2008中使用稀疏列和列集的 *** (1)

a8116255310年前 (2015-11-17)系统运维14

尽管存储的成本已经很低了，但是我们仍然需要考虑使用多种技术(例如压缩和存档)来节省空间。当你思考怎样节省空间时，你之一个想到的是文件系统，但是空间节省也可以用在数据库。当我们创建一个数据库时，我们确保数据文件具有合适的大小和增长速度。我们定期分析我们的数据库规模并执行缩小操作。我们可能执行这些任务用于不同的目的，但是有一个方面是相同的，这些任务帮助我们确保我们的数据库具有更佳的存储。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 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权限。

尽管存储的成本已经很低了，但是我们仍然需要考虑使用多种技术(例如压缩和存档)来节省空间。当你思考怎样节省空间时，你之一个想到的是文件系统，但是空间节省也可以用在数据库。当我们创建一个数据库时，我们确保数据文件具有合适的大小和增长速度。我们定期分析我们的数据库规模并执行缩小操作。我们可能执行这些任务用于不同的目的，但是有一个方面是相同的，这些任务帮助我们确保我们的数据库具有更佳的存储。Microsoft SQL Server为我们提供了用于降低数据库所用空间的各种技术。SQL Server 2008推出了一个用于定位可为空字段的技术，它为可为空字段提供了更佳的存储。在SQL Server 2008中的这个新特性就是稀疏列。该文不会讲述很多关于稀疏列的特性，它介绍了具有列集的稀疏列的使用，以及在使用它们时你需要了解和考虑的事情。