SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 database performance and autocompletes
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

romeo40777
Starting Member

USA
3 Posts

Posted - 02/24/2014 :  14:19:46  Show Profile  Reply with Quote
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

Sweden
30112 Posts

Posted - 02/24/2014 :  14:30:01  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

USA
3 Posts

Posted - 02/24/2014 :  14:53:14  Show Profile  Reply with Quote
oh ok thanks i will do that.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000