And just how would you have them in synch between make sure production conditions?
If this involves indexes on database tables, my philosophy is they are a fundamental element of writing any code that queries the database. You cannot introduce new queries or change a question without examining the impact towards the indexes.
And So I do my better to keep my indexes in synch betweeen all my conditions, but to tell the truth, I am not thriving at automating this. It is a kind of aimless, manual process.
I periodocally review index stats and remove unnecessary indexes. It's my job to do that by developing a remove script which i then copy to another conditions.
But in some places indexes get produced and erased outdoors from the normal process and it is difficult to see in which the variations are.
I have found one factor that actually helps is to choose simple, number index names, like
where t is really a short abbreviation for any table. I've found index maintenance impossible after i attempt to get clever with the posts involved, like,
It's way too hard to distinguish indexes like this.
Does anybody have an excellent method to integrate index maintenance into source control and also the development lifecycle?
Indexes are an element of the database schema and therefore ought to be source controlled together with anything else. Nobody is going around creating indexes on production without studying the normal QA and release process- particularly performance testing.
There has been numerous other threads on schema versioning.
The entire schema for the database ought to be in source control right beside your code. When I only say "full schema" I am talking about table definitions, queries, saved methods, indexes, the entire lot.
When you are performing a brand new installation, then you definitely do: - take a look at version X from the product. - in the "database" directory of the checkout, run the database script(s) to produce your database. - make use of the codebase out of your checkout to have interaction using the database.
When you are developing, every developer ought to be working against their very own private database instance. Once they make schema changes they checkin a brand new group of schema definition files that actually work against their modified codebase.
With this particular approach you won't ever have codebase-database sync issues.
Yes, any DML or DDL changes are scripted and checked directly into source control, mostly through activerecord migrations in rails. I personally don't like to constantly toot rails' horn, however in years of building DB-based systems I've found the migration path to be so a lot better than any home-grown system I have used or built.
However, I actually do title my indexes (don't allow the DBMS develop whatever crazy title it picks). Don't prefix them, that's silly (since you have type metadata in sysobjects, or perhaps in whatever db you've), however i do range from the table title and posts, e.g. tablename_col1_col2.
This way if I am browsing sysobjects I are able to see the indexes for the table (in fact it is a pressure of habit, wayyyy in older days on some dBMS I made use of, index names were unique over the whole DB, so the only method to ensure that's to make use of unique names).