SQL: Why does SYS.COLUMNS show my columns having different properties than what I set

Question

I added a table to a database with the following column names and datatypes

"ID" bigInt, "Description" nchar(11)

If I check the design view of the table, the datatypes of the columns are as I set them.

If I write a query that will get the columns in that table from SYS.COLUMNS the column properties are different e.g.(the max_length of description is 22, not 11)

Can someone explain why that is?

I am using Microsoft SQL Server Enterprise Edition (64-bit) if that is relevant.

Answer 1

The column max_length in sys.columns show the max length in bytes. nchar is double byte so 11 chars take 22 bytes.

If you want the number of characters you could use INFORMATION_SCHEMA.COLUMNS

select COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'YourTable'

Or you could use the function COLUMNPROPERTY

select name,
       columnproperty(object_id, name, 'charmaxlen') as charmaxlen
from sys.columns
where object_name(object_id) = 'YourTable'