Dealing with large object (LOB) data is one of the challenges of managing SQL Server installations. LOBs are usually composed of pictures but they can contain other data as well. Typically these images are used to display product images or other graphical media on a website, and they can be quite large.
Related: Where to store LOB data?
SQL Server has many data types that can be used for different types of LOB storage, but picking the right one for LOB storage can be difficult—if you even want to store the LOBs in the database at all. Many DBAs prefer to keep LOBs out of the database. The basic rule of thumb is that LOBs smaller than 256KB perform best stored inside a database while LOBs larger than 1MB perform best outside the database.
Related: What about moving LOB data?
Storing LOBs outside the database offers performance advantages, but it also jeopardizes data because there’s no built-in mechanism to ensure data integrity. To help you find the best way to store your LOB data, it’s necessary to understand the differences between SQL Server’s LOB data types.
1. TEXT
Used for non-Unicode variable-length text data, this data type can’t be used for binary data. The TEXT data type supports data up to 2^31–1(2,147,483,647)—2GB. The TEXT data type is deprecated, but it’s still present in SQL Server 2008 R2.
2. NTEXT
Used for Unicode variable-length text data, like the TEXT data type, this data type doesn’t support binary data. The NTEXT data type supports data up to 2^30–1(1,073,741,823)—1GB. The NTEXT data type is depreciated but is still present in SQL Server 2008 R2.
3. IMAGE
Used for variable length binary data, the IMAGE data type is the traditional LOB storage type for SQL Server, and you can store both text and binary data in it. The IMAGE data type supports data up to 2^31–1(2,147,483,647)—2GB. The IMAGE data type is depreciated, but it’s still present in SQL Server 2008 R2.
4. VARCHAR(MAX)
Used for non-Unicode variable length text data, the VARCHAR(MAX) data type supports data up to 2^31 –1(2,147,483,647)—2GB. The VARCHAR(MAX) data type was added with SQL Server 2005 and is current.
5. NVARCHAR(MAX)
Used for Unicode variable length text data, the NVARCHAR(MAX) data type supports data up to 2^30–1(1,073,741,823)—1GB. The NVARCHAR(MAX) data type was added with SQL Server 2005 and is current.
6. FILESTREAM
The FILESTREAM data type combines the performance of accessing LOBs directly from the NTFS file system with the referential integrity and direct access through the SQL Server relational database engine. It can be used for both binary and text data, and it supports files up to the size of the disk volume. The FILESTREAM data type is enabled using a combination of SQL Server and database configuration and the VARBINARY(MAX) data type. The FILESTREAM data type was added with SQL Server 2008 and is current. You can find more information about this data type in “Using SQL Server 2008's FILESTREAM Data Type."
7. XML
Used for storing XML based data.
The XML data type can also store large amounts types. The XML data type supports data up to 2^31-1(2,147,438,647)—2GB. The XML data type was added with the SQL Server 2005 release and is current.
8. VARBINARY
Technically, the VARBINARY data type is not really a LOB data type as it is limited to 8000 bytes. It can be used to store smaller binary data.
