Indexes are vital for good database performance. Pick the correct index and your queries will be fast and responsive. Pick the wrong ones, and you’ll waste a lot of time and resources.
An index on a table is simply a way of looking up the data contained within the table. It should be as small as possible whilst still being useful.
For example, you might have a table that contains information about people. Your primary key might be a simple ID number, and you’d also store things like Forename, Surname, Date of Birth etc.
ID Forename Surname DateOfBirth 1 John Smith 1990-01-01 2 James Apple 1980-12-10 3 Phil Barns 1975-06-06 4 Mary Stokes 1987-09-23
It would be very common to query this table by searching on Surname.
SELECT Forename, Surname, DateOfBirth FROM People WHERE Surname LIKE 'A%'
If you turn on the “Include Actual Execution Plan” with this query in Sql Server Management Studio, you will see a “Table Scan”. This occurs when every record in the table must be search through to find matches. In a large table this would be very slow.
You could add a simple Index the table to speed the query up massively
CREATE INDEX IX_Surname ON People (Surname)
When this index has been created, the query optimiser will look-up the values in the index, and only then get the data from the table for the rows that match, a much faster operation.
If you want to find out the Indexes that SQL Server believes you need, then you can use the dynamic maangement view sys.dm_db_missing_index_details
Don’t rely on this view to be comprehensive, just use it as a guideline to begin investigation. Sometimes it’s a bit enthusiastic, whilst the indexes may be useful, the performance trade-off may not be ideal.
This is only a very basic introduction to indexes, I’ll be going over them in more detail in further posts. If there’s anything you want to see covered sooner rather than later, let me know and I’ll see what I can do.