Search code examples
t-sqlnvarchar

TSQL - Why sysname is created when I create nVarChar column?

I have a table in my tsql datatable:

CREATE TABLE dbo.Test
(
     Col nVarChar (50) null
)
GO

And then I executed query:

Select 
    c.name As Name, ty.name as Type, c.max_length As MaxLenght, c.precision As Precision, c.scale As Scale, c.is_nullable As IsNullable, *
From
    sys.schemas s
    inner join sys.tables t on s.schema_id = t.schema_id
    inner join sys.columns c on t.object_id = c.object_id
    inner join sys.types ty on ty.system_type_id = c.system_type_id
Where
    s.name LIKE 'dbo' AND t.name LIKE 'Test'

The question is... Why there are Two Rows?!

Solution

  • Check this:

    SELECT ROW_NUMBER() OVER(PARTITION BY system_type_id ORDER BY system_type_id)
      , *
FROM sys.types;

    Check the first column for values >1...

    There are few types mapping to the same system_type_id. Some names are just an alias for something else...

    UPDATE

    This question addresses the same issue...