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

user_206771 0
Viewed 68

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?

sql-server sql
1 Answers

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.

user_6398290
Related