Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL2005's varbinary(MAX)

Posted on 2007-04-09
1
Medium Priority
?
1,526 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 11

Accepted Solution

by:
dready earned 2000 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

664 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