CODEWITHSUNDEEP
sql-server-2005database-indexes
Mike Comstock
Mike Comstock

Reputation: 6720

Do non-clustered indexes slow down inserts?

I'm working in Sql Server 2005. I have an event log table that tracks user actions, and I want to make sure that inserts into the table are as fast as possible. Currently the table doesn't have any indexes. Does adding a single non-clustered index slow down inserts at all? Or is it only clustered indexes that slow down inserts? Or should I just add a clustered index and not worry about it?

Upvotes: 6

Views: 8201

Answers (2)

marc_s
marc_s

Reputation: 754963

Yes, any index will take a little bit of time to keep up to date when doing INSERT, UPDATE, DELETE operations. The more indices you have, the more time we're talking about.

But ultimately it depends on what's more important to you - good query performance (then add indices as needed), or good insert performance (then have as few indices as possible).

Which operation do you perform more often??

Upvotes: 2

Josef Richberg
Josef Richberg

Reputation: 613

Indexes, clustered or non-clustered,will always slow down inserts as SQL has to maintain both the table and index. This slowdown is in an "absolute" sense and you may not notice it. I would add whatever indexes are necessary to retrieve your data.

Upvotes: 7

Related Questions