Home > Datatypes > SQL_VARIANT datatype

SQL_VARIANT datatype


According to MS BOL SQL_VARIANT can be used in columns, parameters, variables, and the return values of user-defined functions. sql_variant enables these database objects to support values of other data types.

It can have a maximum length of 8016 bytes including both the base-type information and the base-type value. Thus the maximum length of the actual base-type value is 8,000 bytes.

USE [tempdb]
GO

DECLARE @var SQL_VARIANT

-- Set variable as DATETIME type
SET @var =GETDATE()
SELECT @var as Data, SQL_VARIANT_PROPERTY(@var , 'BaseType') as BaseType
, SQL_VARIANT_PROPERTY(@var , 'Precision') as Precision
, SQL_VARIANT_PROPERTY(@var , 'Scale') as Scale
, SQL_VARIANT_PROPERTY(@var , 'TotalBytes') as TotalBytes
, SQL_VARIANT_PROPERTY(@var , 'MaxLength') as MaxLength
, SQL_VARIANT_PROPERTY(@var , 'Collation') as Collation;
Output:
Data            	BaseType Precision Scale TotalBytes MaxLength Collation
2011-01-27 17:13:53.150	datetime 23        3     10         8         NULL
-- Set variable as INT type
SET @var = 1234
SELECT @var as Data, SQL_VARIANT_PROPERTY(@var , 'BaseType') as BaseType
, SQL_VARIANT_PROPERTY(@var , 'Precision') as Precision
, SQL_VARIANT_PROPERTY(@var , 'Scale') as Scale
, SQL_VARIANT_PROPERTY(@var , 'TotalBytes') as TotalBytes
, SQL_VARIANT_PROPERTY(@var , 'MaxLength') as MaxLength
, SQL_VARIANT_PROPERTY(@var , 'Collation') as Collation;
Output:
Data	BaseType Precision Scale TotalBytes MaxLength Collation
1234	int      10        0     6          4         NULL
-- Set variable as VARCHAR type
SET @var = SYSTEM_USER
SELECT @var as Data, SQL_VARIANT_PROPERTY(@var , 'BaseType') as BaseType
, SQL_VARIANT_PROPERTY(@var , 'Precision') as Precision
, SQL_VARIANT_PROPERTY(@var , 'Scale') as Scale
, SQL_VARIANT_PROPERTY(@var , 'TotalBytes') as TotalBytes
, SQL_VARIANT_PROPERTY(@var , 'MaxLength') as MaxLength
, SQL_VARIANT_PROPERTY(@var , 'Collation') as Collation;
Output:
Data        BaseType Precision Scale TotalBytes MaxLength Collation
XXX\mpandey nvarchar 0         0     30         256       Latin1_General_CS_AS
-- Set variable as BIT type
SET @var = cast(1 as bit)
SELECT @var as Data, SQL_VARIANT_PROPERTY(@var , 'BaseType') as BaseType
, SQL_VARIANT_PROPERTY(@var , 'Precision') as Precision
, SQL_VARIANT_PROPERTY(@var , 'Scale') as Scale
, SQL_VARIANT_PROPERTY(@var , 'TotalBytes') as TotalBytes
, SQL_VARIANT_PROPERTY(@var , 'MaxLength') as MaxLength
, SQL_VARIANT_PROPERTY(@var , 'Collation') as Collation;
Output:
Data	BaseType Precision Scale TotalBytes MaxLength Collation
1	bit      1         0     3          1         NULL

More on SQL_VARIANT: http://msdn.microsoft.com/en-us/library/ms173829.aspx

About these ads
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 379 other followers

%d bloggers like this: