SDU Tools: SQL Variant Info for T-SQL

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.

sql_variant isn’t perfect. Unfortunately, there have been a number of times that the SQL Server team have added features and sql_variant hasn’t worked with them. A good example was when datetimeoffset was released. At first, code like this would have failed:

USE tempdb;
GO

DECLARE @When datetimeoffset;
DECLARE @DataValue sql_variant = @When;
GO

That works fine now (since SQL Server 2012), but if you try this:

USE tempdb;
GO

DECLARE @Embedding vector(1024);
DECLARE @DataValue sql_variant = @Embedding;
GO

you get an error returned:

Msg 206, Level 16, State 2, Line 5
Operand type clash: vector is incompatible with sql_variant

I really wish this wasn’t so. At present, Microsoft Learn lists the data types that don’t work:

  • datetimeoffset
  • geography
  • geometry
  • hierarchyid
  • image
  • ntext
  • nvarchar(max)
  • rowversion (timestamp)
  • text
  • varchar(max)
  • varbinary(max)
  • User-defined types
  • xml

A few of those are understandable, perhaps the SQL CLR types like geography, geometry, hierarchyid, and the user-defined types, and also perhaps the deprecated types like image, ntext, and text. But the rest are quite disappointing.

And the list is incomplete. I’ve already shown that vector doesn’t work. Nor does json. (I’ve submitted a pull request - hopefully that’ll be updated soon).

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:

YouTube Video

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

https://sqldownunder.com/freestuff

2026-11-14