2

I'm creating a lookup table to be used for data processing. The structure is rather simple.

The first field will be the primary key and clustered index. There will be a second field that will track the items type.

We would like an index on that second field since some (but not all queries) will be searching on that field. Those queries would also need to pull the value of the primary key.

I would like the queries that utilize the non-clustered index to not have to go to the table and pull the primary key value.

So my question is do normal SQL Server indexes have the primary key value on them or do they just have a pointer to the clustered index (for my purposes here I'm just assuming the primary key and the clustered index are coextensive)? If it only contains a pointer to disk and not the primary key value, I was thinking about making the first column (the primary key) an include column on the normal index based on the second column.

geoffrobinson
  • 1,580
  • 3
  • 15
  • 23
  • I wouldn't expect a field not defined on an index to appear in it, whether it is clustered/primary key or otherwise. If you want the non-clustered index to be covering of the primary key column, add it explicitly. – Oded Sep 27 '12 at 15:55
  • Not the *primary key value* - but the **clustering key**. Now, by default, the primary key *is* the clustering key - but it doesn't have to be that way. – marc_s Sep 27 '12 at 17:02
  • Thanks guys. For the purposes of this question, they are one and the same. And it seems the answer is yes, the value is on the regular, non-clustered index so I don't need an include column. – geoffrobinson Sep 27 '12 at 17:29

3 Answers3

8

To quote the docs for CREATE INDEX :

Nonclustered indexes always contain the clustered index columns if a clustered index is defined on the table.

AakashM
  • 62,551
  • 17
  • 151
  • 186
3

They don't necessarily have the PK columns in them, but the key columns of the clustered index. They are indeed used as a pointer to the CI row, but also usable in other types of queries.

For example an index on column A (and not explicitly on ID) can satisfy the query select A, ID from T.

usr
  • 168,620
  • 35
  • 240
  • 369
1

The key values of the index establish the order for the data. The data is stored with the index so that the data itself is ordered. With a clustered index, the pointer on the non-clustered index consists of the values that define the clustered index. Otherwise, if there is no index present on the table (heap), a scan is used in which data is searched by using a row identifier as a pointer.

Source: SQL Server 2008 Query Performance Tuning Distilled

Kermit
  • 33,827
  • 13
  • 85
  • 121