SQL Server 2008 R2 Max Varistor Size

Question

What is the maximum file size that I can insert using varbinary (max) in SQL Server 2008 R2? I tried to change the maximum value in the column to more than 8000 bytes, but this will not allow me, so I assume that max is 8000 bytes, but from this article on MSDN , it says that the maximum storage size is 2 ^ 31-1 bytes :

varbinary [(n | max )]

Binary data of variable length. n can be a value from 1 to 8000. max indicates that the maximum storage size is 2 ^ 31-1 bytes. Storage size is the actual length of the entered data + 2 bytes. Entered data can be 0 bytes long. The ANSI SQL synonym for varbinary is binary different .

So how can I store large files in a varbinary field? I do not consider using FILESTREAM, since the files I want to save are from 200 kb to 1 mb, the code that I use:

UPDATE [table] SET file = ( SELECT * FROM OPENROWSET ( BULK 'C:\A directory\A file.ext', SINGLE BLOB) alias) WHERE idRow = 1 

I managed to execute this code successfully so that the files were less than or equal to 8000 bytes. If I try with a file size of 8001 bytes, it will fail. In my file field in the table there is a field of type "file" varbinary(8000) , which, as I said, cannot change to a larger value.

Answer 1

I can not reproduce this scenario. I tried the following:

 USE tempdb; GO CREATE TABLE dbo.blob(col VARBINARY(MAX)); INSERT dbo.blob(col) SELECT NULL; UPDATE dbo.blob SET col = (SELECT BulkColumn FROM OPENROWSET( BULK 'C:\Folder\File.docx', SINGLE_BLOB) alias ); SELECT DATALENGTH(col) FROM dbo.blob; 

Results:

 -------- 39578 

If this gets an 8K limit, I would suggest that either one of the following is true:

• Actually the VARBINARY(8000) column VARBINARY(8000) .

• You select the data in Management Studio and analyze the length of the data that is displayed there. This is limited to a maximum of 8192 characters in the results for the text, if so, so using DATALENGTH() directly against the column is a much better approach.

Answer 2

I would dare to say that to use the file stream for files larger than 1 MB based on the following: MS TechNet | Overview of FILESTREAM .

In SQL Server, BLOBs can be standard varbinary(max) data that stores data in tables or FILESTREAM varbinary(max) objects that store data in a file system. The size and use of the data determines whether to use database storage or file system storage. If the following conditions are true: you must use FILESTREAM :

• Objects that are stored are on average larger than 1 MB.
• Fast read access is important.
• You develop applications that use a middle tier for application logic.

For small objects that store varbinary(max) BLOB in the database often provides better streaming performance.

Answer 3

"SET TEXTSIZE" Defines the size of varchar(max) , nvarchar(max) , varbinary(max) , text , ntext and image data returned by the SELECT .

 select @@TEXTSIZE 

The internal ODBC client for SQL Server and the OLE DB provider for SQL Server TEXTSIZE Client for SQL Server automatically install TEXTSIZE on 2147483647 when connecting. The maximum value for SET TEXTSIZE is 2 gigabytes (GB), specified in bytes. A value of 0 resets the default size (4 KB).

As already mentioned, for large files you prefer file stream.