database datatype performance: int or string

I'm storing phone country codes. They ran开发者_JAVA技巧ge from 1 to about 300. What's going to be more performant for datatype: int or string? I'm using SQL server 2008 and linq-to-sql.

Thanks.

Note: Whoa, really wierd - you asked about phone codes and I wrote about ZIP codes. Sorry about that! I think the advice still stands though...

Original answer: Performance will most likely be negligible - assign the proper type based on what the data is. ZIP codes, while numeric (in the US at least), aren't numbers - they should be stored as strings.

It is very important to understand the semantic nature of the data you are storing. Once you understand what something is then you can begin to reason about how it should be stored. I am assuming that currently you are storing only the first 5 numbers of a US postal code (like this: 12345).

If you were to store this data as a number this would work. Then imagine that your manager tells you that there is a new requirement that the app you are building will start to collect ZIP codes in the ZIP+4 format (which looks like this: 12345-6789). Now you are stuck with a nasty refactoring that involves either changing the type in the database to varchar(10) or doing some crazy voodoo in your app to strip out the dash when you save the ZIP code and then add it back in for display later.

If you're really worried about space and performance then you could use a smallint (which equates to a int16). This will mean that the data will only take 2 bytes of storage (and 2 bytes in memory).

Given an option where I know the datatype will always be integer, I'll go for integer albeit smaller size - smallint / tinyint (depending on the required range).

I don't expect much difference in performance though.

How are you going to be using them and do any have leading zeros?

If you are going to be combining with phone numbers that are usually stored as string, you want to store them as a string as well or you will waste processing power converting them in every query.

If you aren't planning on doing math or joins with it, it is problably a bad idea to store as a number. Your data set is likely so small and the strings so tiny (300 is the max value) that using an int would probably gain you nothing in a join either.

Country codes are strings (notwithstanding that they use only the characters 0..9) and should be stored as such.

They are so few that you don't need to be concerned about this, though it would be simpler to apply a check constraint with an integer type.

my rule of thumb has always been.. do I need an average? For example, you can store a zip code as integer, but are you ever going to need the average zip code? Probably not. As such, store as char.. unless you may need more than 5 characters, in which case store as varchar.