Presents your 
SQL SERVER E-NEWSLETTER for September 24, 2002

<------------------------------------------->

IDENTIFY BASE DATA TYPE WITH SQL_VARIANT_PROPERTY

The SQL_VARIANT_PROPERTY function will identify the base data type used
in a table column's value where the column has been defined as a
SQL_VARIANT data type. However, it isn't the column's data type that the
function identifies; it's the actual value's data type that's stored in the
column. The SQL_VARIANT data type is an abstraction of the real data type.

The sample script below demonstrates the SQL_VARIANT_PROPERTY function:

SET NOCOUNT ON
GO
CREATE TABLE MyTable(counter INT IDENTITY PRIMARY KEY,
col1 SQL_VARIANT)
GO
INSERT MyTable ( col1 ) VALUES ( 'ABCDEF' )
GO
INSERT MyTable ( col1 ) VALUES ( 123)
GO
INSERT MyTable ( col1 ) VALUES ( $34.25 )
GO
INSERT MyTable ( col1 ) VALUES ( 34.39283 )
GO
SELECT * FROM MyTable
GO
SELECT
CHAR(10),SQL_VARIANT_PROPERTY ( col1, 'Value' ),
CHAR(10),SQL_VARIANT_PROPERTY ( col1, 'BaseType' ),
CHAR(10),SQL_VARIANT_PROPERTY ( col1, 'Precision' ),
CHAR(10),SQL_VARIANT_PROPERTY ( col1, 'Scale' ),
CHAR(10),SQL_VARIANT_PROPERTY ( col1, 'TotalBytes' ),
CHAR(10),SQL_VARIANT_PROPERTY ( col1, 'Collation' ),
CHAR(10),SQL_VARIANT_PROPERTY ( col1, 'MaxLength' )
FROM MyTable
GO
DROP TABLE MyTable
GO

In the script, there are four different types of values being inserted
into a table with a column, col1, defined as a SQL_VARIANT. The first
value is a string, ABCDEF; another value is a money amount, $34.25.

The SQL_VARIANT_PROPERTY function, using the argument BaseType, will
determine the data type value in the column. For ABCDEF, the function
returns the VARCHAR data type for that value. For $34.25,
SQL_VARIANT_PROPERTY returns the MONEY data type for that value.

----------------------------------------