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/，如需轉載，請註明出處，否則將追究法律責任。