I'm a bit confuse about index intersection.
MSDN says you can but:
- I'm not enable to reproduce the case example
- I've never sees anything like that (and even heard that's not supported)
The FORCESEEK hint supports index unions and intersections. The hint makes the query optimizer more likely to use these techniques. To avoid slowing the compilation time of simple queries, index unions and intersections are normally only chosen according to rules that take into account the cardinality and selectivity of the columns. However, when the FORCESEEK hint is specified, such rules are bypassed and these techniques are always considered.
Do not use multiple aliases for a single table in the same query to simulate index intersection. This is no longer necessary because SQL Server automatically considers index intersection and can make use of multiple indexes on the same table in the same query.
Microsoft case test reproduced here which does not show any index intersection (under SS2k8R2 10.50.1617):
CREATE TABLE [dbo].[intTable](
[int1] [int] NOT NULL,
[int2] [int] NOT NULL
CREATE NONCLUSTERED INDEX [IX_1] ON [dbo].[intTable]
CREATE NONCLUSTERED INDEX [IX_2] ON [dbo].[intTable]
DECLARE @i AS INT = 1
WHILE @i < 30000
INSERT INTO dbo.intTable VALUES (@i,@i+1)
SET @i += 1
SELECT * FROM dbo.intTable T WITH(FORCESEEK) WHERE T.int1 = 1 AND T.int2 = 2;
There's even an article on SSC that promote this. However it's more 10 years old:
What am I missing? Or what are the particular scenario to enable (make SQL use) index intersection?
Can someone provide a test sample that actually trigger index intersection (or show how to detect them in the execution plan)?