Which to choose Number or String | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Which to choose Number or String

I have table which contains city as one column.This table get lot of select
query statement and each select contain city column (in where clause).
Let i store the city as integer .What i want to know how much it speed up the
select if table has more than 50,000 rows.length of string can be maximum 20 characters.
Plz totally ignore the other complications arise in other applications.
Thanks Vijay

This would make up for a test scenario that you can easily build up yourself. Copy the production db to a dev box, copy the table, change one table’s column to INT and then stress it and compare. With only 50,000 rows I doubt you will see a vast improve in performance. —
Frank Kalis
Microsoft SQL Server MVP
Heute schon gebloggt?http://www.insidesql.de/blogs

What about creating a test system with two tables containing a proper amount of data, one with the integer code and a lookup to the reference table, and one with the city name, BOTH indexed. Now compare the results and report back – always interesting to see such comparisons.