SDU Tools: SQL Variant Info for T-SQL

If you aren’t aware of the SQL Server data type called sql_variant, don’t feel bad; you’re not alone. Many people who’ve worked with SQL Server for a very long time haven’t used it.

sql_variant is a very special data type. It’s the data type that you use when you need to store other data but you’re not sure what type you will need to store. Once it is stored though, it has the appropriate data type.

For the developers, this is a bit like var in C#. In C# if I use code like this:

CustomerLink customer = new CustomerLink();

I could have instead written:

var customer = new CustomerLink();

and the variable customer ends up being of type CustomerLink.

Similarly, in SQL Server, look at the main image above. I’ve defined a variable called @Value as being of sql_variant data type. I’ve then assigned an ASCII string to it.

In our free DBA and developer SDU Tools, we’ve added a function to let you interrogate the contents of a sql_variant. In the image, you can see that it returns a table (with a single row) showing the details of what is currently stored in the sql_variant value.

You can see it in action here:

For more information on joining SDU Insiders to get our free SDU Tools and other resources, please visit here:

http://sdutools.sqldownunder.com

2018-05-02