Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Non clustered index on columns used in Data Modification queries

Answered Non clustered index on columns used in Data Modification queries

  • Saturday, March 16, 2013 4:05 PM
     
     
    Sign In to Vote
    0

    Hi,

    I have a Stored procedure in which I have delete and update operations.

    Could the columns in where clause be indexed?

    I don't think so but any insights, please.

    Thanks, S

     

All Replies

  • Saturday, March 16, 2013 4:17 PM
     
     Answered
    Sign In to Vote
    0

    Yes, they can be included.

    SQL Server will update clustered index and all non clustered indexes for each insert/delete.

     
  • Saturday, March 16, 2013 4:20 PM
     
     
    Sign In to Vote
    0

    Thanks.

    This where clause is used in the DML, for ex-

    Delete from Table1 where column1 ='ABC'

    Update Table1

    Set Col2 = X

    where Col1 = Y

    Thanks, S

     
  • Saturday, March 16, 2013 7:14 PM
     
     
    Sign In to Vote
    0

    Hi Shweta,

    You can create indexes if our SELECTIVITY ratio is narrow. Otherwise, even if you create index, SQL Server may end up doing scan for the operation. What combination/individual columns index should be created, depends on the usage of the table. Let me know if you have further queries. More details are required to answer your question.

    Regard,

    Phaneendra Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem.