CODEWITHSUNDEEP
sql-serverindexing
Matthew
Matthew

Reputation: 2280

Index a SQL Server table for searching on multiple columns

I have a table with 15 columns - all of type int, nvarchar or datetime. I want to be able to find rows that match values in 6 of the nvarchar columns and 1 of the datetime columns. A stored procedure will do the select and return the rows. My question is how should I create an index so the select will be optimal? One index on 7 columns or 7 separate indexes on each column? Does it matter? Does the order of WHERE clauses in the select matter to the order of indexed columns?

edit

It may help to know that the purpose of the select is to find 1 or 0 records. The 6 columns are needed to define a unique record and the datetime column is used to find that record in the past X days.

edit 2

Currently, there is no data in the table. The purpose of the table is to determine whether certain action has been performed for a certain client. Before each action, I will look for a record in the table with those 7 column values. If there is no record, I will perform the action and then insert a record. If there is a record, I will skip to the next action and repeat the process.

Given that there will be lots of inserts to this table the indexes will be rebuilding frequently.

Thanks!

Upvotes: 2

Views: 2758

Answers (1)

TToni
TToni

Reputation: 9391

Edit 2: Since you clarified your question I can give a more specific answer.

You want one index with the six columns included.

I'm still a bit confused regarding the datetime column. You say that the six columns define unique entries. If they are unique for the whole table you need the datetime value neither in the index nor in the query. If they are unique only for a given date, you need to include it in both the index (total of seven columns) and the query.

The six/seven-column index is probably not feasible as a clustered index (especially if you want to create other indexes as well), so if you don't have a clustered index already, create one, preferably on an auto-id-column. A clustered index should be as small as possible, unique and always rising, because it affects the way in which the table and the other indexes are stored.

The six/seven-column index should be marked as unique and not contain any additional columns. If possible, the columns should be ordered in a way that the index produces rising values on each insert, because that will limit or in the best case eliminate fragmentation, thus increasing I/O performance.

Based on your description this is the best advice I can give you.

Upvotes: 2

Related Questions