SQL 2005 database - select by id or select by title?

Question

I'm creating some url rewriting for asp.net. Now I am tobbing if I should include the id in url or just the title. Do you guys know if it's a significant performance hit to lookup an item by title instead of id?

Answer 1

If you can, lookup by the primary key, which is probably ID in your case.

However, if your titles are unique and you have an index on Title, the performance difference should be minimal.

Edit : Since is URLwriting, the title probably has better SEO mileage, FWIW

Answer 2

It depends on how many rows you have in your table and many other factors but generally if you have an index on your title column it shouldn't be too much of a performance hit. Ultimately the only real way to see if it's a problem in your scenario is to try it and run some tests.

Answer 3

Hi,

The most important factor is to make sure you have have index on the column you are attempting to do the lookup on. So another way to say it is put an index on the columns in you where clause.

Enjoy!

Answer 4

That depends.

If the Id is the used for clustered id (default form PK) so the difference can be significant,

because in simple words, If you are using a clustered index to retrieve the data you do less operation.

The numeric type vs character. That also depend of the size that You have declared for this type. NUMERIC(20) is slower than VARCHAR(5).