CODEWITHSUNDEEP
sqlsql-serverindexing
Jeffrey Kramer
Jeffrey Kramer

Reputation: 1345

Index for table in SQL Server 2012

I had a question on indexes. I have a table like this:

id BIGINT PRIMARY KEY NOT NULL,
cust_id VARCHAR(8) NOT NULL,
dt DATE NOT NULL,
sale_type VARCHAR(10) NOT NULL,
sale_type_sub VARCHAR(40),
amount DOUBLE PRECISION NOT NULL

The table has several million rows. Assuming that queries will often filter results by date ranges, sale types, amounts above and below certain values, and that joins will occur on cust_id... what do you all think is the ideal index structure?

I wasn't sure if a clustered index would be best, or individual indexes on each column? Both?

Upvotes: 2

Views: 175

Answers (2)

marc_s
marc_s

Reputation: 754538

Any serious table in SQL Server should always have a well-chosen, good clustering key - it makes so many things faster and more efficient. From you table structure, I'd use the ID as the clustering key.

Next, you say joins occur on cust_id - so I would put an index on cust_id. This speeds up joins in general and is a generally accepted recommendation.

Next, it really depends on your queries. Are they all using the same columns in their WHERE clauses? Or do you get queries that use dt, and others that use sale_type separately?

The point is: the fewer indices the better - so if ever possible, I'd try to find one compound index that covers all your needs. But if you have an index on three columns (e.g. on (sale_type, dt, amount), then that index can be used for queries

  • using all three columns in the WHERE clause
  • using sale_type and dt in the WHERE clause
  • using only sale_type in the WHERE clause

but it could NOT be used for queries that use dt or amount alone. A compound index always requires you to use the n left-most columns in the index definition - otherwise it cannot be used.

So my recommendation would be:

  • define the clustering key on ID

  • define a nonclustered index on cust_id for the JOINs

  • examine your system to see what other queries you have - what criteria is being used for selection, how often do those queries execute? Don't over-optimize a query that's executed once a month - but do spend time on those that are executed dozens of times every hour.

Add one index at a time - let the system run for a bit - do you measure an improvement in query times? Does it feel faster? If so: leave that index. If not: drop it again. Iterate until you're happy with the overall system performance.

Upvotes: 4

Prashant Kumar
Prashant Kumar

Reputation: 249

The best way to find indexes for your table is sql server profiler.

Upvotes: -3

Related Questions