CODEWITHSUNDEEP
sqlsql-server
Arnold Zahrneinder
Arnold Zahrneinder

Reputation: 5200

Multiple filtered indexes for the same columns

Would it make sense to create multiple filtered indexed based on different criteria for the same columns? For example assuming the following:

 CREATE TABLE [dbo].[Skills](
     [UserId] INT NOT NULL PRIMARY KEY,
     [SkillTitle] NVARCHAR(50) NOT NULL,
     [SkillLevel] INT NOT NULL CHECK ([SkillLevel] BETWEEN 0 AND 10)
 ) 
 GO

 CREATE INDEX [IX_Skill_Low] ON [dbo].[Skill] (SkillTitle,SkillLevel) WHERE SkillLevel BETWEEN 3 AND 0
 GO

 CREATE INDEX [IX_Skill_Moderate] ON [dbo].[Skill] (SkillTitle,SkillLevel) WHERE SkillLevel BETWEEN 5 AND 3
 GO

 CREATE INDEX [IX_Skill_Good] ON [dbo].[Skill] (SkillTitle,SkillLevel) WHERE SkillLevel BETWEEN 7 AND 5
 GO

 CREATE INDEX [IX_Skill_High] ON [dbo].[Skill] (SkillTitle,SkillLevel) WHERE SkillLevel > 7

Or would it be better to create a single non-filtered index?

And what happens if the following query is made SELECT * FROM Skills WHERE SkillLevel BETWEEN 7 AND 1?

Upvotes: 0

Views: 275

Answers (1)

TheGameiswar
TheGameiswar

Reputation: 28900

Unless you have millions of records for each variation of skill level.

ie.,
BETWEEN 5 AND 3
BETWEEN 5 AND 7

I wouldn't recommend that,i recommend creating a single filtered index like below

skilllevel between 1 and 10

And what happens if the following query is made SELECT * FROM Skills WHERE SkillLevel BETWEEN 7 AND 1?

if if your select * contains many columns other than which are not specfied in index ,sql may

  • choose to use this index and do a look up on base table for rest of columns
  • do a full table scan

i would create a below covering index,but this depends on your data

create index nci_test on table(skilllevel)
include(SkillTitle)

I did some tests to understand more, you can find below test script

CREATE TABLE [dbo].[Skills](
     [UserId] INT NOT NULL  identity(1,1) PRIMARY KEY,
     [SkillTitle] NVARCHAR(50) NOT NULL,
     [SkillLevel] INT NOT NULL CHECK ([SkillLevel] BETWEEN 0 AND 10)
 ) 
 GO

insert into dbo.Skills
(SkillTitle,SkillLevel)
select cast(NEWID() as varchar(50)),
case when rand()*10<0 then 1 else rand()*10 end
go 100000

now lets run some queries 

create index nci_1and5 on dbo.skills(skilllevel)
include(skilltitle)
where skilllevel >= 1 and skilllevel <=5

create index nci_5and10 on dbo.skills(skilllevel)
include(skilltitle)
where skilllevel >5 and skilllevel <=10

select * from skills where skilllevel 
between 1 and 4--uses 1 to 5 index

select * from skills where skilllevel 
between 6 and 10--uses 6 to 10 index

select * from skills where skilllevel 
between 1 and 7--uses scan

however i dont recommend these indexes even if you have millions of rows between skill levels.I would recommend partioning

Upvotes: 2

Related Questions