An SQL database developer should be aware that the clustered index is the most important index for any table, so it should be crafted with care. As a general guideline, every table should have at least a clustered index. In some cases, clustered indexes are created automatically. In SQL Server, for instance, defining a primary key for a table will automatically create a clustered database index for that table. However, once a clustered database index has been created for a primary key, it is no longer editable. If the database developer wishes to configure the columns that appear in this database index when dealing with primary keys, it might be a better idea to remove the primary key and create an unclustered index on the former primary key column with the unique constraint enabled. This allows the database developer to create a more custom-tailored clustered index.
Creating a proper clustered database index is important, but not all clustered database indexes are created equal.
Creating a proper clustered index is important, but not all clustered indexes are created equal. Take, for example, a phone book. If the listings are ordered by city, then by last name, then by first name, it is not difficult to find out how many phone numbers are listed for a particular city. If, however, the listings are ordered by last name, then first name, then city, we would have to go through the whole phone book and count every listing with the desired city—not a fun task for anyone.
The same idea applies to databases. Order the columns in your clustered index based on your business logic. Group them in some way that makes sense for your software application. If your queries are most frequently searching for all customers who have a particular type of vehicle, set the vehicle column as the first column in your clustered index and the customer ID as the second. Customers are both logically and physically (on disk) grouped by their vehicle type. Carefully crafting these database index strategies will improve your database optimization techniques.
The image above shows an existing clustered index for a Customer table in SQL Server 2005. Since our database keeps track of customer records for many different car dealerships, the customers are grouped by the dealership and then by their customer ID for optimal selection performance.
