For a parameterised query It can't just do two seeks on
WHERE A=@P1 AND B=@P2 AND C=@P3 AND D=@P5
and
WHERE A=@P1 AND B=@P2 AND C=@P4 AND D=@P5
Because if @P3 = @P4
that would incorrectly bring back duplicate rows. So it would need an operator that removed duplicates from these first.
From a quick test this end it appears to be dependant upon the size of the table whether or not you get that. In the test below 245
/246
rows is the cut off point between plans (this was also the cut off point between the index fitting all on one page and it becoming 2 leaf pages and a root page).
CREATE TABLE T(A INT,B INT,C INT,D INT)
INSERT INTO T
SELECT TOP (245) 1,2,3,5
FROM master..spt_values v1
CREATE CLUSTERED INDEX IX ON T(A, B, C, D)
SELECT index_level,page_count, record_count
FROM sys.dm_db_index_physical_stats(db_id(),object_id('T'),1,NULL, 'DETAILED')
DECLARE @C1 INT = 3,
@C2 INT = 4
SELECT * FROM T WHERE A=1 AND B=2 AND (C=@C1 OR C=@C2) AND D=5
DROP TABLE T
1 Pages / 245 rows
This plan has a seek on A=1 AND B=2
with a residual predicate on (C=@C1 OR C=@C2) AND D=5
2 leaf Pages / 246 rows
In the second plan the extra operators are responsible for removing any duplicates from @C1,@C2
first before performing the seek(s).
The seek in the second plan is actually a range seek between A=1 AND B=2 AND C > Expr1010
and A=1 AND B=2 AND C < Expr1011
with a residual predicate on D=5
. It still isn't an equality seek on all 4 columns. More information about the additional plan operators can be found here.
Adding OPTION (RECOMPILE)
does allow it to inspect the parameter values for duplicates at compile time and produces a plan with two equality seeks.
You could also achieve that with
;WITH CTE
AS (SELECT DISTINCT ( C )
FROM (VALUES (@C1),
(@C2)) V(C))
SELECT CA.*
FROM CTE
CROSS APPLY (SELECT *
FROM T
WHERE A=1 AND B=2 AND D=5 AND C = CTE.C) CA
But actually in this test case it would likely be counter productive as having two seeks into the single page index rather than one increases the logical IO.
Because a foreign key can point to a primary key or a unique constraint, and whoever created that foreign key possibly created it before the primary key existed (or they shifted the FK to point to the Unique index while they changed something else about the primary key). This is easy to repro:
CREATE TABLE dbo.MyTable(MyTableID INT NOT NULL, CONSTRAINT myx UNIQUE(MyTableID));
CREATE TABLE dbo.OtherTable1(ID INT FOREIGN KEY REFERENCES dbo.MyTable(MyTableID));
ALTER TABLE dbo.MyTable ADD CONSTRAINT PKmyx PRIMARY KEY(MyTableID);
CREATE TABLE dbo.OtherTable2(ID INT FOREIGN KEY REFERENCES dbo.MyTable(MyTableID));
In fact, both of these foreign keys will point to the first unique constraint defined on that column (myx
).
You can fix the foreign key on the other table by dropping it and re-creating it. You will need to repeat that process for any other tables that point to this column. You can find these easily:
SELECT s.name,t.name,fk.name
FROM sys.foreign_key_columns AS fkc
INNER JOIN sys.foreign_keys AS fk
ON fkc.constraint_object_id = fk.[object_id]
INNER JOIN sys.tables AS t
ON fkc.parent_object_id = t.[object_id]
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
INNER JOIN sys.columns AS c1
ON c1.[object_id] = fkc.referenced_object_id
AND c1.column_id = fkc.referenced_column_id
AND c1.name = N'MyTableID'
WHERE fkc.referenced_object_id = OBJECT_ID('dbo.MyTable');
Results:
dbo OtherTable1 FK__OtherTable1__ID__32E0915F
dbo OtherTable2 FK__OtherTable2__ID__35BCFE0A
And even generate a script to drop and re-create them (dropping the redundant unique constraint in the meantime):
DECLARE
@sql1 NVARCHAR(MAX) = N'',
@sql2 NVARCHAR(MAX) = N'ALTER TABLE dbo.MyTable DROP CONSTRAINT myx;',
@sql3 NVARCHAR(MAX) = N'';
SELECT
@sql1 += N'
ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
+ ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + ';',
@sql3 += N'
ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
+ ' ADD CONSTRAINT ' + QUOTENAME(fk.name) + ' FOREIGN KEY '
+ '(' + QUOTENAME(c2.name) + ') REFERENCES dbo.MyTable(MyTableID);'
FROM sys.foreign_key_columns AS fkc
INNER JOIN sys.foreign_keys AS fk
ON fkc.constraint_object_id = fk.[object_id]
INNER JOIN sys.tables AS t
ON fkc.parent_object_id = t.[object_id]
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
INNER JOIN sys.columns AS c1
ON c1.[object_id] = fkc.referenced_object_id
AND c1.column_id = fkc.referenced_column_id
AND c1.name = N'MyTableID'
INNER JOIN sys.columns AS c2
ON c2.[object_id] = fkc.parent_object_id
AND c2.column_id = fkc.parent_column_id
WHERE fkc.referenced_object_id = OBJECT_ID('dbo.MyTable');
PRINT @sql1;
PRINT @sql2;
PRINT @sql3;
-- EXEC sp_executesql @sql1;
-- EXEC sp_executesql @sql2;
-- EXEC sp_executesql @sql3;
Results:
ALTER TABLE [dbo].[OtherTable1] DROP CONSTRAINT [FK__OtherTable1__ID__32E0915F];
ALTER TABLE [dbo].[OtherTable2] DROP CONSTRAINT [FK__OtherTable2__ID__35BCFE0A];
ALTER TABLE dbo.MyTable DROP CONSTRAINT myx;
ALTER TABLE [dbo].[OtherTable1] ADD CONSTRAINT [FK__OtherTable1__ID__32E0915F]
FOREIGN KEY ([ID]) REFERENCES dbo.MyTable(MyTableID);
ALTER TABLE [dbo].[OtherTable2] ADD CONSTRAINT [FK__OtherTable2__ID__35BCFE0A]
FOREIGN KEY ([ID]) REFERENCES dbo.MyTable(MyTableID);
This explicitly handles this case, where the constraint only involves a single column. It gets a little more complex if there are multiple columns involved (and this answer is not meant to solve that problem). I also didn't test if this works exactly as coded if the foreign keys point to a redundant unique index (which has the same underlying structure but is created with slightly different DDL). Exercise for the reader. :-)
Best Answer
It does not. That primary key does not guarantee that either
a
orb
are unique, just that all combinations ofa
&b
are. There could be many rows for whichb = 1
is true, maybe all of them, maybe none of them. When you search for a specific combination ofa
andb
then it can use the index to do a simple seek.With that table definition, your first query is like asking "find all words in the dictionary where the second letter is 'a'". You can't answer that with a single seek. Your second query is like asking for "words starting 'aa'" which is easy to answer with that index.
Note though that some database systems are able to perform a skip-search to speed up the first query, which would help here, essentially looking for
a=1 and b=1
thena=2 and b=1
thena=3 and b=1
and so on (it isn't quite this but as near to as makes little odds). If operation is supported it may be used if the datatype ofa
and the selectivity indicated by the index stats suggest it might be appropriate. No version of SQL server supports this operation though. Oracle does unless you have a really old version (> a decade or two), as does SQLite, IIRC both call the operation a "skip scan". Further note that in every circumstance where a skip-scan is better than a full index scan, having an index on the second column to use would be more efficient, often significantly so, meaning that while the feature might make some queries better in databases that are not optimised for them, if you expect such queries you should optimise your table(s) for them by having the extra index even if your DBMS supports skip-searches.To explain why that particular index is selected and used instead of any other, we would need to know how that index and all the other indexes are defined, and perhaps see the query plan. Also you don't say how the index is used - I assume it was scanned rather than being used for seeks.