SQL Server/T-SQL Tutorial/Data Types/SQL VARIANT — различия между версиями

Материал из SQL эксперт
Перейти к: навигация, поиск
м (1 версия)
 
м (1 версия)
 
(нет различий)

Текущая версия на 13:25, 26 мая 2010

Comparing SQL_VARIANTs

   <source lang="sql">

4> IF CAST(12.0 AS SQL_VARIANT) > CAST(10E AS SQL_VARIANT) 5> PRINT "Bigger" 6> ELSE 7> PRINT "Smaller"; 8> GO Smaller</source>


Insert different type of values into a sql_variant column

   <source lang="sql">

3> CREATE TABLE var (a int, b sql_variant) 4> GO 1> INSERT INTO var VALUES (1, 3) 2> INSERT INTO var VALUES (2, 3000000000) 3> INSERT INTO var VALUES (3, "abc") 4> INSERT INTO var VALUES (4, current_timestamp) 5> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> select * from var; 3> GO a b












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







































         1 3











         2 3000000000











         3 abc











         4 2008-08-17 13:17:55.547000000











(4 rows affected) 1> 2> drop table var; 3> GO 1></source>


Select case and cast SQL_VARIANT

   <source lang="sql">

2> SELECT 3> CASE 4> WHEN 1 > 1 THEN CAST(10 AS SQL_VARIANT) 5> WHEN 1 = 1 THEN CAST("abc" AS SQL_VARIANT) 6> WHEN 1 < 1 THEN CAST(10. AS SQL_VARIANT) 7> END; 8> GO



















































abc











(1 rows affected) 1> 2></source>


sql_variant data type is designed to allow a single variable, column, or parameter to store values in different data types.

   <source lang="sql">

Internally, variant objects record two values: The actual value The metadata describing the variant: base data type, maximum size, scale, precision, and collation 13> Create table Lookup( 14> LookupGroupId tinyint, 15> LookupId smallint, 16> LookupValue sql_variant) 17> Go 1> 2> 3> Insert Lookup (LookupGroupId, LookupId, LookupValue) 4> Values (2, 34, "VAR") 5> Insert Lookup (LookupGroupId, LookupId, LookupValue) 6> Values (3, 22, 2000) 7> Insert Lookup (LookupGroupId, LookupId, LookupValue) 8> Values (4, 16, "1/12/2000") 9> Insert Lookup (LookupGroupId, LookupId, LookupValue) 10> Values (4, 11, $50000) 11> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> drop table Lookup; 3> GO 1> 2> 3></source>


sql_variant type column

   <source lang="sql">

26> CREATE TABLE variant2 27> (a sql_variant, b sql_variant ) 28> GO 1> INSERT INTO variant2 2> VALUES (CAST (111 as int), CAST(222 as money )) 3> GO (1 rows affected) 1> INSERT INTO variant2 2> VALUES (CAST (333 as int), CAST(444 as char(3) )) 3> GO (1 rows affected) 1> 2> SELECT * 3> FROM variant2 4> WHERE a > b 5> 6> GO a










b



















































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







































- 333










444











(1 rows affected) 1> 2> SELECT * 3> FROM variant2 4> WHERE a < b 5> GO a










b



















































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







































- 111










222











(1 rows affected) 1> 2> drop table variant2; 3> GO 1></source>


The creation of the table, with a column of the SQL_VARIANT type.

   <source lang="sql">

5> CREATE TABLE Item_Attributes ( 6> item_id INT NOT NULL, 7> attribute NVARCHAR(30) NOT NULL, 8> value SQL_VARIANT NOT NULL, 9> PRIMARY KEY (item_id, attribute)) 10> GO 1> 2> drop table Item_Attributes; 3> GO</source>