Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 database performance and autocompletes

Author  Topic 

romeo40777
Starting Member

3 Posts

Posted - 2014-02-24 : 14:19:46
I am new to database performance issues and have a question about best practices. I have a database table that has over 10,000 pairs of cities and states such as

Dallas, Texas
Houston, Texas
Miami, Florida

Someone told me that the best practice would be to have cities and states in different tables and reference them with a composite index. I forgot to mention that these locations are used with a Json textbox with auto-completion does this change things or should I still have separate tables and if i should leave them on same table would it be best to index both fields or just one? The way the textbox are used is similar to google searches were you type in a question and you get suggestions right below that. If a user's types in "Hou" then Houston, Texas would start to appear below textbox. The textbox is only used for searches never any inserts,deletes or updates.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-02-24 : 14:30:01
Read about normalization.

One table with column "states" and a surrogate key.
One table with column "city", and a value for the surrogate key to point to the correct state.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

romeo40777
Starting Member

3 Posts

Posted - 2014-02-24 : 14:53:14
oh ok thanks i will do that.
Go to Top of Page
   

- Advertisement -