SQL Server Performance Forum – Threads Archive

char or int?

I have one table where people enter their address information. I have the zip field set to char(5). I will be performing a lot of searches by zip code and want to put an index on that field. Would I be better off changing the field to an int? And if so should I keep two fields – one of each type or just do a conversion on selects? Thanks Rob Mills

---

You can create the index on char column…
I don’t see any reason to convert the column or adding another column with int datatype…
Mohammed U.

---

Ok thanks. I was just considering it since I’ve always heard that searching on numeric fields is faster than character fields. Rob Mills

---

Basically you are correct, though I doubt you’ll experience significant differences between searching an indexed INT and an indexed CHAR(5) column anyway. However, there is a logical aspect in this. I don’t know if it applies to you too, but here in my place there are certain zip codes that contain a leading 0. By definition this 0 is removed when stored in a numeric column. This might not only represent a loss of information, but certainly makes the task of verifying the validity of the data more complex as you cannot use a simple CHECK constraint such CHECK(zip LIKE ‘[0-9][0-9][0-9][0-9][0-9]’). —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

---

In addition to Frank’s point, there are countries where the zipcode contains characters as well as numbers. (As a point of interest, while most countries have a nation-wide zipcode, there are also countries with regional zipcodes.) So if your database needs to cover multiple countries, alphanumeric is the way to go.

---

I can back Frank up: the town where I grew up has zip code 01033 <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />. As an INT this is 1033 — invalid.<br /><br />Most zip codes now are also zip + 4: 12345-1234, so I use a varchar(10) or char(10), both of which can be indexed.

---

Thanks for all your input. Currently I have it setup to split the first 5 digits into one field and the other 4 in another because I don’t see any use for searching on the last 4 right now but I do want it for displaying. Rob Mills

---

Eventhough we can put indexes on CHAR cols,which one results in faster search CHAR or INT

---

INT. But perhaps you do not have a choice.

---

]]>