SQLServerMAX-Length data and storage of data added to the LOB Column

Source: Internet
Author: User
This document briefly introduces the MAX-Length data and storage of data added to the LOB column.

This document briefly introduces the MAX-Length data and storage of data added to the LOB column.

This document briefly introduces the MAX-Length data and storage of data added to the LOB column. It also describes the basics of filestream data.

SQL2005/2008 gave us the MAX operator option to define variable length fields. Using MAX definition, you can let SQL Server determine the storage value as the conventional varchar, nvarchar, varbinary, or LOB data (text, ntext, and image are usually used when the size is greater than 8000 bytes ).

In the storage engine, each LOB column is divided into shards of no more than 8040 bytes. When attaching data to the LOB column, SQL Server searches for additional points and tries to find the location where new data will be added. If new and old data are combined to exceed 8040 bytes, allocate a Large Object Page until the remaining parts are smaller than 8040 bytes, and then find a Page to store the remaining bytes. When SQL Server allocates pages for LOB data, it has two allocation policies: (1) if the data is smaller than 64 KB, a page is randomly allocated. This page comes from a range (extent) of the part of a large object IAM, but the page is not necessarily continuous. (2) If it is larger than 64 KB, it uses an additional page distributor to allocate a range at a time and write data continuously in this range. Therefore, it is recommended that 64 KB data be inserted. When 8*8040 is inserted at a time, the data will be stored in a range (extent.

If you find that the data of a Large Object is becoming increasingly fragmented, you can use the ALter Index reorganize option to sort it out. The premise is that the WithLOB_compaction option ON is enabled by default, and you have not disabled it.

  FileStream Data)

Although SQL Server provides great convenience, You can flexibly store Large object Data in. Benefits:

1. Transaction consistency of big object data can be ensured.

2. backup and recovery of big object data allows you to completely restore big object data at a certain point in time.

3. All data is stored and queried in a single environment.

However, there are also some unfavorable factors:

1. Large Object Data occupies a large number of buffers in the cache.

2. Updating a large object will cause large-scale data sorting.

3. database files become very large.

SQL Server 2008 and SQL Server Native Client 10.0 support enhanced FILESTREAM functions. The FILESTREAM function allows you to store and access large binary values through SQL Server or by directly accessing the Windows file system. A large binary value is greater than 2 GB. For more information about enhanced FILESTREAM support, see FILESTREAM overview. As a big data storage solution, file stream has the following advantages:

1. Large object data is stored in the file system, but there is a 48-byte file pointer value in the database stored in the column containing the file stream.

2. Big object data can be accessed through T-SQL and NTFS stream APIs.

3. The size of big object data is limited by the NTFS value, not the size of the old 2 GB (LOB data)

Disadvantages:

1. database images cannot be used in databases that contain file stream data.

2. Database snapshots cannot contain file stream file groups. Therefore, file stream data is unavailable. An error occurs when you use slect to request a file stream column in the snapshot.

3. file stream data cannot be locally encrypted by SQL Server.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.