Size of Varbinary Field in SQL Server 2005

Size of VARBINARY field in SQL Server 2005

Actually, you can do this in T-SQL!

DATALENGTH(<fieldname>) will work on varbinary(max) fields.

Max real space in a varbinary(max) in SQL Server

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 gigabytes
• varbinary(max) FILESTREAM is limited by your file system (FAT32 - 2 Gb, NTFS - 16 exabytes)

Is there a big technical difference between VARBINARY(MAX) and IMAGE data types?

They store the same data: this is as far as it goes.

"image" is deprecated and has a limited set of features and operations that work with it. varbinary(max) can be operated on like shorter varbinary (ditto for text and varchar(max)).

Do not use image for any new project: just search here for the issues folk have with image and text datatypes because of the limited functionality.

Examples from SO: One, Two

Using MD5 in SQL Server 2005 to do a checksum file on a varbinary

Two options

1. VARBINARY type without size modifier utilizes VARBINARY(1), so you are hashing the very 1st byte of file, SELECT DATALENGTH(@HashThis) after assignment will bring to you 1
2. If you use varbinary(MAX) instead - then keep in mind, that HASHBYTES hashes only first 8000 bytes of input

If you want to perform hashing more than 8000 bytes - write your own CLR hash function, for example the file is from my sql server project, it brings the same results as other hash functions outside of sql server:

using System;
using System.Data.SqlTypes;
using System.IO;

namespace ClrHelpers
{
    public partial class UserDefinedFunctions {
        [Microsoft.SqlServer.Server.SqlFunction]
        public static Guid HashMD5(SqlBytes data) {
            System.Security.Cryptography.MD5CryptoServiceProvider md5 = new System.Security.Cryptography.MD5CryptoServiceProvider();
            md5.Initialize();
            int len = 0;
            byte[] b = new byte[8192];
            Stream s = data.Stream;
            do {
                len = s.Read(b, 0, 8192);
                md5.TransformBlock(b, 0, len, b, 0);
            } while(len > 0);
            md5.TransformFinalBlock(b, 0, 0);
            Guid g = new Guid(md5.Hash);
            return g;
        }
    };
}

VarBinary vs Image SQL Server Data Type to Store Binary Data?

Since image is deprecated, you should use varbinary.

per Microsoft (thanks for the link @Christopher)

ntext , text, and image data types will be removed in a future
version of Microsoft SQL Server. Avoid using these data types in new
development work, and plan to modify applications that currently use
them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

Fixed and variable-length data types for storing large non-Unicode and
Unicode character and binary data. Unicode data uses the UNICODE UCS-2
character set.

From Image to Varbinary(max)

You can use sp_tableoption to set "large value types out of row"

Some additional info

---

Related Topics