Today’s topic is one I have never written about. It is about the SQL Server data type SQL_Variant. I haven’t used it because I have always preferred strongly typed data. So, I often stay away from data types like XML or SQL_Variant. Soon, JSON will be added to the mix of undefined data types in the SQL Server family.
Regardless of what data you store in an SQL_Variant column or variable, it is limited to 8000 bytes, or a single page of data in SQL table storage. So, that allows you to save any scalar data types found in SQL Server.
One of the neat things about this data type is that you can store different kinds of data in the same column of a table, for different rows. Ah, but there is a price to pay for this flexibility. Because the data may be a different data type from one row to the next, it is difficult to do comparisons or ordering. Think of it working more like a binary data type. If you have character data in some rows, and numeric in others, what would happen when you use a comparison operator such as > (greater than)? You can find out how comparisons are evaluated at https://msdn.microsoft.com/en-us/library/ms173829.aspx.
Microsoft also provides a function allowing you to inspect the properties of a value contained within an SQL_Variant data type. The SQL_VARIANT_PROPERTY function allows you to inspect the type of data stored in an instance of the SQL_Varient, exposing the base type, precision, scale, collation, max length, and Total Bytes it contains.
Note that the ODBC driver, targeted to be the only database connectivity library, only supports the SQL_Variant data type as binary data. You may upload data as a native data type for inserts and updates, and SQL Server will implicitly convert it on your behalf. However, when you read the data, it is returned as binary, unless you cast the data while reading it. Of course this works only if you cast to different columns in your output, or only cast records having the same base data type.
You can find more details on the use of the SQL_Variant data type at https://msdn.microsoft.com/en-us/library/ms173829.aspx.
Are you using the SQL_Variant data type? How did you get around the restrictions? Has the flexibility been worth the effort? You can share yout thoughts and experience in our comments, or drop an email to btaylor@sswug.org.
Cheers,
Ben