Performance difference with filter index and with out filter index

Question

Performance difference with filter index and with out filter index

dudekula.kareemulla

Ten Centuries

Points: 1039
More actions
June 17, 2016 at 3:24 am

#311192

Hi,
I have a table 'AAA'
Structure:
CREATE TABLE AAA (ID BIGINT PRIMARY KEY CLUSTERED,Name VARCHAR(225) NULL,UAN BIGINT NOT NULL)
Assume that this Table Having 75 million of records.
Here I am going to create index like
script 1:
---------
CREATE INDEX IX_AAA
ON AAA (UAN) WHERE UAN>0
script 2:
----------
CREATE INDEX IX_AAA
ON AAA (UAN)
In UAN column all values already >0
Then also if i add filter condition like UAN>0 what is the difference between script1 and script2 in performance wise.
Please help me out on this.

Viewing 3 posts - 1 through 2 (of 2 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

dudekula.kareemulla (6/17/2016)
Hi,
I have a table 'AAA'
Structure:
CREATE TABLE AAA (ID BIGINT PRIMARY KEY CLUSTERED,Name VARCHAR(225) NULL,UAN BIGINT NOT NULL)
Assume that this Table Having 75 million of records.
Here I am going to create index like
script 1:
---------
CREATE INDEX IX_AAA
ON AAA (UAN) WHERE UAN>0
script 2:
----------
CREATE INDEX IX_AAA
ON AAA (UAN)
In UAN column all values already >0
Then also if i add filter condition like UAN>0 what is the difference between script1 and script2 in performance wise.
Please help me out on this.

Since all values of UAN are greater than zero, the number of rows in the index will always be the same as the number of rows in the table. The advantage a filtered index offers lies in the reduction of rows compared to the table. The proposed index offers no such advantage.

There's a cost associated with maintaining the filtered index. Changes to UAN will have to be compared to the predicate to determine whether a row in the index should be added, removed, or remain.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ScottPletcher SSC Guru Points: 101101 More actions · Answer 2

If UAN is how you do lookups and, especially, joins, then you'd probably get better overall performance by clustering the table on UAN rather than on an identity column. You would also very likely reduce the number of covering indexes you have to create.

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".