4
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?
0

1 Answer 1

8

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.