Varchar Minimum Lengths - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Varchar Minimum Lengths

Author

Topic

RichardSteele
Posting Yak Master

160 Posts

Posted - 2003-03-07 : 20:54:40

Is there any reason to use a varchar(5) instead of char(5) for a datatype? In other words, do varchar need more space due to overhead when the char lengths are small? If so, when should one use the char type. If not, why should one use the char type at all?

Thanks in advance.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-03-07 : 21:26:30

Depends on your needs.

If you need to support multiple languages or use Unicode characters then you will want to use nvarchar, nchar instead of varchar and char.

If you string lengths are going to be the same most of the time use char or nchar.

If your string lengths are going to be unpredictable use nvarchar or varchar.

Also the max length for nvarchar and nchar is 4000. The size would be 4000*2bytes = 8000bytes

Max length for varchar and char is 8000. The size would be 8000*1byte = 8000bytes

Depending on ANSI_PADDING and the columns definition settings the varchar,nvarchar,char,nchar may or may not pad null or truncate nulls.

I would suggest reading BOL under topics for more
Using char and varchar Data and
Using Unicode Data

Edited by - ValterBorges on 03/07/2003 21:32:23

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-03-08 : 09:01:23

There is one annoying little gotcha with char: if you have data that doesn't fill out to the full length, you get extra spaces padded at the end, and it is ANNOYING when you write queries for values YOU KNOW ARE THERE, but you didn't include the extra spaces in the search term. If you have logical codes that never vary in length, like U.S. zip codes or state codes, then it's no problem, but if they vary at all and you want to keep your sanity, I'd suggest using varchar. The overhead is really quite small.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources