Should I Use SQL_Variant Data Type

Should I use SQL_Variant data type?

10 reasons to explicitly convert SQL Server data types

As a general rule, you should avoid using SQL Server’s sql_variant
data type. Besides being a memory hog, sql_variant is limited:

• Variants can’t be part of a primary or foreign key. (this doesn't hold as of SQL Server 2005. See update below)
• Variants can’t be part of a computed column.
• Variants won’t work with LIKE in a WHERE clause.
• OLE DB and ODBC providers automatically convert variants to nvarchar(4000) — ouch!

To avoid problems, always explicitly convert sql_variant data types as
you use them. Use any method you please, just don’t try to work with
an unconverted sql_variant data type.

I haven't used sql_variant before but with these restrictions and performance implications in mind, I would first look at alternatives.

Following would be my most to least prefered solution

• Simply create three different columns. 3 Different data types (should) mean 3 different ways of interpreting it both at the client side and server side.
• If that is not an option, use a VARCHAR column so you can at least use LIKE statements.
• Use the sql_variant data type.

Edit Cudo's to ta.speot.is

Variants can be part of a primary of foreign key

A unique, primary, or foreign key may include columns of type
sql_variant, but the total length of the data values that make up the
key of a specific row should not be more than the maximum length of an
index. This is 900 bytes

How does a non-clustered index with SQL Variant as one of the columns work?

I have never used this data type myself.

Update:

Found an article that demonstrates a way to use SQL_VARIANT data type: https://aboutsqlserver.com/2012/02/22/store-custom-fieldsattributes-in-microsoft-sql-server-database-part-2-namevalue-pairs/?unapproved=201416&moderation-hash=771c41a02ff9a7c909e93140a8795e3a#comment-201416

Ordering

From reading documentation, specifically value comparison part I can tell that there will be cases where ordering will not look "natural" e.g.

CREATE TABLE #Test( a SQL_VARIANT )
INSERT INTO #Test VALUES( 2 ) -- INT
INSERT INTO #Test VALUES( 2.1 ) -- DECIMAL
INSERT INTO #Test VALUES( '3' ) -- VARCHAR
INSERT INTO #Test VALUES( CAST( 1.8 AS FLOAT ) )
INSERT INTO #Test VALUES( DATEFROMPARTS( 2020, 1, 1 ) )
SELECT *, SQL_VARIANT_PROPERTY ( a , 'BaseType' ) FROM #Test ORDER BY a
DROP TABLE #Test

Results (sorted by col a in ascending order):

a                          
-------------------------- -----------
3                          varchar
2                          int
2.1                        numeric
1.8                        float
2020-01-01 00:00:00.000    date

Index Performance

I don't think there will be any performance issues with index seeks. Inserts and Updates will likely take a penalty

Gotchas

There will be a lot of little gotchas when working with this data type. Some examples are below:

1| WHERE conditions will not match unless data types match:

-- Returns nothing
SELECT *, SQL_VARIANT_PROPERTY ( a , 'BaseType' ), SQL_VARIANT_PROPERTY ( a , 'TotalBytes' )
FROM #Test
WHERE a = '2'
ORDER BY a

-- Returns nothing
SELECT *, SQL_VARIANT_PROPERTY ( a , 'BaseType' ), SQL_VARIANT_PROPERTY ( a , 'TotalBytes' )
FROM VariantTest
WHERE a = 3
ORDER BY a

2| This will not insert data with correct data types as data types will first be implicitly cast to VARCHAR before being cast to SQL_VARIANT:

INSERT INTO #Test VALUES( 2 ), ( 2.1 ), ( '3' ), CAST( 1.8 AS FLOAT )

I want to use SQL_VARIANT datatype in external table Azure SQL and I get the Index was out of range error.

Well - after a weekend of sleep I figured out the answer!

If you use nvarchar(30) in he external table definition - you can then convert it to a bigint in any query you use it in

CREATE EXTERNAL TABLE [SOURCE_SYS].[identity_columns](
      [object_id] int not null
      ,[name] nvarchar(128) null
      ,[column_id] int not null
      ,[system_type_id] tinyint not null
      ,[seed_value] nvarchar(30) null
    )
    WITH
    (
    DATA_SOURCE = MyElasticDBQueryDataSrc,
    SCHEMA_NAME = 'sys',
    OBJECT_NAME = 'identity_columns'
    );

Now I can access the value like this:

select cast(isnull([seed_value], 0) as bigint) from SOURCE_SYS.identity_columns

Beware that if you do a select * from - you will need to do the variants separately from the rest of the query - you'll get this error:

Msg 46825, Level 16, State 1, Line 58
The data type of the column 'seed_value' in the external table is different than the column's data type in the underlying standalone or sharded table present on the external source.

Hope this is helpful to someone!

Original data type reference when using Sql_Variant

You can create a user-defined table type to pass array of parametes as table-valued parameter:

SQL Code:
CREATE TYPE MyType AS TABLE (ID int, Value Sql_Variant)

CREATE PROCEDURE SP_NAME 
    @Values dbo.MyType READONLY
AS
    --@Values is usual table variable (but readonly) and you can select from it like from tables in database
    INSERT INTO SomeDBTable
    SELECT * 
    FROM @Values V
    WHERE V.ID <= 100500

.NET Code

DataTable dtList = new DataTable();
List<SqlDataRecord> filterList = new List<SqlDataRecord>();
foreach (KeyValuePair<string, object> filter in arrFilterList)
{
    SqlDataRecord record;

    record = new SqlDataRecord(new SqlMetaData[] { new SqlMetaData("ID", SqlDbType.Int),
                                      new SqlMetaData("Value", SqlDbType.Variant) });
    record.SetInt(0, filter.Key);
    record.SetValue(1, filter.Value);
    filterList.Add(record);
}
SqlCommand oCommand = new SqlCommand("SP_NAME", connection);
oCommand.CommandType = CommandType.StoredProcedure;
oCommand.Parameters.AddWithValue("@Values", filterList.Count > 0 ? filterList : null);
oCommand.Parameters["@Values"].SqlDbType = SqlDbType.Structured;
oCommand.Parameters["@Values"].TypeName = "dbo.MyType";

Why sql_variant datatype only returns string? in SQL-Server and VS# debugger?

If you really really had to use sql_variant, you would need to insert the values via INSERT statements, and thus specify the datatype:

INSERT INTO test values (cast (1 as int))  
INSERT INTO test values (cast (20.5 as decimal(8, 2)))  
INSERT INTO test values (cast ('hello' as nvarchar))  

Then when you run your query you see the values and data types:

---

Related Topics