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。