Query.sql

/*
https://connect.microsoft.com/SQLServer/feedback/details/683411
*/

IF NOT EXISTS ( SELECT TOP 1 NULL
                FROM   INFORMATION_SCHEMA.TABLES
                WHERE  TABLE_NAME = 'SinglePrimaryKey' )
BEGIN
  CREATE TABLE SinglePrimaryKey
  (
    PrimaryId INT NOT NULL PRIMARY KEY
  );

  CREATE TABLE SingleForeignKey
  (
    PrimaryId INT NOT NULL PRIMARY KEY REFERENCES SinglePrimaryKey(PrimaryId)
  );

  CREATE TABLE CompositePrimaryKey
  (
    CompositeId INT NOT NULL,
    PrimaryId INT NOT NULL,
    CONSTRAINT PK_CompositePrimaryKey PRIMARY KEY (CompositeId, PrimaryId)
  );

  CREATE TABLE CompositeForeignKey
  (
    CompositeId INT NOT NULL,
    PrimaryId INT NOT NULL,
    CONSTRAINT PK_CompositeForeignKey PRIMARY KEY(CompositeId, PrimaryId),
    CONSTRAINT FK_CompositeForeignKey_CompositePrimaryKey
      FOREIGN KEY (CompositeId, PrimaryId) REFERENCES CompositePrimaryKey(CompositeId, PrimaryId)
  );
END; 

/* Execution plan => no join */
SELECT COUNT(*)
FROM   SingleForeignKey
       INNER JOIN SinglePrimaryKey
         ON SingleForeignKey.PrimaryId = SinglePrimaryKey.PrimaryId;

/* Execution plan => redundant join */
SELECT COUNT(*)
FROM   CompositeForeignKey
       INNER JOIN CompositePrimaryKey
         ON CompositeForeignKey.CompositeId = CompositePrimaryKey.CompositeId
            AND CompositeForeignKey.PrimaryId = CompositePrimaryKey.PrimaryId;