HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

Where are filtered-index dependencies listed?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
arewherelistedindexdependenciesfiltered

Problem

Given a table of the form...

create table dbo.FilterIDXTest ( 
    id int not null identity primary key clustered,
    _bigint bigint,
    _varchar10 varchar(10),
    _varchar20 varchar(20),
    _guid uniqueidentifier
);

go

create unique index uq_FilterIDXTest
    on dbo.FilterIDXTest ( _varchar10, _varchar20 ) 
    include ( _guid )
    where _bigint is not null 
        and id > 5;

go


... in what (if any) catalog view (or other well-formed reference object) can the columns names in in the WHERE clause be located and rigorously identified?

sys.indexes can be viewed to remind us that the filter_definition is ([_bigint] IS NOT NULL AND [id]>(5)).

sys.index_columns can be viewed to tell us both the ON and INCLUDE columns but lists only 3 of the 5 columns in the original CREATE INDEX command. The filter_predicate columns are not found here.

I must assume that the filter_predicate dependencies are rigorously checked at some stage because sp_rename 'dbo.FilterIDXTest._bigint', 'foo_bigint', 'column' fails with the the error...

Caution: Changing any part of an object name could break scripts and stored procedures.
Msg 5074, Level 16, State 1, Procedure sp_rename, Line 611
The index 'uq_FilterIDXTest' is dependent on column '_bigint'.
Msg 4922, Level 16, State 9, Procedure sp_rename, Line 611
RENAME COLUMN _bigint failed because one or more objects access this column.


...and I can't bring myself to believe that the only reason this fails is because of some text matching at sys.indexes.filter_definition.

For reference, the task at hand is to extract the source code from a number of undocumented legacy servers. My request to use RedGate/some other paid helper app for this purpose has already been rejected since "You can just script out the objects from the UI, right?". Perhaps I'm been overly rigorous, but I'd like to have a very high level of confidence that the index I'm checking in to source control is the index that a

Solution

I think this information is stored in sys.sql_expression_dependencies. See the table in MSDN, sys.sql_expression_dependencies:

THIS TOPIC APPLIES TO:

yes: SQL Server (starting with 2008)

no: Azure SQL Database

yes: Azure SQL Data Warehouse

yes: Parallel Data Warehouse

Remarks

The following table lists the types of entities for which dependency information is created and maintained. Dependency information is not created or maintained for rules, defaults, temporary tables, temporary stored procedures, or system objects.

Entity type          Referencing entity    Referenced entity  
Table                Yes*                  Yes  
View                 Yes                   Yes  
Filtered index       Yes**                 No  
Filtered statistics  Yes**                 No  
...


** Each column used in the filter predicate is tracked as a referencing entity.

Also note that filtered indexes were added as a feature in version 2008 and this system view does not exist in 2005 (thnx to @Michael Eriksson for noting this).

Code Snippets

Entity type          Referencing entity    Referenced entity  
Table                Yes*                  Yes  
View                 Yes                   Yes  
Filtered index       Yes**                 No  
Filtered statistics  Yes**                 No  
...

Context

StackExchange Database Administrators Q#150461, answer score: 5

Revisions (0)

No revisions yet.