SQL Server資料型別BLOB

用於儲存BLOB資料的資料型別

　　BLOB資料是一個每條記錄都擁有大量資料的欄位。這個資料可以是文字格式的或二進位制格式的。當在SQL Server中使用BLOB資料時，每條記錄的資料量一般都遠遠超過了一個單獨記錄的8K的限制。

　　有多種資料型別用於儲存BLOB資料，並且它們隨著時間已有所改變。可用於BLOB儲存的資料型別很多年都沒有變，但是在SQL Server 2005中，微軟推出了許多新的資料型別。在SQL Server 2008中，微軟甚至計劃推出更多的資料型別，不過到目前為止，他們還沒有在CTP版本中推出這些資料型別。

　　在SQL Server 2000和更早的版本中，我們被限制於TEXT、NTEXT和IMAGE資料型別。TEXT資料型別是用於儲存非Unicode的BLOB資料，而NTEXT資料型別是用於儲存Unicode 的BLOB資料。IMAGE資料型別是用於儲存二進位制BLOB資料的。你可以使用IMAGE資料型別來儲存任何二進位制資料，例如圖片、office文件、壓縮資料等等。因為資料是二進位制格式的，所以它可以是Unicode的也可以是非Unicode的。

　　SQL Server 2005資料型別是基於VARCHAR、NVARCHAR、VARBINARY和XML的。在使用這些資料型別時，一般你會指定要儲存的資料的最大規模。為了在這些欄位裡儲存BLOB資料，你要指定欄位的最大規模。這些資料型別允許你在每條記錄裡最大儲存2GB的資料(對於字元和二進位制資料是2^31位，對於Unicode資料是2^30)。微軟計劃在未來的微軟SQL Server版本中刪除TEXT、NTEXT和IMAGE資料型別，它們的使用將會被轉換到新的VARCHAR(MAX)、NVARCHAR(MAX)、VARBINARY(MAX)和XML資料型別上。

CREATE TABLE BlobData 　　(ID int PRIMARY KEY, 　　FirstName nvarchar(30), 　　LastName nvarchar(30), 　　EmailAddress varchar(255), 　　[Resume] nvarchar(MAX))

　　在SQL Server 2008中，傳說我們會得到一個叫做FILESTREAM的新的資料型別，它可以將二進位制資料例如office文件儲存到和普通的資料庫檔案不同的地方。通過將這個資料儲存到和普通的MDF和NDF資料檔案不同的地方，我們可以將它儲存到單獨的硬碟上來將訪問BLOB資料對資料庫中其餘內容造成的效能影響降到最低。

　　SQL Server BLOB資料怎麼儲存和訪問?

　　在使用TEXT、NTEXT和IMAGE資料型別時，資料是不和普通資料儲存在一起的。一個被稱作指標的很小的二進位制值，和普通資料儲存在一起。這個二進位制值指向資料檔案裡的資料實際儲存的位置。

　　當儲存VARCHAR(MAX)、NVARCHAR(MAX)、VARBINARY(MAX)和XML資料型別的資料時，如果資料小於一行資料8K的最大限制，那麼這個資料被儲存到這個頁面內。但是如果這個資料比一行資料8K的最大限制大的話，它會以和儲存遺留資料型別一樣的儲存方式來儲存，並且它會通過一個二進位制指標的值被訪問。

　　注意：這個二進位制值和位置都是在後臺處理的，對於使用者是不可見的。

為了以TEXT、NTEXT和IMAGE資料型別讀取資料，需要使用READTEXT命令。這個命令接受一個列名還有一個16位的二進位制指標，要讀取的第一個字元和要讀取的字元數目。它使用TEXTPTR函式來獲得正確的二進位制指標，這個二進位制指標是用於定位物理檔案中的資料的：

DECLARE @ptr varbinary(16); 　　SELECT @ptr = TEXTPTR(LongText) 　　FROM TextDataExample 　　where TextID = 1 　　READTEXT TextDataExample.LongText @ptr 1 50;

　　與READTEXT命令類似的，還有一個WRITETEXT命令用於將資料插入到記錄中去。和READTEXT命令一樣，WRITETEXT命令使用TEXTPTR函式來定位物理檔案中的資料：

DECLARE @ptr varbinary(16); 　　SELECT @ptr = TEXTPTR(LongText) 　　FROM TextDataExample 　　where TextID = 1 　　READTEXT TextDataExample.LongText @ptr 'Sample data';

