[RESOLVED] SQL Server 2000 - Actual Field Length vs Field Length Returned From Syscolumns Table

[Hack]

I've done some Googling, but can't find an answer to this. It is just a curiousity question.

I have the following fields in a table. All are nvarchar except Next_Status which is datetime.

When I run this from Query Analyser

SELECT [name] as Field_Name, [length] as Field_Length 
FROM syscolumns
WHERE id = (SELECT id 
FROM sysobjects
WHERE type = 'U'
AND [NAME] = 'MyTableName')

, I get the following

• TICKET_NO 20
• DATE_IDENTIFIED 40
• IDENTIFIED_BY 100
• SYSTEM_NAME 200
• BUS_ISSUE_STATE 16
• GPRS_IMPACTED_STATUS 1000
• SYS_FUNC_AVAIL 1000
• PRIORITY_IMPACT 150
• ISSUE_STATUS 16
• DEFECT_TICKET_NO 200
• NEXT_STATUS 8
• RESPONSIBLE 200
• ECD 100
• ACD 100
• ISSUE_NO 10

But, the actual field lengths are half those numbers. In reality, the field lengths are

• TICKET_NO 10
• DATE_IDENTIFIED 20
• IDENTIFIED_BY 50
• SYSTEM_NAME 100
• BUS_ISSUE_STATE 8
• GPRS_IMPACTED_STATUS 500
• SYS_FUNC_AVAIL 500
• PRIORITY_IMPACT 75
• ISSUE_STATUS 8
• DEFECT_TICKET_NO 100
• NEXT_STATUS 8
• RESPONSIBLE 100
• ECD 50
• ACD 50
• ISSUE_NO 5

I'm just wondering why the syscolumns table is doubling the actual length.

[si_the_geek]

the N of NVarChar is the important part.

What it means is that the field is capable of storing unicode, which takes 2 bytes per character - so a field that can store 10 characters will take 20 bytes.

[Hack]

I knew nvarchar permitted the storage of unicode but it never really dawned on me what affect this might have on physical storage.

To be honest, I'm not even sure why I use nvarchar for text fields. I have developed a mere handful of applications that actually required unicode storage, but I ashamedly confess to using nvarchar all the time. I suspect that, many years ago, someone told me that is what I should use and I've been doing so ever since without giving it much thought.

Maybe you can't teach an old dog new tricks, but sometimes you can remind that old dog of tricks he (allegedly) already knows.

Thanks si!