SQL with Manoj

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