Database systems generally use indices to provide fast access to relational data. An index is a separate physical data structure that enables queries to access one or more data rows fast. Proper tuning of indices is therefore a key for query performance. An index is in many ways analogous to a book index. When you […]
Read More...Clustered Indices, Nonclustered Indices – SQL Server 2012
Clustered Indices A clustered index determines the physical order of the data in a table. The Database Engine allows the creation of a single clustered index per table, because the rows of the table cannot be physically ordered more than one way. When using a clustered index, the system navigates down from the root of […]
Read More...Creating Indices (CREATE INDEX Statement) – SQL Server 2012
The CREATE INDEX statement creates an index for the particular table. The general form of this statement is
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | CREATE [UNIQUE] [CLUSTERED |NONCLUSTERED] INDEX index_name ON table_name (column1 [ASC | DESC] ,...) [ INCLUDE ( column_name [ ,... ] ) ] [WITH [FILLFACTOR=n] [[, ] PAD_INDEX = {ON | OFF}] [[, ] DROP_EXISTING = {ON | OFF}] [[, ] SORT_IN_TEMPDB = {ON | OFF}] [[, ] IGNORE_DUP_KEY = {ON | OFF}] [[, ] ALLOW_ROW_LOCKS = {ON | OFF}] [[, ]ALLOW_PAGE_LOCKS = {ON | OFF}] [[, ] STATISTICS_NORECOMPUTE = {ON | OFF}] [[, ]ONLINE = {ON | OFF}]] [ON file_group | "default"] |
index_name identifies the name of the created index. An index can be established for one or more columns of a single table (table_name). column1 is the name of the column for which the index is created. […]
Read More...Obtaining Index Fragmentation Information & Editing Index Information
During the life cycle of an index, it can become fragmented, meaning the storage of data in its pages is done inefficiently. There are two forms of index fragmentation: internal and external. Internal fragmentation specifies the amount of data, which is stored within each page. External fragmentation occurs when the logical order of the pages […]
Read More...Altering Indices & Removing and Renaming Indices- SQL Server 2012
Altering Indices The Database Engine is one of a few database systems that support the ALTER INDEX statement. This statement can be used for index maintenance activities. The syntax of the ALTER INDEX statement is very similar to the syntax of the CREATE INDEX statement. In other words, this statement allows you to change the […]
Read More...Guidelines for Creating and Using Indices – SQL Server 2012
Although the Database Engine does not have any practical limitations concerning the number of indices, it is advisable to limit them, for a couple of reasons. First, each index uses a certain amount of disk space, so it is possible that the total number of index pages could exceed the number of data pages within […]
Read More...Special Types of Indices – SQL Server 2012
The Database Engine allows you to create the following special types of indices: Indexed views Filtered indices Indices on computed columns Partitioned indices Column store indices XML indices Full-text indices Indexed views are based on views and therefore will be discussed in the next chapter.Filtered indices are similar to indexed views. You can find their […]
Read More...