Why can't SQL Server optimizer use foreign key [duplicate]

Question

I have the following tables(See below for complete reproducible SQL):

ToBeTransferredTemp (ID varchar(100),type varchar(100) NOT NULL, other...)
CODES(Key1,Key2,Key3, IDSTRING varchar(100), other...)

I have the following query:

SELECT *
FROM
ToBeTransferredTemp a 
INNER JOIN codes c on a.type = c.idstring

There is an index on ToBeTransferredTemp.type and unique index on CODES.idstring. There is a foreign key constraint on ToBeTransferredTemp.type -> codes.idstring

You would think SQL Server would gather from this, that the join will produce the same number of rows as is present in ToBeTransferredTemp. But it doesn't. There are 8500 rows in ToBeTransferredTemp, and the estimated number of rows after the join is 450, and "actual number of rows" is, not surprisingly, 8500 rows.

Any idea why SQL Server misses so terribly? I've checked that the foreign key constraint is trusted by SQL Server.

Could it be that SQL Server 2017 don't take foreign key and unique constraints into account when estimating cardinality of joins?

I know I could fix this easily by just doing a LEFT OUTER JOIN, but it feels so clumsy. I'm really interested to know why SQL Server can't figure this one out.

EDIT: queryplan: https://www.brentozar.com/pastetheplan/?id=ByL54PtNB

EDIT: SQL needed to reproduce. Note that you might need to re-run the query a few times. Sometimes the values form statistics that make it seem to work

CREATE TABLE dbo.TEST_CODES
(
    key1 varchar(60) NOT NULL,
    key2 varchar(60) NOT NULL,
    key3 datetime NOT NULL,
    Filler4 varchar(100),
    IDSTRING varchar(100)
);
ALTER TABLE [dbo].[TEST_CODES] ADD  CONSTRAINT [PK_CODES_TEST] PRIMARY KEY CLUSTERED 
(
    key1 ASC,
    key2 ASC,
    key3 ASC
)

CREATE UNIQUE INDEX UQ_Codes_IDSTRING ON dbo.TEST_Codes (IDSTRING);

CREATE TABLE dbo.TEST_ToBeTransferredTemp 
(
    ID varchar(100) NOT NULL,
    [type] varchar(100) NOT NULL,
    Filler1 varchar(100) NOT NULL,
    Filler2 varchar(50) NOT NULL,
    Filler3 int NULL,

    CONSTRAINT PK_ToBeTransferredTemp_TEST PRIMARY KEY (ID),
    CONSTRAINT FK_ToBeTransferredTemp_type FOREIGN KEY ([type]) 
        REFERENCES dbo.TEST_CODES (IDSTRING)
);

CREATE NONCLUSTERED INDEX IX_type ON dbo.TEST_ToBeTransferredTemp ([type]);

INSERT INTO dbo.TEST_CODES
SELECT TOP (18139) 
    CAST(NEWID() AS varchar(60)),
    CAST(NEWID() AS varchar(60)),
    GETDATE(),
    CAST(NEWID() AS varchar(100)),
    CAST(NEWID() AS varchar(100))
FROM master.dbo.spt_values v1
    CROSS JOIN master.dbo.spt_values v2;

INSERT INTO dbo.TEST_ToBeTransferredTemp
SELECT TOP (8002)
    CAST(NEWID() AS varchar(100)),
    (SELECT TOP 1 IDSTRING FROM TEST_CODES order by newid()),
    CAST(NEWID() AS varchar(100)),
    CAST(NEWID() AS varchar(50)),
    NULL
     FROM master.dbo.spt_values v1
    CROSS JOIN master.dbo.spt_values v2;

SELECT *
FROM
TEST_ToBeTransferredTemp a 
inner join TEST_codes c on a.type = c.idstring

Answer 1

I can't reproduce your problem, which means there's likely something missing in your description (or I've missed something in your description!). If you want more detailed help with this issue, please suggest changes to this repro code that allow it to more closely match your situation.

Here's an attempt and recreating the tables and data based on your description:

There is an index on ToBeTransferredTemp.type
and unique index on CODES.idstring.
There is a foreign key constraint on ToBeTransferredTemp.type -> codes.idstring

In addition to those three pieces of information, I noticed that there is a clustered index on each of the two tables.

USE [master];
GO
DROP DATABASE IF EXISTS [245776];
CREATE DATABASE [245776];
GO

USE [245776];
GO

CREATE TABLE dbo.CODES
(
    IDSTRING varchar(100),
    Filler1 varchar(100) NOT NULL,
    Filler2 varchar(50) NOT NULL,
    Filler3 int NULL,

    CONSTRAINT PK_CODES PRIMARY KEY (IDSTRING)
);

CREATE UNIQUE INDEX UQ_Codes_IDSTRING ON dbo.Codes (IDSTRING);

CREATE TABLE dbo.ToBeTransferredTemp 
(
    ID varchar(100),
    [type] varchar(100) NOT NULL,
    Filler1 varchar(100) NOT NULL,
    Filler2 varchar(50) NOT NULL,
    Filler3 int NULL,

    CONSTRAINT PK_ToBeTransferredTemp PRIMARY KEY (ID),
    CONSTRAINT FK_ToBeTransferredTemp_type FOREIGN KEY ([type]) 
        REFERENCES dbo.CODES (IDSTRING)
);

CREATE NONCLUSTERED INDEX IX_type ON dbo.ToBeTransferredTemp ([type]);

Here are the two tables, with clustered primary keys, foreign key constraint, an NC index, and a unique index.

I have included some filler columns in order for the original key lookup plan shape to be possible.

INSERT INTO dbo.CODES
SELECT TOP (8500) 
    CAST(NEWID() AS varchar(100)),
    CAST(NEWID() AS varchar(100)),
    CAST(NEWID() AS varchar(50)),
    v1.number
FROM master.dbo.spt_values v1
    CROSS JOIN master.dbo.spt_values v2;

INSERT INTO dbo.ToBeTransferredTemp
SELECT 
    CAST(NEWID() AS varchar(100)),
    c.IDSTRING,
    CAST(NEWID() AS varchar(100)),
    CAST(NEWID() AS varchar(50)),
    NULL
FROM dbo.CODES c;

This loads exactly 8500 rows into each table.

The query as written in the OP produces a merge join plan for me:

SELECT *
FROM ToBeTransferredTemp a 
    INNER JOIN codes c
        ON a.[type] = c.idstring;

In order to get the plan shape from the OP, I have to hint the join type (INNER LOOP JOIN) and hint that the CODES table should be accessed by the unique index.

SELECT *
FROM ToBeTransferredTemp a 
    INNER LOOP JOIN codes c WITH (INDEX (UQ_Codes_IDSTRING)) 
        ON a.[type] = c.idstring;

This appears to be the right plan shape. However, the estimates are exactly right in this plan.