The Importance of SQL Indexes and Database Relationships
A great way to help manage your database indexes and database relationships in MS SQL Server databases is to keep a database diagram up to date. The larger you database the more important optimisation becomes. Remember its alot easier to get things right the first time round than having to go back and refactor problematic code or procedures. Sometimes you can acheive large performance increases by adding required database indexes, but be careful as adding incorrect indexes can have a negative effect on performance.
When connected through Management Studio, expand the Databases list and then expand the database you wish to work on. Right-click Database Diagrams and select New Database Diagram.
An index is basically a sorted list of key values associated with the physical address of the rows that match these key values.
Add your tables to the blank canvas, you now need to add-in the database relationships of all primary and foreign key columns in your tables. Drag and drop the column names where the SQL Server relationships exist, making sure that the correct columns are assigned.
Depending on your data and queries you may also require other columns indexed or other groups of columns. If you search on the values of two columns and index only one of them the database will have to fetch all the rows that correspond to the key you sesarched, and discard the subset of these rows that don't match the other column. By indexing both columns (called a composite index) you can eliminate this redundant process and deal only with the data we really want.
Right-click on your table and select Indexes/Keys, you can now add in single or composite indexes on your required columns.