This article is half-done without your Comment! *** Please share your thoughts via Comment ***
What is a Clustered Index?
Clustered Index stores a key value in the same data or row page. It is attached with data rows.
Clustered indexes sort and store the data rows in the table or view based on their key values.
For example, each and every book has attached one page number on each page so this called clustered index of that book.
How many clustered indexes there can be in one table?
You can create only one clustered index.
What is a Non-clustered Index?
Nonclustered indexes have a structure separate from the data rows. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value.
The pointer from an index row in a nonclustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key.
You can add nonkey columns to the leaf level of the nonclustered index to by-pass existing index key limits, 900 bytes and 16 key columns, and execute fully covered, indexed, queries.
Can you make non-clustered indexes as unique index?
Yes, you can create unique non-clustered index in which no two rows can have the same value for the index key.
What is a Hash Index?
With a hash index, data is accessed through an in-memory hash table. Hash indexes consume a fixed amount of memory, which is a function of the bucket count.
hash indexes are used is because hash tables are extremely efficient when it comes to just looking up values. So, queries that compare for equality to a string can retrieve values very fast if they use a hash index.
What is the main disadvantage of the Hash Index?
Hash tables are not sorted data structures, and there are many types of queries which hash indexes can not even help with.
For instance, suppose you want to find out all of the employees who are less than 40 years old. How could you do that with a hash table index? Well, it’s not possible because a hash table is only good for looking up key value pairs – which means queries that check for equality condition.
What is a Unique Index?
A unique index ensures that the index key contains no duplicate values and therefore every row in the table or view is in some way unique.
Uniqueness can be a property of both clustered and nonclustered indexes.
Why CLUSTERED INDEX is a Special Index in the SQL Server?
The clustered index in any table has special uses and significance. This index is the data itself, and it will be used in every nonclustered index in the table. If you are defining a new clustered index or changing a clustered index, SQL Server will need to do IO on every nonclustered index on the table as well.
T-SQL Script to create a CLUSTERED INDEX.
|
1 |
CREATE CLUSTERED INDEX Index_Name ON Schema.TableName(Column); |
T-SQL Script to create a NONCLUSTERED INDEX.
|
1 |
CREATE NONCLUSTERED INDEX Index_Name ON Schema.TableName(Column); |
T-SQL Script to create a NONCLUSTERED INDEX with covered nonkey columns.
|
1 |
CREATE NONCLUSTERED INDEX Index_Name ON Schema.TableName(Column) INCLUDE (ColumnA, ColumnB); |
What is a Filtered Index?
An optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.
What is a Full-text Index?
A special type of token-based functional index that is built and maintained by the Microsoft Full-Text Engine for SQL Server. It provides efficient support for sophisticated word searches in character string data.
What is a Spatial Index?
A spatial index provides the ability to perform certain operations more efficiently on spatial objects (spatial data) in a column of the geometry data type. The spatial index reduces the number of objects on which relatively costly spatial operations need to be applied.
What are Covering Indexes and Covered Queries in SQL Server?
If all the columns requested in the select list of query, are available in the index, then the query engine doesn’t have to lookup the table again which can significantly increase the performance of the query. Since all the requested columns are available with in the index, the index is covering the query. So, the query is called a covering query and the index is a covering index.
A clustered index can always cover a query, if the columns in the select list are from the same table.
How Indexes are used by the Query Optimizer?
Well-designed indexes can reduce disk I/O operations and consume fewer system resources therefore improving query performance. Indexes can be helpful for a variety of queries that contain SELECT, UPDATE, DELETE, or MERGE statements.
When the query optimizer uses an index, it searches the index key columns, finds the storage location of the rows needed by the query and extracts the matching rows from that location. Generally, searching the index is much faster than searching the table because unlike a table, an index frequently contains very few columns per row and the rows are in sorted order.
