This post looks at a curious data type that isn’t really a data type. Instead, sql_variant tries to be all things to all people. As with most things in life, it has a few shortcomings as a result.

If you would like to read about storage of other data types, here are the previous posts in the series:

By a show of hands, who uses the sql_variant data type? In my experience the answer is “no one,” and that’s not necessarily a bad thing. There’s a general philosophy in our industry where we assume that columns are a specific data type. This predictability makes writing queries and applications against those columns easier. It’s difficult enough dealing with NULL values, so adding confusion with handling data conversions is an invitation to introduce bugs.

I wonder, however, how many of my readers know that some of our favourite internal tables in SQL Server use sql_variant, including the venerable sys.configurations?

The official documentation for this data type - available from Microsoft Docs - tells us that:

[A] column defined as sql_variant can store int, binary, and char values [up to] a maximum length of 8016 bytes. […] The maximum length of the actual base type value is 8,000 bytes.

Another aspect of this data type is that it is not fully supported by ODBC, so depending on the driver you use to connect to the database the underlying column will be returned in binary.

This last sentence gives us a clue as to how the SQL Server and Azure SQL Database storage engine might persist a sql_variant column.

#database #tutorial #sql #sql server #data-science