SQL Index
An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes; they are just used to speed up queries. Effective indexes are one of the best ways to improve performance in a database application. A table scan happens when there is no index available to help a query. In a table scan, the SQL Server examines every row in the table to satisfy the query results. Table scans are sometimes unavoidable, but on large tables, scans have a terrific impact on performance.Index in sql is created on existing tables to retrieve the rows quickly.
When there are thousands of records in a table, retrieving information will take a long time. Therefore indexes are created on columns which are accessed frequently, so that the information can be retrieved quickly. Indexes can be created on a single column or a group of columns. When a index is created, it first sorts the data and then it assigns a ROWID for each row.
Syntax to create Index:
CREATE INDEX index_name
ON table_name (column_name1,column_name2...);
Syntax to create SQL unique Index:
CREATE UNIQUE INDEX index_name
ON table_name (column_name1,column_name2...);
- index_name is the name of the INDEX.
- table_name is the name of the table to which the indexed column belongs.
- column_name1, column_name2.. is the list of columns which make up the INDEX.
Implicit Indexes:
They are created when a column is explicity defined with PRIMARY KEY, UNIQUE KEY Constraint.Explicit Indexes:
They are created using the "create index.. " syntax.NOTE:
1) Even though sql indexes are created to access the rows in the table quickly, they slow down DML operations like INSERT, UPDATE, DELETE on the table, because the indexes and tables both are updated along when a DML operation is performed. So use indexes only on columns which are used to search the table frequently.
2) Is is not required to create indexes on table which have less data.
3) In oracle database you can define up to sixteen (16) columns in an INDEX.
So, why do you need to index your tables?
ReplyDeleteBecause without an index SQL server has to scan entire tables to return requested data. It is like the index page in a book. You check for the keyword you want to read about in the index and you jump directly to the page were the content belongs, instead of scanning page by page for the material you want to read.
Similarly a table index allows you to locate data without the need to scan the entire table. You create indexes on one or more columns in a table to help SQL server find the data quickly in a query.
ndex Types
There are two main index types; Clustered index and Non-Clustered index.
A clustered index alters the way that the rows are stored. When you create a clustered index on a column (or a number of columns), SQL server sorts the table’s rows by that column(s). It is like a dictionary, where all words are sorted in alphabetical order in the entire book. Since it alters the physical storage of the table, only one clustered index can be created per table. In the above example the entire rows are sorted by computer_id since a clustered index on computer_id column has been created.
CREATE CLUSTERED INDEX [IX_CLUSTERED_COMPUTER_ID]
ON [dbo].[nics] ([computer_id] ASC)
A non-clustered index, on the other hand, does not alter the way the rows are stored in the table. It creates a completely different object within the table that contains the column(s) selected for indexing and a pointer back to the table’s rows containing the data. It is like an index in the last pages of a book, where keywords are sorted and contain the page number to the material of the book for faster reference. A non-clustered index on the computer_id in the previous example would look like the table below:
CREATE NONCLUSTERED INDEX [IX_NONCLUSTERED_COMPUTER_ID]
ON [dbo].[nics] ([computer_id] ASC)
More: reference:
ReplyDeletehttp://www.itbully.com/articles/sql-indexing-and-performance-part-2-clustered-and-non-clustered
http://stackoverflow.com/questions/5070529/difference-between-clustered-and-nonclustered-index