sql server max_lenght returns double the actual size

  • Hi all,

    I am retrieving column properties of a table with the following query

    select

    c.name as ColumnName, type_name(c.system_type_id) as ColumnType,

    c.is_nullable as AllowNull,

    object_name(fkc.referenced_object_id) as RefTable,

    col_name(fkc.referenced_object_id,fkc.referenced_column_id) as RefColumn,

    OBJECTPROPERTY(OBJECT_ID(kcu.constraint_name), 'IsPrimaryKey') as IsPK,

    max_length as Length

    from sys.tables as t

    inner join sys.columns c on c.object_id=t.object_id

    left outer join sys.foreign_key_columns fkc on fkc.parent_column_id=c.column_id and fkc.parent_object_id=c.object_id

    left outer join INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu on kcu.COLUMN_NAME=c.name and kcu.TABLE_NAME=object_name(c.object_id)

    where c.object_id=OBJECT_ID('City')

    which returns CityCode column's length as 50.

    The thing is the actual length of the CityCode column in the table is 25 (nvarchar(25)).

    I checked it with other tables and max_length property always returns the double of the actual size. I dont need the other columns, just columns with nvarchar

    I read the documentation of max_length and it says:

    Maximum length (in bytes) of the column.

    -1 = Column data type is varchar(max), nvarchar(max), varbinary(max), or xml.

    For text columns, the max_length value will be 16 or the value set by sp_tableoption 'text in row'.

    Is dividing the max_length by 2 to retrieve original value reliable, or is there another way?

    Thanks in advance.

  • For unicode you need to divide by 2, as each unicode character takes 2 bytes. That's why a nvarchar(25) takes a max of 50 bytes (as the section you quoted says, the max length is the max number of bytes, not maximum number of characters). For non-unicode strings and all other data types, the max length doesn't need to be divided by anything.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Got it. Thanks for the reply.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply