Filtered index with IS NULL predicate

Question

Filtered index with IS NULL predicate

Viewing 4 posts - 31 through 33 (of 33 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150468 More actions · Answer 1

GilaMonster (11/8/2011)
Time to reopen the item I think.

It'll be interesting to see if you can - I know Aaron has had trouble re-opening things recently.

Anyway, yes this needs clarifying - at the moment it says Won't Fix, fixed in SP2, and fixed in a future version. Add your email to that and I think they've made very decision possible at one stage or another 🙂

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Robert vd Berg SSC Eights! Points: 872 More actions · Answer 2

I ran into the same problem today, and tried to see if this has been fixed in SQL 2014. I'm a bit disappointed to see that this is not the case. SQL will still perform the lookup.

Robert van den Berg

Freelance DBA
Author of:

AdaTheDev Newbie Points: 6 More actions · Answer 3

Also just hit this (SQL 2012). Put in the "INCLUDE" workaround, but went digging as it felt grubby and came across this post 🙂

ScottPletcher SSC Guru Points: 101122 More actions · Answer 4

If you do many lookups by ManagerID, you'd likely be able to avoid messing with all this by clustering the table differently:

CREATE TABLE Employees (

ID INT NOT NULL IDENTITY,

ManagerID INT NULL,

Department VARCHAR(2) NOT NULL

)

GO

CREATE CLUSTERED INDEX Employees__CL ON Employees ( ManagerID ) WITH ( FILLFACTOR = 99, SORT_IN_TEMPDB = ON ) ON [<filegroup>] --<< adjust FILL if/as needed

--or, if you prefer only explicitly unique clustered indexes:

--CREATE UNIQUE CLUSTERED INDEX Employees__CL ON Employees ( ManagerID, ID )

GO

CREATE UNIQUE NONCLUSTERED INDEX Employees__IX_ID ON Employees ( ID ) ON [<filegroup>]

A single lookup by ID will be extremely efficient either way. The huge gain comes when you need to process all/some employees for a specific manager(s).

You might end up having to rebuild the table slightly more often, but it could be well worth it in terms of overall performance, esp. if you can use ONLINE rebuild.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".