Are primary keys available in non-clustered indexes?

Question

In an attempt to make the best index choice for my database, I noticed some specific behavior that I would like to consider.

Note the following table and associated 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 performed the following request ...

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

... only nonclustered index lookups occur. This seems odd to me because I obviously did not add id to the nonclustered index.

Answer 1

NCIX , , . , ( ), .

, , , .

: . , , , , , .

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

Rob

Answer 2

. - , , .

Answer 3

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