SQL Server 2014 Index Optimization: Any Benefit with Including a Primary Key in Indexes?

Question

After executing a query in a real query plan, SQL Server 2014 shows a missing index, as shown below:

 CREATE NONCLUSTERED INDEX IX_1 ON Table1 (Column1) INCLUDE
 (PK_Column,SomeOtherColumn)

The missing index suggests including the primary key column in the index. The table is a clustered index with PK_Column.

I am confused and it seems that I do not understand the correctness of the cluster index key.

My guess: when a table has a clustered PC, all non-clustered indexes indicate a PK value. I'm right? If so, why is there no index in the query index that asks me to include the PK column in the index?

Answer 1

:
, . . .

.

. , , .

, , .

, , , . SQL Server .

, .

 CREATE NONCLUSTERED INDEX IX_1 ON Table1 (Column1) 
        INCLUDE (PK_Column, SomeOtherColumn)

, , .

select pk_column, someothercolumn 
from table 
where column1 = 'somevalue'

SQL Server , , .

, ,

create index nci_test on table(column1)

,

select pk_column, someothercolumn 
from table 
where column1 = 'somevalue'

:
.

[orderid] [int] NOT NULL Primary key,
[custid] [char](11) NOT NULL,
[empid] [int] NOT NULL,
[shipperid] [varchar](5) NOT NULL,
[orderdate] [date] NOT NULL,
[filler] [char](160) NOT NULL

.

create index onlyempid on orderstest(empid)

,

select empid,orderid,orderdate --6.3 units
from orderstest 
where empid=5

.

CREATE NONCLUSTERED INDEX empidalongwithorderiddate
ON [dbo].[orderstest] ([empid])
INCLUDE ([orderid],[orderdate])--you can drop orderid too ,it doesnt make any difference

, orderid

.

--- -------

onlyempid..

empidalongwithorderiddate

---- -------

onlyempid..

empidalongwithorderiddate

, , .

I , ,

Answer 2

. .

, , .

, PK. , (, ), . , , PK_index Table1.

, 1 PK_column. Column1, PK_value, Column1, PK_value...

, .

, , , .