Skip to content

Instantly share code, notes, and snippets.

@wjkhappy14
Forked from taspeotis/Query.sql
Created April 3, 2017 10:07
Show Gist options
  • Save wjkhappy14/98fdb6ba5084724b9513cb71366d558d to your computer and use it in GitHub Desktop.
Save wjkhappy14/98fdb6ba5084724b9513cb71366d558d to your computer and use it in GitHub Desktop.
/*
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment