(DIR) Return Create A Forum - Home --------------------------------------------------------- ExcelSoft Database Professionals (HTM) https://esdbp.createaforum.com --------------------------------------------------------- ***************************************************** (DIR) Return to: Scripts ***************************************************** #Post#: 125-------------------------------------------------- Filter Index Access Demo By: srinivasma_exceldbp Date: December 25, 2014, 9:13 pm --------------------------------------------------------- --** How Filter Index Works Demo USE AdventureWorks2012; GO SELECT ComponentID, StartDate FROM Production.BillOfMaterials WHERE EndDate IS NOT NULL; GO -- Create two nonclustered indexes on the same columns of the -- BillofMaterials table; one filters out the NULLs, -- the other plain IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'BillOfMaterials_ComponentID' AND object_id = OBJECT_ID (N'Production.BillOfMaterials')) DROP INDEX BillOfMaterials_ComponentID ON Production.BillOfMaterials GO CREATE NONCLUSTERED INDEX BillOfMaterials_ComponentID ON Production.BillOfMaterials (ComponentID, StartDate); GO IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'FIBillOfMaterialsWithEndDate' AND object_id = OBJECT_ID (N'Production.BillOfMaterials')) DROP INDEX FIBillOfMaterialsWithEndDate ON Production.BillOfMaterials GO CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate ON Production.BillOfMaterials (ComponentID, StartDate) WHERE EndDate IS NOT NULL; GO -- Index Size SELECT object_name(object_id) as object_name, index_id as index_name, page_count, * FROM sys.dm_db_index_physical_stats ( DB_ID('AdventureWorks2012') , object_id('Production.BillOfMaterials') , null , null , DEFAULT ) exec sp_spaceused 'Production.BillOfMaterials' -- Find a ComponentID value that only occurs a few -- times, so that the index will be used SELECT ComponentID, COUNT(*) FROM Production.BillOfMaterials GROUP BY ComponentID GO -- Notice the performance difference when using the -- filtered index SET STATISTICS IO ON GO SELECT * FROM Production.BillOfMaterials WHERE ComponentID = 4 SELECT * FROM Production.BillOfMaterials WHERE ComponentID = 4 AND EndDate is NOT NULL SET STATISTICS IO OFF GO *****************************************************