In the holiday tradition of giving, for the year of 2015, I want to share some of the cool things you can do with the SQL Server binary and varbinary data types.
You can easily cast just about every other data type into one of the binary data types. Varchar max can be saved in a VarBinary max, etc. Numbers, strings, dates, they all translate nicely into a binary based data type.
So, big deal, right? Well, I have found the fact that the binary data type is case sensitive, you can use it to enforce case sensitivity in strings, even if the collation you have chosen for your database is not case sensitive. In Binary, the letter C and the letter c are different. The actual ANSI or Unicode values representing these characters are what is compared; and they are different.
Because Binary datatypes are simply serialized instances of data, you can have a single column in a database store different types of data. You can store dates, strings, numbers, formulas, complex data types, etc. all in a binary data type. The issue for you is that you need to know what the column or variable contains so that you can cast it into something meaningful for you to use. This is much the same as the SQL_VARIANT data type. The difference is that it does not have the properties available, as a SQL_VARIANT does, exposing the base type contained in an instance. You have to know that yourself in order to cast it properly.
Sorting and comparing of binary values will based on the byte values of the data it contains. This will, of course, change with the collation selected for the column.
You can use binary data types for so much more than just blob data. Make your own complex data type and store it in a binary column. It’s not just for images.
There you go. Have a happy holiday.
Cheers,
Ben