Since SQL Server 2008, there are two different storage options, each of which allows you to store LOBs and to save storage space:
- FILESTREAM
- Sparse columns
The following subsections describe these options.
FILESTREAM Storage
As you already know, SQL Server supports the storage of LOBs using the VARBINARY(max) data type. The property of this data type is that binary large objects (BLOBs) are stored inside the database. This solution can cause performance problems if the stored files are very large, as in the case of video or audio files. In that case, it is common to store such files outside the database, in external files.
The FILESTREAM storage option supports the management of LOBs, which are stored in the NTFS file system. The main advantage of this type of storage is that the database system manages data, although the data is stored outside the database. Therefore, this storage type has the following properties:
- You use the CREATE TABLE statement to store FILESTREAM data and use the data modification statements (SELECT, INSERT, UPDATE, and DELETE) to query and update such data.
- The database system assures the same level of security for FILESTREAM data as for relational data stored inside the database.
The creation of FILESTREAM data will be described in detail in coming lessons.
Sparse Columns
The aim of sparse columns as a storage option is quite different from the FILESTREAM storage support. Whereas FILESTREAM is Microsoft’s solution for the storage of LOBs outside the database, sparse columns help to minimize data storage space. These columns provide an optimized way to store column values, which are predominantly NULL. (NULL values are described at the end of this chapter.) If you use sparse columns, NULL values require no disk space, but on the other side, non-NULL data needs an additional 2 to 4 bytes, depending on the data type of the non-NULL values. For this reason, Microsoft recommends using sparse columns only when the overall storage space savings will be at least 20 percent.
You specify and access sparse columns in the same way as you specify and access all other columns of a table. This means that the statements SELECT, INSERT, UPDATE, and DELETE can be used to access sparse columns in the same way as you use them for usual columns. (These four SQL statements are described in detail in later chapters) The only difference is in relation to creation of a sparse column: you use the SPARSE option (after the column name) to specify that a particular column is a sparse column: col_name data_type SPARSE.
If a table has several sparse columns, you can group them in a column set. Therefore, a column set is an alternative way to store and access all sparse columns in a table. For more information concerning column sets, see Books Online.
