THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

Using the sql_variant datatype in CLR

I am not generally a proponent of creating tables with columns of datatype sql_variant in them.  Often times these tables seem to be a really great idea at first, but then slowly begin to become problemattic.  However, if you do happen to use the sql_variant columns in your database eventually you may need to use those columns in your application code.  Microsoft provides a datatype mapping table between the SQL native datatypes the CLR datatypes for SQL Server, and the CLR native datatypes in the Books Online.  If you look at the table, you will notice that there is no SQL Server CLR datatype for the sql_variant datatype, and the CLR native type is Object, the base for all types in the CLR runtime.

So how exactly do you use a sql_variant in CLR?

Well first, lets look at how you would use one in SQL Server.  To do this, we'll create a table called SpecialData and we'll load it with a few rows of differing datatypes:

CREATE TABLE SpecialData   
(RowID INT IDENTITY PRIMARY KEY,   
 
Data sql_variant)   
    
DECLARE @varchar VARCHAR(20)   
SET @varchar = 'some data'  
INSERT INTO SpecialData VALUES (@varchar)   

DECLARE @datetime datetime   
SET @datetime = GETDATE()   
INSERT INTO SpecialData VALUES (@datetime)   

DECLARE @decimal decimal(10,2)   
SET @decimal = 12.22   
INSERT INTO SpecialData VALUES (@decimal)

DECLARE @int INT
SET
@int = 100
INSERT INTO SpecialData VALUES (@int)

DECLARE @float float
SET @float = 1.23
INSERT INTO SpecialData VALUES (@float)
 
DECLARE @tinyint tinyint
SET @tinyint = 4
INSERT INTO SpecialData VALUES (@tinyint)

