I have been asked the following question in an interview recently. Using clustered index in a table, which operation among insert, delete or update will be faster or slower?
4
-
And what did you answer?Raj– Raj2014-02-18 07:53:10 +00:00Commented Feb 18, 2014 at 7:53
-
To some extent, it depends on what the clustered index is on - is it on real data column(s) or on an identity column?Damien_The_Unbeliever– Damien_The_Unbeliever2014-02-18 07:54:46 +00:00Commented Feb 18, 2014 at 7:54
-
@Damien_The_Unbeliever,can you please explain more ?you mean in case of identity there is no page split .please explain.KumarHarsh– KumarHarsh2014-02-18 08:05:44 +00:00Commented Feb 18, 2014 at 8:05
-
Indexes are created to enhance DQL querie (SELECT) and it has negative effect on DML (Insert, Delete and Update). If they did not give any real time scenario, I would say all the DML queries will have negative effect.Sandy– Sandy2014-02-18 10:23:13 +00:00Commented Feb 18, 2014 at 10:23
Add a comment
|
1 Answer
The effect of indexes on DML queries is mostly negative ie more are the number of indexes created on table , slower is performance of an DML query like insert , update ,delete.
In fact , removing indexes while doing bulk load i.e inserting huge number of rows at same time is common practice. See here
