This post is for T-SQL Tuesday #10 hosted by Michael J Swart twitter | blog.
This is also an introduction to indexes. The goal is for you to gain a basic understanding of indexes. The different types and why you want to use them. I will cover advantages and disadvantages of the use of indexes.
What are indexes?
Indexes in databases are like indexes in a library. They allow the information you are seeking to be retrieved quickly. If the books in the Library are stored alphabetically by book name then finding a book on SQL Server is easy. You go right to the section that begins with the letter “S”. Instead of starting a the letter “A”.
You have the option of creating an index on one column or a number of columns. Indexes are basically storage items or data structures within a database that contain information (keys). SQL Server knows how to quickly get the row or rows associated with the values in the keys.
Types of Indexes:
There are 8 different types of indexes with brief explanations.
Clustered indexes sort and store data in order based on the clustered index key. A Primary key is a good example of a clustered index. Each entry represents one row in a table. You may only have one clustered index on a table.
Nonclustered indexes can be used on tables or views having a clustered index (primary key) or on tables or views (heap) not having a clustered index defined. The nonclustered index has a key and row locator pointing to data. If a table or view does not have a clustered index defined the order of the rows in the index are not guaranteed. SQL 2005 allows up to 249 nonclustered on a table and SQL 2008 allows up to 999.
Using a unique index on a table or view will ensure the key does not contain any duplicate values and every row will be unique. You can apply a unique index on clustered and nonclustered indexes.
Index with included columns:
An index with included columns is a nonclustered index that you add additional columns to that are not covered by the key columns. You can increase performance greatly by using non-key columns covering more queries.
Full-text indexes can be created on tables or indexed views. This index can include up to 1024 columns and you can only have one is permitted per table or view. To use Full-text indexes you must setup the Full-text engine with SQL server. This type of index is primarily used for word searches in character string data.
Spatial indexes are special indexes specific to spatial data stored geometry data type columns. This index is only available in SQL 2008 and later versions.
A Filtered index is used to cover queries selecting from a defined subset of data.
Comparing this type of index with a table index, you can improve performance, reduce maintenance, and reduces index storage.
An XML index is a used on XML data type columns. These indexes cover tags, values and paths within your xml data and can improve performance.
What do we gain by using indexes?
If used properly indexes can reduce disk I/O operations and reduce the resources needed and overall improving query performance.
Indexes on tables can improve performance when selecting data from a table because if no indexes are present SQL server must look at each record in the table starting at the top until it finds what it is looking for. Imagine if it had a several thousand or several million records. You can see where performance can and will quickly go right out the window without indexes.
When designing indexes you should keep in mind performance benefits gained must not outweigh the cost of storage and resources needed for processing the requests. What this means is tables or views with indexes require more storage space. Keeping in mind certain statements that insert, update, and delete data can take longer to perform and require more processing resources to complete their tasks as well.
So, in summary, I talked about what indexes are, why we can benefit from their use and some of the costs associated with using indexes. Hopefully you have a better understanding of indexes now.