T-SQL Tuesday #136 – My Favourite Data Type

T-SQL Tuesday Logo

Another month, another T-SQL Tuesday. This time we have to thank Brent Ozar (B|T) for the topic “Blog About Your Favorite Data Type (Or Least Favorite)“, sorry Brent but I have to add the extra ‘u’ back for the title for my post 😉.

Everyone, meet varbinary.

I have to admit that I am a fan of the varbinary type. This is for several reasons, most notably is it’s versatility and that it underpins a raft of SQL Server capability. So, what specifically do I like so much that makes it my favourite?

The Store Anything Type

Varbinary lets us essentially store anything we want to. From storing blob data in our databases either directly (please don’t do this), via filestream, or with FileTable, varbinary is there helping us meet our goals. Sticking with the storing of something less structured it is also the datatype that is returned by the COMPRESS() and DECOMPRESS() T-SQL functions. Building on these functions it means that we can look to store JSON, XML, etc. in the database but in a compressed form to help save on storage space. There are some other This lets us basically store anything that we want in this data type which can be very useful in certain circumstances. Would I recommend this, not over using alternative solutions for a dedicated non-structured data source. If you do want to dive down this rabbit hole I would check out this tweet and its thread by Randolph (B|T) for some commentary and other options.

Supporting Actor Nomination

Varbinary also plays a supporting role in a couple of security capabilities which I have used over the years and think are really cool. Notably they all centre around column level encryption capabilities. Prior to SQL Server 2016 we were limited in our options for column level encryption. We either did it app layer and stored the ciphertext, or we could use the following encryption functions.

These functions would take our clear text, encrypt it, then output varbinary, with the corresponding decryption functions doing the reverse. Again, we would be storing the data in the tables as the varbinary type.

With SQL Server 2016 Microsoft introduced a new column level encryption feature called Always Encrypted. This was an Enterprise Edition feature only prior to SP1 for SQL Server 2016, but Microsoft saw sense that security really ought not to be an optional feature only for those with deep pockets and now we get it in all editions. While this does not use varbianry I wanted to mention it as a preferable option to the functions above because of how much better it allows separation of duties to be implemented.

Summary

So, while not something that we will use every day I have to say that having varbinary in my toolbox as an option to that I can use in a bind. I like to think of it as the SQL Server equivalent of the Wera Koloss ratchet, which is also a hammer, something that can be precise but also something that I can use to knock in nails, and can also hurt when you clobber your finger.

One thought on “T-SQL Tuesday #136 – My Favourite Data Type

Leave a Reply to Brent Ozar Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.