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
NOT NULLand a foreign key relationship is defined to
CODES.IDSTRING, then it logically follows that all values in
ToBeTransferredTemp.typewill be present. But you suspect for some reason SQL Server don't reason on this information alone?
IDSTRINGis not the PK and more importantly it is nullable.