a companion discussion area for blog.codinghorror.com

Why Can't Database Tables Index Themselves?


This link may be of interest for SQL Server 2005 users. It describes a way of implementing automated auto-indexing:


This is just stupid. lazy and bad programing.

If you have just created an application then you should know what table fields to index before you put the application to use. if you don’t know then why are you creating applications (And Bad application i must add).

this is a fundamental design process and you as the developer should theoretically know what parts of your applications database needs indexing for performance. after all I’m sure you application won’t be creating random SQL queries on the fly?!?! they will always look up the fields, tables and joins you programed into the app at the start. then why don’t you think how performance will be hit when these queries hit tables and columns which will have allot of rows in the future.

This is some thing which conceptually you need to know to create a good application!!!


Isn’t this covered in SQL 2008?


And robots will rule the earth.

SkyNet auto-indexes like da bomb!


I’m surprised nobody has mentioned GData. While the public API is rather newer than the post, it has done this for years - Google’s databases have completely automatic indexes. They are also denormalized for performance, duplicating data while disallowing joins.


My first analysis with SQL Tuning Advisor happened to be really bad advice. It ended up the largest table needed a different clustered index than the obvious one and it was not smart enough to figure it out.


I agree with the general idea, but the Index Clippy would need to watch for a while to give good advice, and it would need to be run under the right operating mode. Auto Indexes generated during development or initial data load would probably be detrimental when the db was actually in production.

The biggest consideration is that indexes can considerably impact insert and update performance, so unnecessary indexes can really hurt you. Maybe Auto Indexes could be marked as such, so the Index Clippy could delete indexes it had previously created but were no longer needed.

My fear would be that fully automatic indexes would be about as good as Word HTML.


And regarding all the comments about expensive worthless DBA’s - data is incredibly important and valuable to most businesses, so it makes sense to spend on it. And while I’ve worked with plenty of worthless, overpaid data trolls, I’ve also had the pleasure to work with some brilliant DBA’s who more than earned their pay. A good DBA’s like a plumber - when you need one, you really need one.


Then you have to worry about too many indexes that hinder the performance of data changes. There is a price for everything.


Wow! 7 year thread bump.