SQL Server Performance Forum – Threads Archive

Kinder garden question about datatype

Hi all, Suppose I know that the maximum length for my customerID field will not be more than 10 characters (95% of times this will be numeric digits like 1000127, while 5% of the times this may be alphanumeric like 1001MT56). Given this scenario should I use char(10) as the datatype for my customerID field or should I use varchar(10). Can you kindly share your expert advise from a performance aspect. BOL has a lot of information but I could not the exact answer to this "kiddy" questions and thus ended up posting this question here.

If the length is fixed to 10, then use char otherwise use varchar. Your sample data tells you that you need to use varchar(10) Madhivanan Failing to plan is Planning to fail

I would also go with CHAR(10). The difference should be really marginal. When you use VARCHAR(10) that column will need to have an entry in the column offset array, which makes up 2 bytes to storage, IIRC. Add this to the 7 bytes of ‘1000127’ and you’ll see, that it’s almost the same in terms of storage requirements. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

… unless the table will contain rows with most of customerIDs < 1000000. In that case use varchar.

Thanks everyone for the replies <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> For this one, I’ll go with char(10). BTW a somewhat related question: Suppose I was to decide between char(20), varchar(20) and nvarchar(20). Which one would you recommend again performance being the main consideration).

Generally, if the data being stored there varies greatly in length, you would go for VARCHAR. If it doesn’t (like in your example above), you would go for CHAR. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

Nvarchar (nchar) takes more space then varchar so the answer is obvious. You use nvarchar when you have to, varchar is used (or char) whenever possible.

If you need to support unicode, but you also decide that you prefer a fixed length column, then you can of course use NCHAR.

Thanks everyone. This helps.

]]>