go backarticles

Articles of SQLschool.gr Team

Find missing indexes on foreign key constraints

Antonios Chatzipavlis

Τα 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 is a Data Solutions Consultant and Trainer. He has been working in IT since 1988. In his career, he has worked as senior developer, IT Manager, Solutions Architect and IT Consultant. Since 1995 he has been devoted on new technologies and software development tools, mainly by Microsoft, either by training company staff and colleagues or assisting them in design, development and implementation as a consultant or chief developer. He has focused in Databases and Data Science since 1995. He specialized in Microsoft SQL Server since version 6.0 in areas like SQL Server Internals, Database Design and Development, Business Intelligence and in 2010 he has started working with Azure Data Platform, NoSQL databases, Big Data Technologies and Machine Learning. He is an active member of many IT communities in Greece, answering colleagues' questions and writing articles in his web site. He is the owner of SQLschool.gr which is a community portal with a lot of information about Microsoft SQL Server. He has been a Microsoft Certified Trainer (MCT) since 2000. Microsoft honored him as MVP on Data Platform due to his activities in SQL Server since 2010. He holds a large number of Microsoft Certifications and Microsoft SQL Server Certifications since version 6.5.


Relative Articles

Leave your comment

COMMENT

FULL NAME

EMAIL ADDRESS

We use Gravatar

WEB SITE



captcha


 

Newsletter

If you want to receive updates from us subscribe below with your email.
Follow us in
PASS chapter logo

The Official PASS Local Group for Greece

About us Contact us Terms of Use Privacy Sing in Register
sql school greece logo
© 2010-2020 All rights reserved

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