Creates an index on a given table or view.
Only the table or view owner can create indexes on that table. The owner of a table or view can create an index at any time, whether or not there is data in the table. Indexes can be created on tables or views in another database by specifying a qualified database name.
What is unique index?
Creates a unique index (one in which no two rows are permitted to have the same index value) on a table or view. A clustered index on a view must be UNIQUE.
Can we create index on table with existing data?
Microsoft® SQL Server™ checks for duplicate values when the index is created (if data already exists) and checks each time data is added with an INSERT or UPDATE statement. If duplicate key values exist, the CREATE INDEX statement is canceled and an error message giving the first duplicate is returned. Multiple NULL values are considered duplicates when UNIQUE index is created.
Which situation create unique index fail?
SQL Server does not allow the creation of a unique index on columns that already include duplicate values, whether or not IGNORE_DUP_KEY is set. If attempted, SQL Server displays an error message; duplicates must be eliminated before a unique index can be created on the column(s).
What is clustered index?
How many clustered index can we have per table?
CLUSTERED
Creates an object where the physical order of rows is the same as the indexed order of the rows, and the bottom (leaf) level of the clustered index contains the actual data rows. A table or view is allowed one clustered index at a time.
Why do we need to create clustered index first before creating nonclustered index?
Create the clustered index before creating any nonclustered indexes. Existing nonclustered indexes on tables are rebuilt when a clustered index is created.
If CLUSTERED is not specified, a nonclustered index is created.
Note Because the leaf level of a clustered index and its data pages are the same by definition, creating a clustered index and using the ON filegroup clause effectively moves a table from the file on which the table was created to the new filegroup. Before creating tables or indexes on specific filegroups, verify which filegroups are available and that they have enough empty space for the index. It is important that the filegroup have at least 1.2 times the space required for the entire table.
What is NONCLUSTERED index?
Creates an object that specifies the logical ordering of a table. With a nonclustered index, the physical order of the rows is independent of their indexed order. The leaf level of a nonclustered index contains index rows. Each index row contains the nonclustered key value and one or more row locators that point to the row that contains the value. If the table does not have a clustered index, the row locator is the row's disk address. If the table does have a clustered index, the row locator is the clustered index key for the row.
How many nonclustered index we can have per table?
Each table can have as many as 249 nonclustered indexes (regardless of how they are created: implicitly with PRIMARY KEY and UNIQUE constraints, or explicitly with CREATE INDEX). Each index can provide access to the data in a different sort order.
Is it possible to create nonclustered index on views?
For indexed views, nonclustered indexes can be created only on a view with a clustered index already defined. Thus, the row locator of a nonclustered index on an indexed view is always the clustered key of the row.
Can we create nonclustered indexes on views?
A unique clustered index must be created on a view before any nonclustered index is created.
Why do we need to create indexes on view?
Indexed views may be used by the query optimizer in SQL Server Enterprise or Developer edition to speed up the query execution. The view does not need to be referenced in the query for the optimizer to consider that view for a substitution.
When creating indexed views or manipulating rows in tables participating in an indexed view, seven SET options must be assigned specific values.
The SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, and ANSI_WARNING must be ON. The SET option NUMERIC_ROUNDABORT must be OFF.
If any of these settings is different, data modification statements (INSERT, UPDATE, DELETE) on any table referenced by an indexed view fail and SQL Server raises an error listing all SET options that violate setting requirements.
In addition, for a SELECT statement that involves an indexed view, if the values of any of the SET options are not the required values, SQL Server processes the SELECT without considering the indexed view substitution. This ensures correctness of query result in cases where it can be affected by the above SET options.
Which are the datatypes not supported by index?
Columns consisting of the ntext, text, or image data types cannot be specified as columns for an index. In addition, a view cannot include any text, ntext, or image columns, even if they are not referenced in the CREATE INDEX statement.
What is a composite index?
Composite indexes are used when two or more columns are best searched as a unit or if many queries reference only the columns specified in the index
What is constraint when defining composite indexes?
As many as 16 columns can be combined into a single composite index. All the columns in a composite index must be in the same table.
The maximum allowable size of the combined index values is 900 bytes. That is, the sum of the lengths of the fixed-size columns that make up the composite index cannot exceed 900 bytes
What is fillfactor?
Specifies a percentage that indicates how full SQL Server should make the leaf level of each index page during index creation.
When an index page fills up, SQL Server must take time to split the index page to make room for new rows, which is quite expensive. For update-intensive tables, a properly chosen FILLFACTOR value yields better update performance than an improper FILLFACTOR value. The value of the original FILLFACTOR is stored with the index in sysindexes.
When FILLFACTOR is specified, SQL Server rounds up the number of rows to be placed on each page.
For example, issuing CREATE CLUSTERED INDEX ... FILLFACTOR = 33 creates a clustered index with a FILLFACTOR of 33 percent. Assume that SQL Server calculates that 5.2 rows is 33 percent of the space on a page. SQL Server rounds so that six rows are placed on each page.
Notes about fillfactor
An explicit FILLFACTOR setting applies only when the index is first created. SQL Server does not dynamically keep the specified percentage of empty space in the pages.
User-specified FILLFACTOR values can be from 1 through 100. If no value is specified, the default is 0. When FILLFACTOR is set to 0, only the leaf pages are filled. You can change the default FILLFACTOR setting by executing sp_configure.
Use a FILLFACTOR of 100 only if no INSERT or UPDATE statements will occur, such as with a read-only table. If FILLFACTOR is 100, SQL Server creates indexes with leaf pages 100 percent full. An INSERT or UPDATE made after the creation of an index with a 100 percent FILLFACTOR causes page splits for each INSERT and possibly each UPDATE.
Smaller FILLFACTOR values, except 0, cause SQL Server to create new indexes with leaf pages that are not completely full. For example, a FILLFACTOR of 10 can be a reasonable choice when creating an index on a table known to contain a small portion of the data that it will eventually hold. Smaller FILLFACTOR values also cause each index to take more storage space.
The following table illustrates how the pages of an index are filled up if FILLFACTOR is specified.
FILLFACTOR Intermediate page Leaf page
0 percent One free entry 100 percent full
1 - 99 percent One free entry <= FILLFACTOR percent full
100 percent One free entry 100 percent full
One free entry is the space on the page that can accommodate another index entry.
Important Creating a clustered index with a FILLFACTOR affects the amount of storage space the data occupies because SQL Server redistributes the data when it creates the clustered index
What is IGNORE_DUP_KEY?
Controls what happens when an attempt is made to insert a duplicate key value into a column that is part of a unique clustered index. If IGNORE_DUP_KEY was specified for the index and an INSERT statement that creates a duplicate key is executed, SQL Server issues a warning and ignores the duplicate row.
If IGNORE_DUP_KEY was not specified for the index, SQL Server issues an error message and rolls back the entire INSERT statement.
The table shows when IGNORE_DUP_KEY can be used.
Index type Options
Clustered Not allowed
Unique clustered IGNORE_DUP_KEY allowed
Nonclustered Not allowed
Unique nonclustered IGNORE_DUP_KEY allowed
When dropping clustered indexes what happen to the clustering keys?
What is DROP_EXISTING?
Specifies that the named, preexisting clustered or nonclustered index should be dropped and rebuilt. The index name specified must be the same as a currently existing index. Because nonclustered indexes contain the clustering keys, the nonclustered indexes must be rebuilt when a clustered index is dropped. If a clustered index is recreated, the nonclustered indexes must be rebuilt to take the new set of keys into account.
What is the advantange of drop_existing?
The DROP_EXISTING clause enhances performance when re-creating a clustered index (with either the same or a different set of keys) on a table that also has nonclustered indexes.
The DROP_EXISTING clause replaces the execution of a DROP INDEX statement on the old clustered index followed by the execution of a CREATE INDEX statement for the new clustered index. The nonclustered indexes are rebuilt once, and only if the keys are different.
If the keys do not change (the same index name and columns as the original index are provided), the DROP_EXISTING clause does not sort the data again. This can be useful if the index must be compacted.
A clustered index cannot be converted to a nonclustered index using the DROP_EXISTING clause;
however, a unique clustered index can be changed to a non-unique index, and vice versa.
Notes about index space:
Space is allocated to tables and indexes in increments of one extent (eight 8-kilobyte pages) at a time. Each time an extent is filled, another is allocated.
Indexes on very small or empty tables will use single page allocations until eight pages have been added to the index and then will switch to extent allocations. For a report on the amount of space allocated and used by an index, use sp_spaceused.
Creating a clustered index requires space available in your database equal to approximately 1.2 times the size of the data.
This is space in addition to the space used by the existing table; the data is duplicated in order to create the clustered index, and the old, nonindexed data is deleted when the index is complete.
When using the DROP_EXISTING clause, the space needed for the clustered index is the amount of space equal to the space requirements of the existing index.
The amount of additional space required also may be affected by the FILLFACTOR specified.
The CREATE INDEX statement is optimized like any other query. The SQL Server query processor may choose to scan another index instead of performing a table scan to save on I/O operations
How do you report index?
To display a report on an object's indexes, execute sp_helpindex.
Can we create indexes and triggers on temporary table?
Indexes can be created on a temporary table. When the table is dropped or the session ends, all indexes and triggers are dropped.
What is Variable type columns in indexes?
The maximum size allowed for an index key is 900 bytes, but SQL Server 2000 allows indexes to be created on columns that may have large variable type columns with a maximum size greater than 900 bytes.
During index creation, SQL Server checks the following conditions:
The sum of all fixed data columns that participate in the index definition must be less or equal to 900 bytes.
When the index to be created is composed of fixed data columns only, the total size of the fixed data columns must be less or equal to 900 bytes. Otherwise, the index will not be created and SQL Server will return an error.
If the index definition is composed of fixed- and variable-type columns, and the fixed-data columns meet the previous condition (less or equal to 900 bytes), SQL Server still checks the total size of the variable type columns.
If the maximum size of the variable-type columns plus the size of the fixed-data columns is greater than 900 bytes, SQL Server creates the index, but returns a warning to the user.
The warning alerts the user that if subsequent insert or update actions on the variable-type columns result in a total size greater than 900 bytes, the action will fail and the user will get a run-time error.
Likewise, if the index definition is composed of variable-type columns only, and the maximum total size of these columns is greater than 900 bytes, SQL Server will create the index, but return a warning.
What needs to be Considerations when indexing computed columns and views?
In SQL Server 2000, indexes also can be created on computed columns and views. Creating a unique clustered index on a view improves query performance because the view is stored in the database in the same way a table with a clustered index is stored.
Creation of an index on a computed column or view may cause the failure of an INSERT or UPDATE operation that previously worked. Such a failure may take place when the computed column results in arithmetic error
What are the Restrictions on indexed views?
The SELECT statement defining an indexed view must not have the TOP, DISTINCT, COMPUTE, HAVING, and UNION keywords. It cannot have a subquery.
The SELECT list may not include asterisks (*), 'table.*' wildcard lists, DISTINCT, COUNT(*), COUNT(
Nonaggregate SELECT lists cannot have expressions. Aggregate SELECT list (queries that contain GROUP BY) may include SUM and COUNT_BIG(
Complex aggregation using AVG cannot participate in the SELECT list of the indexed view.
However, if a query uses such aggregation, the optimizer is capable of using this indexed view to substitute AVG with a combination of simple aggregates SUM and COUNT_BIG.
A column resulting from an expression that either evaluates to a float data type or uses float expressions for its evaluation cannot be a key of an index in an indexed view or on a computed column in a table. Such columns are called nonprecise. Use the COLUMNPROPERTY function to determine if a particular computed column or a column in a view is precise.
Indexed views are subject to these additional restrictions:
The creator of the index must own the tables. All tables, the view, and the index, must be created in the same database.
The SELECT statement defining the indexed view may not contain views, rowset functions, inline functions, or derived tables. The same physical table may occur only once in the statement.
In any joined tables, no OUTER JOIN operations are allowed.
No subqueries or CONTAINS or FREETEXT predicates are allowed in the search condition.
If the view definition contains a GROUP BY clause, all grouping columns as well as the COUNT_BIG(*) expression must appear in the view's SELECT list. Also, these columns must be the only columns in the CREATE UNIQUE CLUSTERED INDEX clause.
Is DROP INDEX statement drop indexes created by primary key or unique key?
Removes one or more indexes from the current database.
The DROP INDEX statement does not apply to indexes created by defining PRIMARY KEY or UNIQUE constraints (created by using the PRIMARY KEY or UNIQUE options of either the CREATE TABLE or ALTER TABLE statements, respectively).
Notes about Drop index statement:
After DROP INDEX is executed, all the space previously occupied by the index is regained. This space can then be used for any database object.
DROP INDEX cannot be specified on an index on a system table.
To drop the indexes created to implement PRIMARY KEY or UNIQUE constraints, the constraint must be dropped. For more information about dropping constraints, see "ALTER TABLE" in this volume.
Nonclustered indexes have different pointers to data rows depending on whether or not a clustered index is defined for the table.
If there is a clustered index the leaf rows of the nonclustered indexes use the clustered index keys to point to the data rows.
If the table is a heap, the leaf rows of nonclustered indexes use row pointers. If you drop a clustered index on a table with nonclustered indexes, all the nonclustered indexes are rebuilt to replace the clustered index keys with row pointers
.
Similarly, when the clustered index of an indexed view is dropped, all nonclustered indexes on the same view are dropped automatically.
Sometimes indexes are dropped and re-created to reorganize the index, for example to apply a new fillfactor or to reorganize data after a bulk load. It is more efficient to use CREATE INDEX and the WITH DROP_EXISTING clause for this, especially for clustered indexes. Dropping a clustered index causes all the nonclustered indexes to be rebuilt.
If the clustered index is then re-created, the nonclustered indexes are rebuilt once again to replace the row pointers with clustered index keys.
The WITH DROP_EXISTING clause of CREATE INDEX has optimizations to prevent this overhead of rebuilding the nonclustered indexes twice. DBCC DBREINDEX can also be used and has the advantage that it does not require that the structure of the index be known.
What is DBCC DBREINDEX
Rebuilds one or more indexes for a table in the specified database.
Notes about DBCC DBREINDEX:
DBCC DBREINDEX rebuilds an index for a table or all indexes defined for a table. By allowing an index to be rebuilt dynamically, indexes enforcing either PRIMARY KEY or UNIQUE constraints can be rebuilt without having to drop and re-create those constraints.
This means an index can be rebuilt without knowing the table's structure or constraints, which could occur after a bulk copy of data into the table.
If either index_name or fillfactor is specified, all preceding parameters must also be specified.
DBCC DBREINDEX can rebuild all of the indexes for a table in one statement, which is easier than coding multiple DROP INDEX and CREATE INDEX statements.
Because the work is done by one statement, DBCC DBREINDEX is automatically atomic, while individual DROP INDEX and CREATE INDEX statements would have to be put in a transaction to be atomic.
Also, DBCC DBREINDEX can take advantage of more optimizations with DBCC DBREINDEX than it can with individual DROP INDEX and CREATE INDEX statements.
DBCC DBREINDEX is not supported for use on system tables.
No comments:
Post a Comment