0

When I have a primary key on a column, do I also need a non-clustered index on that same column for querying purposes? Primary keys ARE indexes, aren't they?

Also, if I have an aggregate primary key on two columns, do I need to create indexes on both of those columns for querying purposes?

And, finally, if I will be commonly querying for rows specifying two columns to match, is it best to have one index that includes both columns? Or two separate indexes, one on each?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
KWallace
  • 1,570
  • 1
  • 15
  • 25
  • A primary key is a unique index, normally its a unique clustered index so no you wont need a 2nd index. If using SQL Server Managment Studio you can use the Database Tuning Advisor against your query to determine what indexes would assist. Use the query plan to see which are currenty being used – David Waterworth Aug 01 '15 at 22:49
  • https://technet.microsoft.com/en-us/library/ms191236(v=sql.105).aspx – Hamlet Hakobyan Aug 01 '15 at 22:52
  • There's some useful info here http://stackoverflow.com/questions/4444085/are-there-any-query-optimization-tools-for-sql-server on tuning – David Waterworth Aug 01 '15 at 23:00
  • 2
    The only reason I can think of that you might want a secondary index on your primary key is if the key was a composite key of multiple fields, and you have situations where the filter is made on different components of the key (remember the order of fields matters). I even support a db with a few tables where the primary key is a non-clustered key and another unique key is the clustered index on the table. It was done this way due to how data are commonly read from and written to the table. Unusual, but it can happen. – Bacon Bits Aug 02 '15 at 00:03
  • I think you are searching for a primary key with two columns combined. The cluster index is the best and you can only have one because is the physical order of the data. – Juan Aug 02 '15 at 02:17
  • @Kirby No upvote and not a single comment on my answer? What's up with that? – Tomalak Aug 03 '15 at 05:10

2 Answers2

3

When a Primary Key is created, a Clustered Index in Created automatically. If you have any JOINS or a WHERE condition on this column, the JOIN as well as the search is faster because the Engine would know the Physical location of the record you are looking for.

In your condition, I would say if you have a primary Key which is a combination of several columns and you would either SEARCH/ JOIN on individual columns you would need a Non Clustered Index..Else assigning a Primary Key will do the trick

Refer this for more information: https://www.simple-talk.com/sql/performance/tune-your-indexing-strategy-with-sql-server-dmvs/

d_luffy_de
  • 967
  • 1
  • 9
  • 24
  • The first sentence is not true unless you are using SSMS to build the PK. You can create a Non-Clustered Primary Key. This allows you to create Clustered Index on any columns you choose, bearing in mind the best practices for CIs. – GoClimbColorado Oct 11 '16 at 16:57
1

When I have a primary key on a column, do I also need a non-clustered index on that same column for querying purposes? Primary keys ARE indexes, aren't they?

A regular index is a sorted copy of one (or multiple) columns. Being sorted it allows for fast searching. If its underlying values change, it will be re-sorted accordingly, but physical table order stays the same.

A clustered index on the other hand defines physical table order. That's why you only can have one - if its values change, the entire table will be re-sorted accordingly.

Often the primary key also is the clustered index of the table. But not necessarily - the defining property of a primary key is its uniqueness.

Having a clustered and a non-clustered index over the same column is redundant and you should not do it. It increases workload during insert/update/delete, but it does nothing for query performance.

if I have an aggregate primary key on two columns, do I need to create indexes on both of those columns for querying purposes?

That depends whether you ever want to query the second column on its own. An index over (A, B) will do nothing for queries that search for B only, so having a second index over B will be necessary in this case.

Include in the index any extra columns you want to return from the query. If set up smartly, a query can be satisfied by the index alone, saving the DB engine from having to look at the table at all.

Note that this applies to non-clustered indexes. Including extra columns for queries against the clustered index is not necessary, as the clustered index is the table. It naturally contains all columns.

if I will be commonly querying for rows specifying two columns to match, is it best to have one index that includes both columns? Or two separate indexes, one on each?

Have a single index that contains both columns, the most selective (highest variance on unique values) or one that you are most likely to query on its own first, the assisting value second. Sometimes it's necessary to have it both ways - (A, B) and (B, A), it entirely depends on how the table is used.

Tomalak
  • 332,285
  • 67
  • 532
  • 628