Does an index already cover a clustered primary key?

Let's say I have a table like this:

CREATE TABLE t(
  [guid] [uniqueidentifier] NOT NULL,
  [category] [nvarchar](400)
  {,...other columns}
  )

Where guid is my primary key, and has a clustered index.

Now, I want an index that covers both category and guid, because I'm rolling up some other stuf开发者_运维技巧f related to t by category, and I want to avoid including the t table itself.

Is it sufficient to create index covering category, or do I need to include guid as well?

I would expect SQL Server indexes to point directly to page offsets in t rather than simply referring to a guid primary key value, which means I would need to explicitly include the PK column to avoid hitting t. Is this the case?

Actually your assumption is wrong - all SQL Server non-clustered indices do include the clustering key (single or multiple columns) and do not point directly at some physical page.

This prevents SQL Server from having to reorganize and update lots of index entries when a page needs to be split in two or relocated. So if you are seeking in a non-clustered index and you find a value, then you have the clustering key and SQL Server will need to do a "bookmark lookup" (or key lookup) to retrieve the actual data page (the leaf page in the clustering index) to get the whole set of data belonging to a single row.

That said - if you ever have a situation where it depends on the ordering of the key columns, then you still might need to create an index specifically on (guid, category) - of course, in that case, SQL Server is smart enough to figure out that the clustering key column is already in the index and won't be adding it one more time.

The fact that the clustering key column(s) are inlcuded in every single non-clustered index is another strong reason why your clustering keys should be narrow, static and unique. Making them too wide (anything beyond 8 byte) is a sure recipe for bloat and slow-down.

Differing slightly to marc_s' answer.

A covering index on (category, guid) will have a different sort on GUID to the primary key sort. Therefore, guid may appear twice in the index because it is in the key column list and the pointer to the clustered index.

If you INCLUDEd (as a non-key column) guid SQL Server won't add it again.

I can't test the key column thing just now, but I have verified the INCLUDE one before on SQL Server 2005.