CODEWITHSUNDEEP
sqlsql-servert-sql
user3032140
user3032140

Reputation: 15

Which columns to put index in the below query in MS SQL Server

I have a table with employee_name,state,city and zip. I have to join using separate city, state and zip table and one table with all city,state and zip columns.

So which columns should I create index on: city,state,zip separately or a combined index on city,state and zip.

This is a theoretical question so please help me understand the indexes and the performance issues.

Upvotes: 0

Views: 44

Answers (1)

ThomasMcLeod
ThomasMcLeod

Reputation: 7769

If I understand your question, what you need is a numerical primary key column on the City State Zip table. Then the table with employee_name has a column with the appropriate key from from the other table. The City, State Zip table of course has an index on the primary key column. You may also want an index on the employee_name column.

Although it's possible, for efficiency purposes one normally would not throw an index across three text columns. That would make for a rather large index. So we create what is called a surrogate key. In this case, as I suggested, the surrogate key should be an simple integer. Three text columns, City, State, Zip together form what is called the natural key. But this natural keys actually contains redundant information that increases its size. For example, their is a dependency between zip code and state.

Upvotes: 1

Related Questions