　　這些新的資料型別，不像他們之前的那些資料型別，它們可以不使用READTEXT 和WRITETEXT命令就可以很容易的被訪問到。這些新的資料型別能夠這麼快就這麼流行的一部分原因是因為要使用他們不需要額外的命令。你可以用我們平常操縱資料所使用的普通的SELECT、INSERT和UPDATE DML來使用這些資料型別。不幸的是，因為這個FILESTREAM資料型別還沒有釋出，沒有關於怎樣使用它的示例程式碼可以公佈。

　　隨著分層儲存而出現的成本效率

　　使用一個叫做分層儲存的儲存設計是一個降低你的SQL Server BLOB儲存的每個位元組的總成本的簡單方法。有了分層儲存技術，高訪問的資料被放置在更快，更貴的儲存器裡。不是經常訪問的資料放在慢一些、較便宜的儲存器上。通常情況下，BLOB資料被頻繁的訪問，而由於儲存在BLOB資料列中的資訊數量，使用分層儲存通常是成本最有效的儲存方法。當建立一個較便宜的儲存層時，更大的、較慢的硬碟被用來建立第二個RAID陣列，而這個RAID陣列是用來存放第二個檔案組的，該檔案組儲存BLOB資料。

　　分層儲存是通過告訴SQL Server在一個獨立於行資料的檔案組裡儲存BLOB資料來使用的。這個指派發生在CREATE TABLE命令使用TEXTIMAGE_ON引數建立了這個表的時候。一個SQL Server表裡的所有BLOB資料儲存在一個單獨的檔案組裡。如果你想在兩個檔案間分離BLOB資料，你必須同時將你的表分割為兩個表，並通過TEXTIMAGE_ON引數為每一個表指定一個不同的組：

CREATE TABLE TextDataExample 　　(TextID int IDENTITY(1,1), 　　TextDescription varchar(25), 　　LongText TEXT) 　　ON [PRIMARY] TEXTIMAGE_ON [TextDataFileGroup]

不幸的是，一旦表被建立了就沒有簡單的方法來改變包含BLOB資料的檔案組。移動BLOB資料到另一個檔案組的唯一方法是建立一個新的表，將資料複製到新的表裡，刪除舊的表，然後將新的表重新命名為正確的名稱。

　　假設你有一個用這個定義建立的表：

CREATE TABLE TextDataExample 　　(TextID int IDENTITY(1,1) PRIMARY KEY, 　　TextDescription varchar(25), 　　LongText TEXT) 　　ON [PRIMARY]

　　並且你想轉移資料到用這個定義建立的表上：

CREATE TABLE TextDataExample 　　(TextID int IDENTITY(1,1) PRIMARY KEY, 　　TextDescription varchar(25), 　　LongText TEXT) 　　ON [PRIMARY] TEXTIMAGE_ON [TextDataFileGroup]

　　那你要用一個與這個類似的指令碼：

CREATE TABLE TextDataExample_Temp 　　(TextID int IDENTITY(1,1) PRIMARY KEY, 　　TextDescription varchar(25), 　　LongText TEXT) 　　ON [PRIMARY] TEXTIMAGE_ON [TextDataFileGroup] 　　SET IDENTITY_INSERT TextDataExample_Temp ON 　　INSERT INTO TextDataExample_Temp 　　(TextID, TextDescription, LongText) 　　SELECT TextID, TextDescription, LongText 　　FROM TextDataExample WITH (TABLOCK) 　　SET IDENTITY_INSERT TextDataExample_Temp OFF 　　DROP TABLE TextDataExample 　　exec sp_rename 'TextDataExample_Temp', 　　'TextDataExample', 'object'

　　在這個過程中要確保在刪除舊錶以後在新的表上重新建立所有需要的索引。這些索引應該在舊錶被刪除之後建立，以便萬一任何指令碼或儲存過程要求唯一的索引名稱才是有效的，那這樣相同的索引名稱就可以使用了。

　　既然SQL Server支援BLOB資料，你在儲存BLOB資料到SQL Server中時，一定要謹慎。儲存大量BLOB資料到你的資料庫中會影響你的資料庫效能。雖然有一些方法可以在效能方面給你些幫助，但是它通常最終導致將BLOB資料移出SQL Server資料庫存入檔案系統。雖然這時把BLOB資料存放在資料庫裡看起來是一個簡單的解決方案，但它會導致後來隨著系統發展而來的效能問題。這個檔案系統對於BLOB資料來說通常是最好的存放地址，但是如果你選擇存放到資料庫中，一定要確保小心使用。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-557593/，如需轉載，請註明出處，否則將追究法律責任。