DECLARE @bigint bigint
SET @bigint = 100
INSERT INTO SpecialData VALUES (@bigint

INSERT INTO SpecialData VALUES (NULL)
  
SELECT *
FROM SpecialData

Just looking at the output information from the select statement, it looks like we have a couple of decimal values, some integer values, a datetime value and a string value in our data.  One of the problems that people encounter when using sql_variant columns is datatype conversion problems either through incorrect filtering of the data in the column, or simply because they don't know how to find out what datatype the value for a specific row has.

Included in SQL Server is the SQL_VARIANT_PROPERTY() function which will provide the definition of the data that is stored in a sql_variant column:

SELECT *, 
  
SQL_VARIANT_PROPERTY(data,'BaseType') AS basetype,
  
SQL_VARIANT_PROPERTY(data,'Precision') AS PRECISION,
  
SQL_VARIANT_PROPERTY(data,'Scale') AS scale,
  
SQL_VARIANT_PROPERTY(data,'BaseType') AS basetype,
  
SQL_VARIANT_PROPERTY(data,'TotalBytes') AS totalbytes,
  
SQL_VARIANT_PROPERTY(data,'Collation') AS collation,
  
SQL_VARIANT_PROPERTY(data,'MaxLength') AS maxlength
FROM SpecialData 

Using this function, we can now see easily what the datatype for each row's column data is. Now how do we do something similar in CLR, especially when the only type that we can map the column to is a .NET object? Well for those familiar with programing in CLR and .NET, the answer is that while sql_variant is mapped to an Object, it is actually a boxed SQL Server CLR datatype. For those not familiar with programming in .NET, essentially all datatypes, classes, structs are Objects, so any of them can be "boxed" as an Object datatype.  To get more indepth coverage of this subject I'd recommend that you read one of the following articles:

CLR Boxing Demystified « Developer Flotsam
Classes, Structs, and Objects—Boxing and Unboxing at C# Online.NET ...

Since our sql_variant data can be typed in CLR as an Object, we can use the .GetType() method to determine what the actual datatype is for the data being stored in a particular row.  This in turn has a .Name Property that can be used to get the actual name of the SQL Server CLR Datatype that the Object can be cast into to get the value back out of the Object:

using System;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace Sql_VariantExample
{
   
class Program
    {
       
static void Main(string[] args)
       
{
           
string line = "RowID: {0}\tDataType: {1}\tValue: {2}";

           
using (SqlConnection conn = new SqlConnection("Data Source=ServerName;Initial Catalog=DatabaseName;Integrated Security=SSPI;"))
           
{
                conn.Open
();

               
using (SqlCommand cmd = new SqlCommand("select * from SpecialData", conn))
               
{
                    SqlDataReader dr
= cmd.ExecuteReader();

                   
while (dr.Read())
                   
{
                       
object obj = dr.GetSqlValue(1);

                       
if(obj is SqlInt16)
                       
{
                            Console.WriteLine
(string.Format(line, dr.GetInt32(0).ToString(), obj.GetType().Name, ((SqlInt16)obj).Value.ToString()));
                       
}
                       
else if(obj is SqlInt32)
                       
{
                            Console.WriteLine
(string.Format(line, dr.GetInt32(0).ToString(), obj.GetType().Name, ((SqlInt32)obj).Value.ToString()));
                       
}
                       
else if (obj is SqlInt64)
                       
{
                            Console.WriteLine
(string.Format(line, dr.GetInt32(0).ToString(), obj.GetType().Name, ((SqlInt64)obj).Value.ToString()));
                       
}
                       
else if (obj is SqlDecimal)
                       
{
                            Console.WriteLine
(string.Format(line, dr.GetInt32(0).ToString(), obj.GetType().Name, ((SqlDecimal)obj).Value.ToString()));
                       
}
                       
else if (obj is SqlDateTime)
                       
{
                            Console.WriteLine
(string.Format(line, dr.GetInt32(0).ToString(), obj.GetType().Name, ((SqlDateTime)obj).Value.ToString()));
                       
}
                       
else if (obj is SqlString)
                       
{
                            Console.WriteLine
(string.Format(line, dr.GetInt32(0).ToString(), obj.GetType().Name, ((SqlString)obj).Value.ToString()));
                       
}
                       
else if (obj is SqlDouble)
                       
{
                            Console.WriteLine
(string.Format(line, dr.GetInt32(0).ToString(), obj.GetType().Name, ((SqlDouble)obj).Value.ToString()));
                       
}
                       
else if (obj is SqlChars)
                       
{
                            Console.WriteLine
(string.Format(line, dr.GetInt32(0).ToString(), obj.GetType().Name, ((SqlChars)obj).Value.ToString()));
                       
}
                       
else if (obj is SqlByte)
                       
{
                            Console.WriteLine
(string.Format(line, dr.GetInt32(0).ToString(), obj.GetType().Name, ((SqlByte)obj).Value.ToString()));
                       

                    }
                }
                conn.Close
();
           
}
        }
    }
}

Now to be perfectly honest, some .NET guru is probably laughing at the above example, but hey, I am a DBA not a C# developer full time. I am sure that there is probably a much better way to do this in .NET using Reflection or some of the other features that I haven't learned yet.  It should however provide enough of a general idea to demonstrate how to use a sql_variant column in CLR.

Published Wednesday, January 21, 2009 7:32 AM by Jonathan Kehayias

Comments

 

Sankar Reddy said:

Jonathan,

Good Info but with my little brain I can't seem to find at-least one good reason to use SQL_Variant in the SQL world where we preach to maintain data integrity using PK, FK, defaults, constraints, UNIQUE etc...

With the limitations of SQL_Variant, doesn't it make it complex to support the queries against it. Are we not better off without using it in the SQL world? If there is a genuine reason then I definitely open to learn and catch up on it.

--Sankar.

January 21, 2009 3:18 PM
 

Jonathan Kehayias said:

Sankar,

As the first sentence of this post said, "I am not generally a proponent of creating tables with columns of datatype sql_variant in them."  These are used for EAV tables (entity attribute value) where data may or may not exist for most of your population set.  I'll post another post next week with a example EAV and why it is problematic, and why it might be seen as a good thing.

January 22, 2009 10:39 AM
 

Mark Gillespie said:

Actually in some environments there are some really good reason for using the SQL_VARIANT type.  Working with a lot of Chemistry related data we saw a varchar(xxx) for a comment on an entry, then the next row we saw a datetime, on the next one we saw a decimal value...so you see, you can't always predict what the data will wind up being, hence, the sql_variant type.  Just because you don't see a need doesn't mean the need doesn't exist.  

April 19, 2009 1:32 AM
 

Jonathan Kehayias said:

Mark,

I am not saying anywhere in here that the need doesn't exist.  Often times this is a misused construct in SQL Server.  One just has to look at the MSDN forums to see how problemattic this datatype can be when misused.  If you look at the DMV's in the Books Online, there are quite a few that have sql_variant columns in their outputs.  There are certainly places where it can be used or it wouldn't still be a part of the product line.  In most cases where I see sql_variants being used it is not due to an actual need, but a lack of proper modeling of data, generally from vague or incorrect requirements up front.

April 19, 2009 1:32 PM
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement