USE tempdb ; GO DROP TABLE tbl ; GO CREATE TABLE tbl ( i SQL_VARIANT NOT NULL ) ; GO INSERT INTO tbl (i) VALUES (1) ; GO SELECT i FROM tbl ; GO DBCC IND ('tempdb','tbl',-1) ; GO DBCC TRACEON (3604) ; -- Page dump will go the console GO DBCC PAGE ('tempdb',1,157,3) ; GO
- Record Size = 17B
- 30000400 01000001 00110038 01010000 00
- TagA = 0x30 = 1B
- TagB = 0x00 = 1B
- Null Bitmap Offset = 0x0004 = 2B
- Column Count = 0x0001 = 2B
- Null Bitmap = 0x00 = 1B
- Variable-Length Columns Count = 0x0001 = 2B
- Variable-Length Column Offset Array = 0x0011 = 2B
- This is the sql_variant encoding for integers...I guess = 0x0138 = 2B
- Our integer column = 0x00000001 = 4B
SELECT SQL_VARIANT_PROPERTY(i , 'BaseType') AS BaseType -- SYSNAME NVARCHAR(128) , SQL_VARIANT_PROPERTY(i , 'Precision') AS [Precision] -- INT , SQL_VARIANT_PROPERTY(i , 'Scale') AS Scale -- INT , SQL_VARIANT_PROPERTY(i , 'TotalBytes') AS TotalBytes -- INT , SQL_VARIANT_PROPERTY(i , 'Collation') AS [Collation] -- SYSNAME NVARCHAR(128) , SQL_VARIANT_PROPERTY(i , 'MaxLength') AS MaxLength -- INT FROM tbl ; GO
My questions:
- 0x3801...what is that
- i = 328792402 Huh? Where did this come from?
- SQL_VARIANT_PROPERTY() says I have an integer. Why does it not choose tinyint?
- Does anyone know where SQL_VARIANT_PROPERTY() can be found. Must I use the DAC to get at it?