Skip to content

Instantly share code, notes, and snippets.

@leppie
Last active October 15, 2021 08:40
Show Gist options
  • Save leppie/464c3545684af6f02aa0794d707f503b to your computer and use it in GitHub Desktop.
Save leppie/464c3545684af6f02aa0794d707f503b to your computer and use it in GitHub Desktop.
What is going on with the SQL Server query optimizer/planner?
drop table if exists [dbo].[Simple]
go
CREATE TABLE [dbo].[Simple](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[Extra] [bit] NULL,
[Data] [nvarchar](max) NULL,
CONSTRAINT [PK_Simple] PRIMARY KEY CLUSTERED ( [Id] ASC )
)
GO
-- Data has potentially huge amounts of data, thus not included
CREATE NONCLUSTERED INDEX [IX_HasData] ON [dbo].[Simple]([Name] ASC) WHERE ([Data] IS NOT NULL)
go
set NOCOUNT ON
set STATISTICS TIME OFF
insert into Simple(Name) values ('Name')
go 500000
set NOCOUNT OFF
set STATISTICS TIME ON
update Simple
set Data = 'blah blah blah blah'
where Id % 100 = 0
update Simple
set Name = Id % 29
go
-- check query plans
-- why is IX_HasData not used in the first 2 cases? The last 2 cases emulates what I expect the query engine to do or a key lookup
select * from Simple where Data is not null
select * from Simple where Data is not null and Name = '25'
select * from Simple where Id in (select Id from Simple where Data is not null)
select * from Simple where Id in (select Id from Simple where Data is not null and Name = '25')
-- why no key lookup?
select Id, Name, Extra from Simple where Data is not null and Name = '25'
-- this is ok
select Id, Name from Simple where Data is not null and Name = '25'
-- what it should be doing
select * from Simple with (index = IX_HasData) where Data is not null and Name = '25' OPTION (MAXDOP 1)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment