Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sql server max_lenght returns double the actual size Expand / Collapse
Author
Message
Posted Sunday, August 12, 2012 7:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 17, 2012 9:14 AM
Points: 4, Visits: 10
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.
Post #1343887
Posted Sunday, August 12, 2012 7:28 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:54 AM
Points: 42,768, Visits: 35,867
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 2008, MVP
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

Post #1343890
Posted Wednesday, August 15, 2012 12:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 17, 2012 9:14 AM
Points: 4, Visits: 10
Got it. Thanks for the reply.
Post #1345486
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse