Sql-server – SQL Server Storage sql_variant

database-internalssql-server-2008-r2

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?

Best Answer

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.
Related Question