Menu
I am saving files (any type ) in a SQL table, using a varbinary(max), I find out that the max usage of this datatype is 8000, but what does the 8000 mean?
The online documentation says that is 8000 bytes. Does that mean that the maximum size of the file to be save there is 8000/1024 = 7.8125 KB?
I start testing and the maximum file that I can store is 29.9 MB. If I choose a larger file a get a SQLException.
String or binary data would be truncated. The statement has been terminated.
794
Description. The VARBINARY type is similar to the VARCHAR type, but stores binary byte strings rather than non-binary character strings. M represents the maximum column length in bytes. It contains no character set, and comparison and sorting are based on the numeric value of the bytes.
The maximum storage size for SQL Server DB instances is the following: General Purpose (SSD) storage – 16 TiB for all editions. Provisioned IOPS storage – 16 TiB for all editions.
INSERT INTO #TempTable(PK, VarBinaryColumn) SELECT PK, VarBinaryColumn FROM dbo. YourPermanentTable; If you need to convert the varbinary data back to the original file text format in T-SQL, you can use CAST or CONVERT to convert to varchar or nvarchar as long as the data was originally ASCII or Unicode.
Taken from here:
http://msdn.microsoft.com/en-us/library/ms188362.aspx:
max indicates that the maximum storage size is 2³¹-1 bytes
which is 2 147 483 647 bytes. I'm not sure why it stops at 29.9MB.
29
Implement SQL Server 2012 (codename Denali) when it's released - it has FileTable feature :)
varbinary(8000) is limited by 8000 bytes - that's for sure!varbinary(max) is limited by 2 gigabytesvarbinary(max) FILESTREAM is limited by your file system (FAT32 - 2 Gb, NTFS - 16 exabytes)
64
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
