Travis Page wants you to think twice before using VARBINARY(MAX):
Brent Ozar put out a call for blogging about your data type of choice. This might be a favorite (or least favorite) datatype. Naturally, varchar(max) and nvarchar(max) are going to have their punishment, deservedly. The datatype I’m not so sure is going to be getting the bludgeoning it deserves is varbinary(max). Sure, there are valid uses for the data type, but having the potential for just shy of 2GB of binary LOB data stored in a database has some negative potentials. Let’s take a look at some of these pitfalls.
Click through for a good reckoning of the downsides. In terms of upside, two good ones that I actively use are:
VARBINARY(MAX), when combined with theCOMPRESS()function, can efficiently store a lot of text data, as that text data gets gzipped. This works best if you just need to store data but not search through it. Then,DECOMPRESS()when it’s time for that data to show up in an app somewhere.- SQL Server Machine Learning Services models are stored in
VARBINARY(MAX).