Data Retrieval Methods within databases:
Method 1: Sequential access method, by searching through each individual record looking for a match. Unindexed table AKA Full-table scan
Method 2: Direct Access Method. Use of Index(es) on a table.
What are Indexes?
An Index is a way of presenting data differently than the way it appears on the disk(within a SQL Table). Special types of Indexes reorder the records physical location within a table. An index can be created and assigned to a single column or multiple columns depending on your database implementation and what’s supported by your RDBMS.
An Index is very similar to the Order by clause however the key differences are:
- Order By Clause Re-sorts and orders the data each time you execute the corresponding SQL Statment.
- When using an Index, the database system creates a physical index object and reuses the same index each time you query the table.
*** Please note that SQL indexes require physical storage and resides on the disk hard drive.
Is there an appropriate and inappropriate time to use indexes?
The answer is yes. Indexes exist to assist with query optimization. Indexes if used incorrectly can cause additional overhead to your query. It’s important to analyze your Table and understand the best and worst case to use/remove an index.
Appropriate times to use Indexes:
- Indexes yield the greatest improvement when the columns you have indexed contain a wide variety of data.
- Indexes can optimize your queries when those queries are returning a small amount of data
- Always index fields that are used in joins between tables. This technique can greatly increase the speed of a join.
Inappropriate times to use Indexes:
- Indexes can improve the speed of data retrieval, however they cause slow data updates. When performing large inserts, updates, deletes, you may want to drop the index before doing so and adding it back after success.
- For small tables, the use of indexes does not result in any performance improvement.
- Indexes should not be used on columns that contain high number of NULL values.
- Do not index on fields that are manipulated frequently. This will result with additional overhead.
- Indexes take up space within your database. If you’re trying to manage space, then be aware of the space being allocated and used by your indexes.
Why would you use an Index?
Indexes are typically used for three primary reasons:
1.) To enforce referential integrity.
2.) To facilitate the ordering of data based on the contents of the indexes field or fields.
3.) To optimize the execution speed of queries.
Syntax to Create Indexes on SQL Server:
CREATE INDEX NameOfIndex
ON TableName(Column1)
You also have the capability to store the column information in ASC or DESC order. Indexes help sort all of the information in the tablebased on the column you’re sorting on.
CREATE INDEX NameOfIndex
ON TableName(Column1 ASC) -- No need to specify ASC for ascending order since this is the default behavior.
CREATE INDEX NameOfIndex
ON TableName(Column1 DESC) -- Sort the column information in descending order.
NOTE: It’s practical to create an index on the primary key or key columns in your table that link you to other tables.
Composite Indexes:
You can also create indexes on more than one column. This is called a Composite index or a covering index.
Here’s the Syntax:
CREATE INDEX IndexName
ON TableName (Column1, Column2)
Composite indexes are usually used with the UNIQUE keyword to rectify columns with unique data. As described earlier, it’s best to use Indexes on columns of a table with unique values.
CREATE UNIQUE INDEX IndexName
ON TableName(Column1, Column2)
Syntax to Drop Indexes on SQL Server:
To drop an index you will use the following syntax:
DROP INDEX NameOfIndex
ON TableName
NOTE: When a table is dropped, all indexes associated with that table is/are dropped.
Disadvantages to Indexes:
1.) Requires additional storage space
2.) Indexes require maintenance
3.) Performance decreases with Data Updates(Insert, Update, delete)