```
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?
Top Answer
Martin Smith (imported from SE)
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.

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.