SQL Server 2008 R2 filtered index

Question

Is there any way to investigate what I haven't used as a value during creating filtered index? I have looked at index create script but still no value.

create nonclustered index IX1 where status=0

I am looking for the status column and 0 value in below query

Answer 1

The built-in system catalog view sys.indexes has the filter information in the filter_definition column:

select
    o.name as [Table],
    i.name as [Index],
    i.filter_definition as [Filter]
from sys.objects o
inner join sys.indexes i on i.object_id = o.object_id
where i.name = 'IX1'

Answer 2

As per the comments, SQL Management Studio can script your filtered index. Just right click on your index and click Script Index as. However, if you want to query it using SQL, you can just select the filter definition column from the sys.indexes table:

 SELECT SCHEMA_NAME(t.schema_id) ,
       t.name ,
       i.name ,
       filter_definition
 FROM   sys.indexes i
       INNER JOIN sys.tables t ON t.object_id = i.object_id
 WHERE  i.type > 0
       AND t.is_ms_shipped = 0
       AND t.name <> 'sysdiagrams'
       AND ( is_primary_key = 0
            AND is_unique_constraint = 0
          )
       AND i.name = 'IX1'

Answer 3

Most likely you cannot confirm the filtered index where clause because that is considered code, and you only have access to data.

See https://dba.stackexchange.com/questions/55811/database-having-only-db-datareader-shows-invalid-properties-for-a-index

It is true that a db_datareader cannot directly confirm the filtered index where clause (sys.indexes.filter_definition), and SSMS will not warn a db_datareader that scripting the index definition will be incomplete.

However, there is a workaround that a db_datareader can use to indirectly confirm the filter_definition: specify an index hint in a query, with a where clause to test for coverage by the filtered index.

MS-SQL will throw an error, if a column or value you specify in the query where clause is NOT covered by the filtered index where clause (sys.indexes.filter_definition).

So this way, you can at least verify that the query where clause you would like to use is covered by the filtered index. You do not need to use the index hint in production, after you have verified it this way.

For example, if a filtered index is created by a db_owner like this:

CREATE UNIQUE INDEX UQ_ZipData_PrimaryPerZip
 ON dbo.ZipData (ZIP) WHERE IsPrimaryCity = 1

Then as a db_datareader, I can confirm that this query works, so I have confirmed that IsPrimaryCity = 1 is covered by the index:

SELECT COUNT(*) AS Num
  FROM dbo.ZipData WITH (INDEX (NCI_ZipData_Zip))
  WHERE IsPrimaryCity = 1

Also as a db_datareader, I can confirm that this query fails, so I have confirmed that IsPrimaryCity = 0 is NOT covered by the index:

SELECT COUNT(*) AS Num
  FROM dbo.ZipData WITH (INDEX (NCI_ZipData_Zip))
  WHERE IsPrimaryCity = 0

The error message returned by MS-SQL is:

Msg 8622, Level 16, State 1, Line 1
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.