Which is fater to search NUMERIC or ALPHABET

[JeffreyO]

Hi,

im a newbie, can you please help me to choose what data type i have to use as a search key. numeric alphabet or alpha numeric.

what are the advantages and disadvantages of using that data type?


thanks in advance.

[iburyak]

Numeric is usually a search key. Numeric datatype if it is for example an int takes 4b per record and character datatype depend on size of a character string.
Alphanumeric is the same as alphabet because numbers in this case treated as characters.
Integer datatype can be up to 2147483647 and to store the same in character datatype you have declare column at least 10 characters long.

Search on smaller columns is faster due to a number of data pages have to be read to get to a data. I can come up with more reasons, like generating unique ID with alphanumeric is much harder, on why numeric is better but I proved my point and it is good enough.

[JeffreyO]

Numeric is usually a search key. Numeric datatype if it is for example an int takes 4b per record and character datatype depend on size of a character string.
Alphanumeric is the same as alphabet because numbers in this case treated as characters.
Integer datatype can be up to 2147483647 and to store the same in character datatype you have declare column at least 10 characters long.

Search on smaller columns is faster due to a number of data pages have to be read to get to a data. I can come up with more reasons, like generating unique ID with alphanumeric is much harder, on why numeric is better but I proved my point and it is good enough.

so you recommend numeric for faster searching. what about if there are alot of records which we cant delete. if i use alphanumeric it will increase the number of records that we can put right? will the difference in search time increase as we put records or will it be the same?



if i use alphanumeric will the difference in search time be big?

[iburyak]

As I said numeric is recommended practice.
Even thou with increased number of records search slow down but with proper indexes it is almost unnoticeable in some cases it is fraction of millisecond. You have to have several millions records to notice slowness.