August 12, 2012 at 7:01 am
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.
August 12, 2012 at 7:28 am
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
August 15, 2012 at 12:56 pm
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