Solved

SQL2005's varbinary(MAX)

Posted on 2007-04-09
1
1,516 Views
Last Modified: 2012-06-21
Can SQL2005's varbinary(MAX) actually store large DOC, PDF, XLS, JPG files in full? I mean these files are physically stored in a table column instead of under a folder in the file system? Thanks.
0
Comment
Question by:ksfok
1 Comment
 
LVL 11

Accepted Solution

by:
dready earned 500 total points
ID: 18879475
When used for tables, it's important to realize that the MAX types have a slightly different row overflow behavior than the TEXT and IMAGE types. In SQL Server, the maximum row size is 8060 bytes. To get around this restriction and still manage storage of up to 2 GB per instance, data stored using the TEXT and IMAGE types is automatically placed off-row by the storage engine, leaving only a 16-byte pointer in the row. That means row sizes are decreased, which is good for performance. Retrieving the large data is expensive, however, since it is not stored in the same place as the on-row data.

The MAX data types, by default, use a hybrid of the TEXT/IMAGE overflow behavior and the behavior of the normal (sized) VARCHAR/VARBINARY types. If a column's data, plus the data in all of the other columns in the table, has a total size of less than 8060 bytes, the data is stored in-row. If the data exceeds 8060 bytes, the data in the MAX column will be stored off-row.

You can change the default behavior of the MAX data types on a per-table basis and they'll behave just like the TEXT and IMAGE types. This is accomplished using the "large value types out of row" option of the sp_tableoption stored procedure.  Use the following T-SQL:

EXEC sp_tableoption
    'myTable',
    'large value types out of row',
    '1'

0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question