sqlschool.gr logo

articles

Articles of SQLschool.gr Team

Find missing indexes on foreign key constraints

Antonios Chatzipavlis
Monday 16 October 2017

Τα foreign keys όπως είναι γνωστό δεν φτιάχνουν κάποιον index στο reference table. Επίσης είναι γνωστό ότι καλό (αλλά όχι πάντα απαραίτητο) να φτιάχνουμε nonclustered index στο reference table στo field(s) που εμπλέκονται στο FK constraint.



Με το παρακάτω script βρίσκουμε αυτές τις περιπτώσεις τις αξιολογούμε και αν χρειαστεί να φτιάξουμε αυτούς η τελευταία κολώνα περιέχει και το create index statement.



Enjoy

//antonch


with fcc as
(
    select 
            sch1.name                                as parent_schema_name,
            object_name(fkc.parent_object_id)        as parent_table_name,
            object_name(fkc.constraint_object_id)    as constraint_name,
            sch2.name                                as referenced_schema,
            object_name(fkc.referenced_object_id)    as referenced_table_name,
            substring(
                        (    select ',' 
                                + rtrim(col_name(fc.parent_object_id,parent_column_id)) as [data()]
                            from sys.foreign_key_columns as fc with (nolock)
                            inner join sys.foreign_keys as fk with (nolock) on fc.constraint_object_id = fk.[object_id]
                            and fc.constraint_object_id = fkc.constraint_object_id
                            order by fc.constraint_column_id
                            for xml path('')
                        ), 2, 8000)                    as parent_columns,
            substring(
                        (    select ',' 
                                + rtrim(col_name(fc.referenced_object_id,referenced_column_id)) as [data()]
                            from sys.foreign_key_columns as fc with (nolock)
                            inner join sys.foreign_keys as fk with (nolock) on fc.constraint_object_id = fk.[object_id]
                            and fc.constraint_object_id = fkc.constraint_object_id
                            order by constraint_column_id
                            for xml path('')
                        ), 2, 8000)                    as referenced_columns
    from        sys.foreign_key_columns        as fkc    with (nolock)
    inner join    sys.objects                    as obj1 with (nolock) on fkc.parent_object_id        =    obj1.[object_id]
    inner join    sys.tables                    as tbl1 with (nolock) on tbl1.[object_id]            =    obj1.[object_id]
    inner join    sys.schemas                    as sch1 with (nolock) on sch1.[schema_id]            =    tbl1.[schema_id]
    inner join    sys.objects                    as obj2 with (nolock) on fkc.referenced_object_id    =    obj2.[object_id]
    inner join    sys.tables                    as tbl2    with (nolock) on tbl2.[object_id]            =    obj2.[object_id]
    inner join    sys.schemas                    as sch2 with (nolock) on sch2.[schema_id]            =    tbl2.[schema_id]
    where        obj1.type = 'U' 
                and 
                obj2.type = 'U'
    group by    obj1.[schema_id],
                obj2.[schema_id],
                fkc.parent_object_id,
                constraint_object_id,
                referenced_object_id,
                sch1.name,
                sch2.name
),
idxcols as
(
    select 
            s.name                        as schemaname,
            object_name(t.[object_id])    as objectname,
            substring(
                        ( 
                            select ',' 
                                + rtrim(ac.name) 
                            from sys.tables                    as st
                            inner join sys.indexes            as ix on st.[object_id] = ix.[object_id]
                            inner join sys.index_columns    as ic on ix.[object_id] = ic.[object_id] and ix.[index_id]    = ic.[index_id] 
                            inner join sys.all_columns        as ac on st.[object_id] = ac.[object_id] and ic.[column_id] = ac.[column_id]
                            where    i.[object_id] = ix.[object_id] 
                                    and 
                                    i.index_id = ix.index_id 
                                    and 
                                    ic.is_included_column = 0
                            order by ac.column_id
                            for xml path('')
                        ), 2, 8000 ) as keycols
    from        sys.indexes        as i
    inner join    sys.tables        as t    on    t.[object_id]    =    i.[object_id]
    inner join    sys.schemas        as s    on    s.[schema_id]    =    t.[schema_id]
    where    i.[type] in (1,2,5,6) 
            and 
            i.is_unique_constraint = 0
            and 
            t.is_ms_shipped = 0
)
select    fcc.constraint_name,
        fcc.parent_schema_name +'.' + fcc.parent_table_name as parent_table,
        fcc.referenced_schema + '.' + fcc.referenced_table_name as reference_table,
        fcc.parent_columns,
        fcc.referenced_columns,
        N'CREATE NONCLUSTERED INDEX idx_'    +
            fcc.referenced_table_name        +    
            '_'                                +
            fcc.constraint_name                +
            N' ON '                            +
            fcc.parent_schema_name            +
            '.'                                + 
            fcc.parent_table_name            +
            N'('                            +
            fcc.referenced_columns            +
            N');'    as ddl_create
from fcc
where not exists ( SELECT 1 FROM idxcols 
                    WHERE fcc.parent_schema_name = idxcols.schemaName
                        AND fcc.parent_table_name = idxcols.objectName 
                        AND REPLACE(fcc.parent_columns,'' ,'') = idxcols.KeyCols)

Antonios Chatzipavlis

Antonios Chatzipavlis

Antonios Chatzipavlis is a highly experienced Data Solutions Consultant and Trainer. He has been working in the IT industry since 1988, holding various roles such as senior developer, IT Manager, Data & AI Solutions Architect and Consultant.

Since 1995, Antonios has focused on modern technologies and software development tools, primarily by Microsoft. He has specialized in Data & AI since 2000, with expertise in Microsoft Data Platform (SQL Server, Azure SQL Databases, Azure Synapse Analytics, Microsoft Fabric, Power BI, AI) and Databricks.

Antonios is also a Microsoft Certified Trainer (MCT) for over 25 years, has been recognized as a Microsoft Most Valuable Professional (MVP) in Data Platform since 2010 and he is in the Data Expert 40 Powerlist 2024 by Boussias. He is the co-founder and visionary behind XLYTiCA, a company dedicated to Data & AI solutions.

Episode

Task Flows in Microsoft Fabric

image

More Episodes...

Tip

Get Certified: Become a Fabric Data Engineer

More Tips...

Become a member

If you want to receive updates from us become a member to our community.

Connect

Explore

Learn

sqlschool.gr © 2010-2025 All rights reserved

This site uses cookies for operational and analytics purposes only. By continuing to browse this site, you agree to their use.