CODEWITHSUNDEEP
sql-serverperformancecharvarchar
macca1
macca1

Reputation: 9681

SQL Better performance: char(10) and trim or varchar(10)

I have a database that uses codes. Each code can be anywhere from two characters to ten characters long.

In MS SQL Server, is it better for performance to use char(10) for these codes and RTRIM them as they come in, or should I use varchar(10) and not have to worry about trimming the extra whitespace? I need to get rid of the whitespace because the codes will then be used in application logic for comparisons and what not.

As for the average code length, hard to tell exactly. Assume all codes are a random length between one and ten. Edit: A rough estimation is about 4.7 characters for the average length of a code.

Upvotes: 1

Views: 4448

Answers (5)

Remus Rusanu
Remus Rusanu

Reputation: 294407

As a general rule, always favor smaller storage over extra CPU. Because the driving factor of database performance is always IO and smaller data records means more records per page and this in turn means fewer IO requests. The extra CPU involved in handling the variable length is not going to be a factor. Historically, in the dark ages of '80s and even in the '90s it may have been a measurable factor, but today is just noise. Because the CPU and memory access have increased tremendously, but the IO speed has stayed pretty much constant. That's why 'old books' advice does not apply today. Unless you have a constant field like char(2) or similar, just use varchar, always.

Upvotes: 3

codingguy3000
codingguy3000

Reputation: 2835

I'm confident that you wouldn't be able to tell a speed difference between the two.

Upvotes: 2

Gabriel Magana
Gabriel Magana

Reputation: 4536

Your requirements are a textbook definition of someone who needs to use varchar.

If you want to worry about performance, worry about DB design and writing good SQL. Char vs VarChar internals are well-optimized by the DB vendors.

Upvotes: 1

a1ex07
a1ex07

Reputation: 37384

In one old book I read that in general char is a better choice when for the most of the records the real string length is at least 60% of maximum; in your example - if more than half of all records have length 6 or greater. Otherwise, use varchar.

Upvotes: 0

gbn
gbn

Reputation: 432431

I'd vote for varchar.

I say varchar to avoid the TRIM which would invalidate index usage (unless you use a computed column etc which defeats the purpose, no?).

Otherwise at length 10, it would be 50/50 but TRIM tips the balance towards varchar and wins out over the fixed length benefit

Upvotes: 6

Related Questions