Parse SQL Server Data Type BLOB

This article explains the BLOB data types that SQLServer uses to access and store data, parses SQLServerBLOB data storage, access methods, and how to reduce the total cost of each byte stored in your SQLServerBLOB. BLOB data is a field with a large amount of data in each record. This data can be in text format or

This article explains the BLOB data types that SQL Server uses to access and store data, and resolves the SQL Server BLOB data storage, access Method and reduce the total cost of each byte stored in your SQL Server BLOB. BLOB data is a field with a large amount of data in each record. This data can be in text format or

This article explainsSQL ServerFor access and storageDataOfBLOBDataType,AnalysisSQL Server BLOBDataStorage, access method, and cost of each byte stored in your SQL Server BLOB.

BLOBDataIs that each record has a large numberData. ThisDataIt can be in text or binary format. When BLOB is used in SQL ServerDataTime,DataGenerally, the quantity exceeds the limit of 8 K for a single record.

There are manyDataTypeUsed to store BLOBDataAnd they have changed over time. Available for BLOB StorageDataTypeIt has not changed for many years, but in SQL Server 2005, Microsoft launched many newDataType. In SQL Server 2008, Microsoft plans to launch moreDataTypeBut so far, they have not released theseDataType.

In SQL Server 2000 and earlier versions, we are restricted to TEXT, NTEXT, and IMAGEDataType. TEXTDataTypeIs used to store non-Unicode BLOBDataWhile NTEXTDataTypeIs the BLOB used to store UnicodeData. IMAGEDataTypeIs used to store binary BLOBData. You can use IMAGEDataTypeTo store any binary data.Data, Examples, office documents, compressionDataAnd so on. BecauseDataIt is in binary format, so it can be Unicode or non-Unicode.

SQL Server 2005DataTypeIt is based on VARCHAR, NVARCHAR, VARBINARY, and XML. When using theseDataTypeGenerally, you specifyData. To store BLOB in these fieldsDataYou must specify the maximum size of a field. TheseDataTypeAllows you to store up to 2 GBData(For characters and binaryDataIs 2 ^ 31 characters, for UnicodeDataIs 2 ^ 30 ). Microsoft plans to delete TEXT, NTEXT, and IMAGE in future Microsoft SQL Server versionsDataType, They will be converted to the new VARCHAR (MAX), NVARCHAR (MAX), VARBINARY (MAX), and XMLDataType.

The following is a reference clip: Create table BlobData (ID int primary key, FirstName nvarchar (30 ), LastName nvarchar (30 ), EmailAddress varchar (255 ), [Resume] nvarchar (MAX ))

In SQL Server 2008, it is said that we will get a new one called FILESTREAM.DataTypeWhich can be binaryDataFor example, office documents are stored inDataLibrary files are different. ByDataStorage to common MDF and NDFDataDifferent files, we can store it to a separate hard disk to access BLOBDataPairDataThe performance of other content in the library is minimized.

　　SQL Server BLOBDataHow to store and access data?

When using TEXT, NTEXT, and IMAGEDataType,DataIs not commonDataStored together. A small binary value called a pointerDataStored together. This binary value pointsDataFileDataThe actual storage location.

When VARCHAR (MAX), NVARCHAR (MAX), VARBINARY (MAX), and XMLDataTypeOfDataIfDataLess than one rowDataThe maximum limit of 8 K, then thisDataStored in this page. However, ifDataThan a rowDataIf the maximum size of 8 K is large, it willDataTypeThe same storage method, and it will be accessed through the value of a binary pointer.

Note: The binary value and position are processed in the background and invisible to users.

To use TEXT, NTEXT, and IMAGEDataTypeReadDataTo use the READTEXT command. This command accepts a column name and a 16-bit binary pointer, the first character to be read and the number of characters to be read. It uses the TEXTPTR function to obtain the correct binary pointer, which is used to locateDataOf:

The following is a reference clip: DECLARE @ ptr varbinary (16 ); SELECT @ ptr = TEXTPTR (LongText) FROM TextDataExample Where TextID = 1 READTEXT TextDataExample. LongText @ ptr 1 50;

Similar to the READTEXT command, there is also a WRITETEXT command usedDataInsert to record. Like the READTEXT command, the WRITETEXT command uses the TEXTPTR function to locateData:

The following is a reference clip: DECLARE @ ptr varbinary (16 ); SELECT @ ptr = TEXTPTR (LongText) FROM TextDataExample Where TextID = 1 READTEXT TextDataExample. LongText @ ptr 'sample data ';

These newDataTypeUnlike those earlierDataTypeThey can be easily accessed without using the READTEXT and WRITETEXT commands. These newDataTypeThis is partly because they don't need additional commands. You can use our usual operationsDataThe common SELECT, INSERT, and update dml used to use theseDataType. Unfortunately, this FILESTREAMDataTypeThere is no release yet, and no sample code on how to use it can be published.

　　Cost efficiency with Hierarchical Storage

Using a storage design called Hierarchical Storage is a simple way to reduce the total cost of each byte stored in your SQL Server BLOB. With Hierarchical Storage technology, high-accessDataStored in faster and more expensive storage. Not frequently accessedDataPut it on a slower and cheaper storage. Normally, BLOBDataFrequently accessed because it is stored in BLOBDataNumber of information in the column. Using Hierarchical Storage is usually the most cost effective storage method. When creating a cheaper storage layer, a larger and slower hard disk is used to create a second RAID array, which is used to store the second file group, this file group stores BLOBData.

Hierarchical Storage is used to tell SQL Server to operate independentlyDataBLOB storage in the file groupData. This assignment occurs when the create table command uses the TEXTIMAGE_ON parameter to CREATE the TABLE. All blobs in an SQL Server tableDataStored in a separate file group. If you want to separate BLOB between two filesDataYou must divide your table into two tables at the same time and specify a different group for each table using the TEXTIMAGE_ON parameter:

The following is a reference clip: Create table TextDataExample (TextID int IDENTITY (1, 1 ), TextDescription varchar (25 ), LongText TEXT) ON [PRIMARY] TEXTIMAGE_ON [TextDataFileGroup]