sql_variant
Data type is a very important factor. Think about situations when we the developers are little bit confused related to data type and don't actually know what type of data actually stores. To solve this type of data type related problem the MS SQL Server provides a special kind of data type called variant. In MS SQL Server it is called sql_variant data type.
In this article, I am trying to demonstrate the feature of sql_variant data type. Hope it will be interesting.
It can't hold
We can use the sql_variant to store data of an unspecified or inconsistent type. We store data of almost any SQL Server data type.
sql_variant can hold any data type except text, ntext, image, and timestamp without conversion.
Example of sql_variant
-- Create the Base Table
IF OBJECT_ID('my_Table') IS NOT NULL
BEGIN
DROP TABLE my_Table;
END
GO
CREATE TABLE my_Table
(ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
DATA sql_variant NOT NULL);
GO
-- Insert Records
INSERT INTO my_Table
(DATA)
VALUES (GETDATE());
GO
INSERT INTO my_Table
(DATA)
VALUES ('Joydeep Das');
GO
INSERT INTO my_Table
(DATA)
VALUES (124);
GO
INSERT INTO my_Table
(DATA)
VALUES(124.22);
GO
-- Display Records
SELECT * FROM my_Table;
Result Set:
ID DATA
----------- ------------------------
1 2012-11-06 15:58:36.197
2 Joydeep Das
3 124
4 124.22
(4 row(s) affected)
Stores original data type in native format
Actually the sql_variant stores the data type in its native format. We can use the SQL_VARIANT_PROPERTY function to see the native data type.
Let's see the use of SQL_VARIANT_PROPERTY with above example.
-- To see the Base Data type.
SELECT ID,
SQL_VARIANT_PROPERTY(DATA, 'BaseType') BaseType
FROM my_Table;
Output:
ID BaseType
----------- ----------
1 datetime
2 varchar
3 int
4 numeric
(4 row(s) affected)
To see the Precisions
-- Precision
SELECT ID,
SQL_VARIANT_PROPERTY(DATA, 'Precision') Precisions
FROM my_Table;
To see the Scale
-- Scale
SELECT ID,
SQL_VARIANT_PROPERTY(DATA, 'Scale') Scale
FROM my_Table;
Practical use
There are 2 practical use of it
1. As a parameters of stored procedure.
Example:
CREATE PROCEDURE spTest (@parm sql_variant)
AS
Select @parm, SQL_VARIANT_PROPERTY(@parm, 'BaseType')
GO
We can us the functions like IsNumeric or IsDate to test what type of parameter.
2. Make the user define fields of the Table.
Some times in application the user can create user define fields (UDF)in a table to store something depends on their needs.
Some times in application the user can create user define fields (UDF)in a table to store something depends on their needs.
References
Related tropics
Summary
Please share you knowledge by making comments related to the practical use of sql_variant.
