What are the pitfalls of using sql_variant?

Question

I read and heard several times that sql_variant should be avoided. I think I have a great choice for this. I used varchar(max) in the past to store different types in one column, but it seems reasonable to avoid the de / serialization overhead when there is a built-in type that does exactly what I want.

So what are sql_variant traps? Are they performance related or frivolous programming errors or something else? By the way, I will interact with this column from client code and CLR functions, if this is something to consider.

Answer 1

I have seen performance issues and code quality issues:

In most cases, you get access to this field, you will have to check the type (using sql_variant_property). This makes your queries more complex, which can cause both problems that you list.

You will also need to use this field every time you use it, which will lead to a further decrease in performance.

In addition, sql_variant columns cannot be part of the primary key, they do not work as part of the computed column, and they do not work with LIKE in the WHERE clause.

Answer 2

It also facilitates programming errors. The DBA / Programmer looks at the column and it looks like an integer, so it puts an integer in it, but further down the line, the process wants it to be a row. I saw this with poorly written import into sql_variant columns.

Answer 3

Saving different types in one column with SQL_VARIANT is almost the same as everything you need to do for Object in .NET. And sometimes there are good reasons for using this type, since it can certainly make it possible to create a more general program structure.

However, as you expected, there are some problems with using SQL_VARIANT that you should be aware of, especially since one of them may be an unlocker:

• Just like giving all Object in .NET (and possibly requiring boxing / unpacking depending on the base type), there is a certain performance improvement when using SQL_VARIANT . Depending on the use case, performance degradation may be acceptable if the functionality really needs it and / or use is not very common (i.e. many times per second).

• Unlike all Object tags in .NET, the SQL_VARIANT data type has restrictions on what underlying data types can contain. The following data types cannot be saved as SQL_VARIANT :

  • VARCHAR(MAX)
  • NVARCHAR(MAX)
  • VARBINARY(MAX)
  • XML
  • TIMESTAMP / ROWVERSION
  • TEXT (you should not use this type anyway with SQL Server 2005)
  • NTEXT (you should not use this type anyway with SQL Server 2005)
  • IMAGE (you should not use this type anyway with SQL Server 2005)

  This restriction can easily prevent the possibility of using SQL_VARIANT if there is a requirement to store any of these data types. Note that the problem here is the underlying data type, not the data size, as the following test shows:

   DECLARE @tmp1 TABLE (col1 SQL_VARIANT NOT NULL); INSERT INTO @tmp1 (col1) VALUES (CONVERT(VARCHAR(MAX), 'g')); 

  Return:

   Msg 206, Level 16, State 2, Line 2 Operand type clash: varchar(max) is incompatible with sql_variant 

To be fair, one of the advantages of using SQL_VARIANT over NVARCHAR is that SQL_VARIANT stores information about the base type and applies its use, so you cannot easily abuse values ​​in completely inappropriate contexts.

 DECLARE @tmp2 TABLE (col1 SQL_VARIANT NOT NULL); INSERT INTO @tmp2 (col1) VALUES (1); SELECT CONVERT(DATETIME, col1) FROM @tmp2; SELECT CONVERT(TIME, col1) FROM @tmp2; 

Return:

 1900-01-02 00:00:00.000 Msg 529, Level 16, State 3, Line 6 Explicit conversion from data type int to time is not allowed. 

Regarding the inability to use SQL_VARIANT as a PC: this is really not a problem, since the nature of the general data type largely excludes it from the desire primarily for such use.

Regarding the fact that you cannot use SQL_VARIANT with the LIKE operator: this is basically not a problem, because it can convert it to the corresponding type that works with LIKE , as in:

 WHERE CONVERT(NVARCHAR(50), [sql_variant_field]) LIKE '%something%' 

The above, of course, is not the most efficient, but it is functional, and, as mentioned above, efficiency is already ruled out, since it was sacrificed for functionality when deciding to use the SQL_VARIANT data type.

Answer 4

The only obvious error that comes to mind is situations in which you have values ​​that you want to insert into the sql_variant field that exceeds its maximum length (8016 bytes to this web page: http://msdn.microsoft.com /en-us/library/ms173829.aspx ). If your values ​​never come close to this limit, then sql_variant can be a very good approach. In addition, you can still use sql_variant, but provide a separate isBlob bitfield that points to a separate table with your varbinary (max) values ​​(e.g.).