SQL Server/T-SQL Tutorial/Data Types/SQL VARIANT — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
|
Текущая версия на 13:25, 26 мая 2010
Содержание
- 1 Comparing SQL_VARIANTs
- 2 Insert different type of values into a sql_variant column
- 3 Select case and cast SQL_VARIANT
- 4 sql_variant data type is designed to allow a single variable, column, or parameter to store values in different data types.
- 5 sql_variant type column
- 6 The creation of the table, with a column of the SQL_VARIANT type.
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>