``` 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: 1. 0x3801...what is that 2. i = 328792402 Huh? Where did this come from? 3. SQL_VARIANT_PROPERTY() says I have an integer. Why does it not choose tinyint? 4. Does anyone know where SQL_VARIANT_PROPERTY() can be found. Must I use the DAC to get at it?
The first 2 answers are from the SQL Server Internals Book p.278 1. `0x38` is 56 in decimal. This indicates `int` in `sys.types` (`system_type_id` column) 2. `0x01` is the version number of the `sql_variant` format (always 1 in SQL Server 2008) 3. This is the way the literal `1` is always interpreted in SQL Server. e.g. `SELECT 1 AS foo INTO NewTable` will create a new column of integer datatype. Use an explicit cast if you want it to be treated as a different datatype. 4. This is part of the product source code. You cannot view the definition. BTW: If you are looking at other datatypes as well you may encounter some additional bytes between the version number and the column value as follows. 1. `numeric`/`decimal` have 1 byte each for precision and scale. 2. `[n][var]char` have 2 bytes for max length and 4 bytes for collation id. 3. `[var]binary` have 2 bytes for max length.