DIR Return Create A Forum - Home
       ---------------------------------------------------------
       ExcelSoft Database Professionals
  HTML 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
       *****************************************************