none
index RRS feed

  • Question

  • Hi all, 

    I'm a newbie to sql server.  Just wonder if this index creation is the best way to create:

    create

        nonclustered index

            [Claim_Detail_idx\SUD_Identification]

        on

            stageTvn.Claim_Detail  (  APR_DRG_Cd  )

        include (

                    ICN_Nbr,

                    Source_File_Name,

                    Bill_Prov_Spclty_Cd,

                    Bill_Prov_Typ_Cd,

                    Diag_1_Cd,

                    Diag_2_Cd,

                    Diag_3_Cd,

                    Diag_4_Cd,

                    EAPG_Cd,

                    PoS_Cd,

                    Prc_Cd,

                    Proc_Mod_1_Cd,

                    Proc_Mod_2_Cd,

                    Proc_Mod_3_Cd,

                    Proc_Mod_4_Cd,

                    Rend_Prov_Spclty_Cd,

                    Rend_Prov_Typ_Cd,

                    Rvn_Cd,

                    Sens_Dat_Cd_sk

                )

           with (

                    allow_page_locks    = on,   allow_row_locks         = on,

                    drop_existing       = off,

                    online              = off,  pad_index               = off,

                    sort_in_tempdb      = off,  statistics_norecompute  = off

                )

             on [PRIMARY];


    Friday, August 30, 2019 3:39 PM

All replies

  • Speaking of index options (WITH part). You are using online=off, which means that you are going to do it offline, locking the table. If you are on Enterprise/Developer edition, you can use online=on eliminating long-time table lock.

    Drop_existing=off, means that you create the new index and there are no indexes with the same name present. Switch it to ON if you are modifying existing index.

    You did not specify FILLFACTOR (amount of free space reserved on the data pages to minimize page splits) - so it will use server-level setting.

    As for index itself (columns, keys, etc). It is impossible to comment without knowing your system. To me, index looks very wide with a lot of columns. It may be acceptable but remember that extra columns add update overhead and use more space. If you just copied SSMS index recommendation, remember that it would apply only one particular query - it is usually better to perform holistic analysis during query optimization


    Thank you!

    Dmitri V. Korotkevitch (MVP, MCM, MCPD)

    My blog: http://aboutsqlserver.com

    Friday, August 30, 2019 3:57 PM