When it comes to retrieving data from your tables, indexes play a fundamental role in finding that data you require and finding it fast. Pretty much every table you create will have an index of one type or another; whether it is a regular index, a primary key or a foreign key.
When creating your tables, it is often the case that the table will have a unique identifier of one sort or another to allow you to uniquely identify a specific row within a table. The column can either be a numeric value like an auto incrementing integer, a unique identifier (a GUID) or even a character based value like a CHAR(#). You have added this column so that you can access all the data in a specific row and, as it stands, you can just use the WHERE clause to have SQL Server search through the rows to find the value request. However, you can greatly improve the performance of this type of request by adding a Primary Key to the table that indexes this column. Things you should be aware of when creating a primary key:
A foreign key is used to create an index bridge between two tables (also known as a relationship but has nothing to do with relational data in the true sense). When a foreign key is available, the server is able to join two tables together much faster than without. Here are some things you should know about foreign keys:
Separate indexes can be created on columns when needed. For example if your solution is likely to perform a lot of searches on a particular column. Having an index on this column will increase the performance. Be careful though with the amount of indexes that you create. Some points to remember:
That’s it for this post. As you can see, indexes are extremely useful but one should not just go banging indexes on to tables left, right and centre. SQL Management Studio has an option to include the execution script when running a command and can be quite useful as it will offer suggestions on where to place indexes and what kind of performance boost you will get from doing so (don’t just follow this blindly though as the suggestion is only based on the script it has just analysed and is not aware of the rest of the world). I hope this post is helpful and as usual, constructive critisism and suggestions are welcome.