Index Tuning

Several years ago, in an effort to develop wizards to help new database users, Microsoft gathered many tables from wide range of users.

One (horrifying) realization was that the overwhelming majority of the tables collected did not have a primary key. From a database perspective this is very troubling… but what if a database was not your introduction in data storage? Much of the data had been simply imported from spreadsheets, where consistency of data… and speed of data retrieval is not paramount.

Subsequent to this analysis, the Microsoft Access Table Analyzer was born. At least it gives new users some advice by looking at the structures and data in their tables and makes some reasonable suggestions.

The reason we bring this up is that we still get to see table design that is far from optimum. Granted, there is a lot of personal style in the way database schema is developed, and it ultimately should be task oriented… get the job at hand (and future jobs) done.

We have prepared a document that you can download that focuses on the use of indexes in database schemas (a description of database in terms of its data definition language). Particular attention is paid to the use of surrogate keys in table design.

The text was first presented at the DevCon '99 conference in Florida, and there have been many subsequent requests (mostly from folk in the SQLServer community) to make it available.

The document is be both philosophical and practical… perhaps giving you some things to think about for existing and future projects using Microsoft SQLServer and/or Microsoft Access tables.

The document also cover suggestions for applying these schema techniques in Microsoft Access applications.

Download the Index Tuning document here.

Revised: December 13, 2017