One of the first questions I got while interviewing for a potential position as a DBA was “If there is performance problem, where is the first place you would look?”
Now at this time, I was trying to move from Software Developer to DBA, and I answered that I would look at IO, Network and errors in code. The interviewer said, “Indexes.” I always assumed that all database designers knew you needed good indexes. This changed my thinking about what I was learning to start reading more about being a DBA. SQLServerCentral.Com became my friend and I started reading one article a day and answering the Question of the Day every morning when I got to work.
1. Clustered Index
What is a clustered index? Or better yet, what do I need to know about a clustered index? The first thing to know is that you can have only one. The logical structure of the table becomes the order of the column(s) you use in a clustered index. You might hear someone say that the primary key is always the clustered index. This is not true. Any index can be the clustered index. By default, the primary key becomes the clustered index unless you change this in a script or the User Interface creating/modifying a table.
ALTER TABLE [Person].[Address]
ADD CONSTRAINT [PK_Address_AddressID]
PRIMARY KEY CLUSTERED
( [AddressID] ASC )
ON [PRIMARY]
GO
2. Non-Clustered Index
You can have more than one non-clustered index. I would suggest creating a clustered index before creating any non-clustered indexes. The clustered index column(s) become part of the non-clustered index. A query plan might need to go to the clustered index to retrieve more columns not available in the non-clustered index after a scan or search of non-clustered index. The non-clustered index can be one or more columns, and the order is important. You want the most specific column (as far as values go) as the first column. It also important to have the first column(s) to be used in the join or where clause that you are trying to be used for a query.
CREATE NONCLUSTERED INDEX
[idxAddress_AddressLine12CistyStateZip]
ON [Person].[Address]
( [AddressLine1] ASC, [AddressLine2] ASC, [City] ASC,
[StateProvinceID] ASC, [PostalCode] ASC)
ON [PRIMARY]
3. Include Columns (i.e. Covering Index)
This is where Microsoft started shining in my opinion. The include columns in a non-clustered index can be these columns that your query is trying to retrieve from the clustered index mentioned above. Instead of retrieving more columns from the clustered index, the include columns are the ones the query needs. The are stored in the Data Page of the non-clustered index and not in the index tree. Be careful not to go overboard with include columns, because you can eventually create a whole new table (even though it is an index) by including all the columns not specified in the index itself. I have also seen a form of Deadlock with Include Column indexes.
CREATE NONCLUSTERED INDEX
[idxAddress_State_IncludeColumns]
ON [Person].[Address]
( [StateProvinceID] ASC)
INCLUDE ([AddressLine1], [AddressLine2], [City], [PostalCode])
ON [PRIMARY]
4. Filtered Indexes
Filtered Indexes should be used carefully because they could become more of a performance problem with Insert/Update/Delete statements. They are really nice for large data warehouses where some tables are not structure. Also, I have seen them used for a column that has one value other than a empty value. Filtered indexes are new to SQL Server, so you should see performance improvements in future versions.
CREATE NONCLUSTERED INDEX
[idxAddress_State_Filtered]
ON [Person].[Address]
(AddressLine1, [AddressLine2], [City], [PostalCode])
WHERE [StateCode] = 'LA'
ON [PRIMARY]
5. Missing Index Feature
Now, this is really cool. With SQL Server 2008, the Query Plan now includes a Missing Index feature which basically gives you a hint on what index might be beneficial. Again, you should be cautious when it suggests include columns that look like all the columns in the table. See number 3 above.
In conclusion, indexes are a great help with query performance. There are some basics shown above to get you started with understanding indexes. Do not close your mind to more information about indexes after you start to understand what was mentioned in this article. I know today after doing DBA work for over 10 years, there is always more to learn.
