Wednesday 18 November 2009

Filter Index in SQL Server 2008

SQL Server 2008 introduces a new type of index called “Filtered Index” which is basically a covered indexed designed to retrieve a smaller set of qualified data from a table. This can be a very hand feature particularly in working with larger data tables.
A Filter index allows us to apply filter criteria on the index definition so that a particular sub set of rows in a table alone can be indexed. Filter indexes can be only created as non clustered index


Syntax
CREATE INDEX Index Name ON Table Name (Columns..) Filter Criteria


Example
CREATE INDEX IX_RegistrationDate ON Employee (RegistrationDate) WHERE RegistrationDate IS NOT NULL


Advantages of Filtered Index

  • Improved Performance: The performance of the query is improved especially with larger tables as it has to scan through as lesser number of records
  • Lesser Maintenance Cost: Since the size of the index is smaller compared to full table index the index maitntenance cost will be much lesser. Also index maintenance jobs like update statics could be faster.

  • Lesser Storage: The amount of space required for index storage will also be very less since the size of the index is smaller compared to the full table index

Analysis

I have created a table patient with 1 lakh records of different Organisations and populated 70% data with OwnerOrganisation value 10 and selected the record with OwnerOrganisation value ="6"

Normal Index

CREATE INDEX IX_OwnerOrganisation ON Patient(OwnerOrganisationUID)



Filterer Index to exclude records of Organisation =10

CREATE INDEX IX_OwnerOrganisation ON Patient(OwnerOrganisationUID) WHERE OwnerOrganisationUID <>10




Conclusion

Index creation is always case to case basis as the need to create a filtered index should be carefully analysed based on the WHERE clause and the data distribution in the table. It is recommended to create filtered indexes if the data retrieved to be a smaller subset. Scenarios like columns with NULL data as major set and NOT NULL values of defined subsets could be a suitable candidate

9 comments:

sap pp said...

Thank you so much! I must say that you explained filter index in very easy & simple language. I understood it very quickly.

Anonymous said...

Hello your web site url: http://www.blogger.
com/comment.g?blogID=8857019241081536639&postID=859811550949680887 seems
to be redirecting to a completely different
site when I click the homepage button.
You might want to have this looked at.

Here is my web page ... View my web page

Unknown said...

ninest123 16.03
ray ban sunglasses, cheap oakley sunglasses, replica watches, michael kors outlet, oakley sunglasses, jordan shoes, prada handbags, nike outlet, nike air max, nike free, louis vuitton, tiffany and co, ugg boots, louboutin outlet, ray ban sunglasses, louis vuitton outlet, burberry outlet online, michael kors outlet, chanel handbags, uggs on sale, tiffany jewelry, louis vuitton outlet, michael kors outlet, oakley sunglasses, oakley sunglasses, louboutin shoes, prada outlet, ugg boots, ray ban sunglasses, ugg boots, burberry, replica watches, michael kors outlet, tory burch outlet, longchamp outlet, michael kors outlet, louis vuitton, michael kors, polo ralph lauren outlet, longchamp outlet, louis vuitton, polo ralph lauren outlet, louboutin, oakley sunglasses, gucci outlet, ugg boots, christian louboutin outlet, nike air max, longchamp

Unknown said...

north face, hogan, coach outlet, air force, hollister pas cher, michael kors, coach outlet, lululemon, burberry, sac guess, nike roshe, nike free, true religion jeans, air jordan pas cher, lacoste pas cher, ralph lauren pas cher, michael kors, kate spade outlet, ralph lauren uk, michael kors, new balance pas cher, true religion jeans, ray ban uk, nike roshe run, converse pas cher, michael kors, hermes, mulberry, vans pas cher, louboutin pas cher, true religion outlet, ray ban pas cher, nike air max, oakley pas cher, air max, timberland, nike free run uk, kate spade handbags, nike air max, hollister, tn pas cher, replica handbags, nike air max, vanessa bruno, abercrombie and fitch, sac longchamp, true religion jeans, nike blazer, coach purses, longchamp pas cher, north face

Unknown said...

ugg,uggs,uggs canada, canada goose, ugg,ugg australia,ugg italia, canada goose, hollister, wedding dresses, moncler, louis vuitton, canada goose outlet, swarovski, pandora charms, pandora jewelry, moncler outlet, moncler, sac louis vuitton pas cher, replica watches, louis vuitton, marc jacobs, louis vuitton, vans, moncler, swarovski crystal, pandora jewelry, toms shoes, moncler, juicy couture outlet, doudoune canada goose, karen millen, converse, lancel, canada goose outlet, coach outlet, gucci, ray ban, thomas sabo, moncler, supra shoes, canada goose uk, juicy couture outlet, links of london, barbour jackets, louis vuitton, canada goose, barbour, ugg boots uk, pandora charms, bottes ugg, converse outlet, moncler, montre pas cher, canada goose, ugg pas cher, moncler
ninest123 16.03

chenlina said...

chenlina20160531
oakley sunglasses
oakley sunglasses outlet
coach factory outlet
louis vuitton outlet
true religion jeans
lebron 13
adidas nmd
louboutin pas cher
replica watches
hollister outlet
tiffany and co
hollister clothing
cheap ray ban sunglasses
ray ban sunglasses
michael kors handbags
coach outlet clearance
louis vuitton purses
michael kors outlet
jordan concords
nike roshe flyknit
coach factory outlet
louis vuitton handbags
coach factory outlet
air jordan 13
coach outlet online
michael kors uk
louis vuitton handbags
fitflops sale clearance
fitflop sandals
gucci handbags
louis vuitton outlet
polo ralph lauren
burberry bags
kobe 10
michael kors uk
nike store
abercrombie and fitch new york
air jordans
ray ban sunglasses
michael kors
as

Unknown said...

rayban
pandora uk
michael kors uk
cheap wedding dresses
nike air max
ugg outlet
prada
hollister
ralph lauren outlet
nike air max
2017.11.20chenlixiang

xjd7410@gmail.com said...

20180105 junda
true religion jeans
christian louboutin outlet
longchamp handbags
ugg outlet
puma outlet
true religion jeans
canada goose outlet
ugg outlet online
coach outlet store
links of london

Rasi said...

Thanks for sharing this post
Power BI Training In Hyderabad
Power BI Training
Power BI Online Training
Power BI Training Online