Are primary keys available in nonclustered indices?

Question

In an attempt to make the best index choices for my database, I've noticed some particular behaviour which I'd like to address.

Observe the following table and corresponding index (SQL Server 2005):

CREATE TABLE demo
(
 id INT PRIMARY KEY IDENTITY,
 name NVARCHAR(50) NOT NULL,
 password BINARY(20) NOT NULL
);

CREATE NONCLUSTERED INDEX idx_demo_foo ON demo ( name, password );

In this case, if I perform the following query...

SELECT id FROM demo
WHERE name = @0
AND   password = @1;

... only a nonclustered index seek occurs. This strikes me as odd because I didn't explicitly add id to the nonclustered index.

Answer 1

The clustered index key is always included in a nonclustered index. The clustered index key is the row locator for the table, and every index must contain the row locator in order to match the index rows with table rows.

Answer 2

Hi

index seek normally occurs when the columns in where clause are same as the columns in index (sometimes same order).. this is nothing to do with primary keys..

Cheers

Ramesh Vel

Answer 3

A NCIX must include the Clustered Index keys, so that the system knows how to reach the underlying rows in the table itself. So if your Primary Key has a Clustered Index behind it (which by default it would), then you're in luck.

Of course, if you change the Clustered Index, or make your table into a heap, then you'll need lookups to get to the Primary Key.

Edit: I would recommend taking the password field out of the index keys, and making it an included column. Surely you just look up the entry by username and then check that the password matches, rather than having a number of passwords for each username.

create index ixBlah on demo (name) include (password);

Rob