Are numeric indexes more effecient?

greenspun.com : LUSENET : SQL Server Database Administration : One Thread

Is there any truth in the idea that using an index on a column holding numeric data is faster than using an index on a column holding text (or other) data? If so why?

Thanks Maureen

-- Anonymous, July 26, 1999

Answers

Maureen,

There is not enough truth to the idea to make it worth pursuing. Disk access is typically the limiting factor in database performance. Trying to save a few CPU cycles when you're doing so much disk access is like Bill Gates stooping over to pick up a penny.

You should create and use a well thought out data model and base the data type on what the data is rather than pervert the model for some minimal efficiency improvement. In particular, you should keep in mind that there are many nominally numeric fields that should actually be thought of as charcter fields. An example is the zip code. Even though it is a 5 digit number, you would never think of adding up a column of zip codes. So zip codes should be a character data type.

Hope this helps,

Eric

-- Anonymous, July 26, 1999


However it might be worth considering that the numeric index could take up less space, and therefore require fewer page reads. Numeric keys are often used for this reason.

-- Anonymous, July 27, 1999

Moderation questions? read the FAQ