Saturday, March 16, 2013 4:05 PM
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.
Saturday, March 16, 2013 4:17 PM
Yes, they can be included.
SQL Server will update clustered index and all non clustered indexes for each insert/delete.
- Marked As Answer by Allen Li - MSFTModerator Monday, March 25, 2013 6:14 AM
Saturday, March 16, 2013 4:20 PM
This where clause is used in the DML, for ex-
Delete from Table1 where column1 ='ABC'
Set Col2 = X
where Col1 = Y
Saturday, March 16, 2013 7:14 PM
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.
Phaneendra Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem.