CODEWITHSUNDEEP
sqlsql-server
Joel Shemtov
Joel Shemtov

Reputation: 3078

Is it a bad idea to create index containing field that changes frequently?

I need to prevent table scan on a big table where a search of a record is based on three fields, one of which may be updated. The searching query looks like this:

select blabla from ttg_transaction where uti = ? and txn_type = ? and state = ?

The index that comes to mind (not unique, not clustered) would the three fields above. But while the first two are constant, 'state' does change during the life cycle of a record.

Is this a good reason to exclude 'state' from the index?

Upvotes: 2

Views: 710

Answers (1)

Preben Huybrechts
Preben Huybrechts

Reputation: 6111

Things that would make this a bad idea

  • If you have slow storage (spinning metal disks)
  • If your data types are large (TEXT/NTEXT, VARBINARY, XML, ...)
  • High frequency updates.

Or a combination of these.

Assuming you have fast storage, I wouldn't worry to much. If you still have slow storage you could

  • Profile the insert/update statements
  • Create the index
  • Profile the insert/update statements when the index is created
  • Compare the results.

To profile you can use SET STATISTICS IO ON and/or SET STATISTICS TIME ON

To handle index fragmentation you could specify a fill factor that makes sense for your case.

If state is random text field this could interfere with your statistics as well, but you didn't specify.

Upvotes: 2

Related Questions