5

I have a large table (SQL Server 2008) with almost 3 million rows. It currently has a clustered index on it's primary key, and a non-clustered index on its foreign key.

Almost all the queries on the table are reads based on the foreign key.

  1. Should I change the index on the table to be against the foreign key?
  2. What is the best process for doing this?

Thanks

Brock Adams
  • 90,639
  • 22
  • 233
  • 295
cman77
  • 1,753
  • 1
  • 22
  • 48
  • How many values are in the FK column? There's little benefit in changing the index depending on how many - IE: two would be worthless to index. – OMG Ponies Apr 04 '11 at 01:43
  • I'm not sure I understand the question, but are you asking how many potential FK values are there? If so, around 16,000.... – cman77 Apr 04 '11 at 01:54
  • @OMGPonies, wouldn't setting the clustered index on the foreign key values help his SQL queries? His query would be faster because it wouldn't have to do a bookmark lookup for the data of the row. – Micky McQuade Apr 04 '11 at 02:04
  • @Micky McQuade - that was my understanding, problem is the table is so large that it's quite difficult to make any structural changes to it.... – cman77 Apr 04 '11 at 02:06
  • @Micky McQuade: If `SELECT DISTINCT fk_column FROM YOUR_TABLE` returns *few values*, an index (clustered or not) won't help. Because there's not enough to distinguish between records. Potential values doesn't matter. – OMG Ponies Apr 04 '11 at 02:22
  • Got it - that returns over 10,000 values – cman77 Apr 04 '11 at 02:38

1 Answers1

2

Without knowing more about your situation, I think that changing the non-clustered index to be a non-clustered, covering index would be the best solution.

http://www.simple-talk.com/sql/learn-sql-server/using-covering-indexes-to-improve-query-performance/

ta.speot.is
  • 26,914
  • 8
  • 68
  • 96