What is an Index
Index is a
database object, which can be created on one or more columns (16 Max column
combination). When creating the index will read the column(s) and forms a
relevant data structure to minimize the number of data comparisons. The index
will improve the performance of data retrieval and adds some overhead on data
modification such as create, delete and modify.
1.It contains Data Pages. That means the complete row information will be present in the Clustered Index Column.
2. Clustered Index enforces a logical order on the rows. Rows are ordered based on Clustering Key
3. If the table does not have Clustered Index it is referred to as a "Heap"
4. A Clustered Index always has Index Id of 1
5. A Table can have ONLY 1 Clustered Index
6. A Primary Key
constraint creates a Clustered Index by default
1. It only contains the Row Locator information in the form of Clustered Index column(if availabe) or the File Indentifier + Page Number + Total Rows in a Page. This means that the query engine must take an additional step in order to locate the actual data.
2. Nonclustered Index does not order actual data, It only orders columns present in the Nonclustered Index based on Index Key specified at the time of creation of Nonclustered Index.
3. A table may not have any Nonclustered Indexes
4. Nonclustered Indexes have Index Id > 1
5. Prior to SQL Server 2008 only 249 Nonclustered Indexes can be created. With SQL Server 2008 and above 999 Nonclustered Indexes can be created
6. A Unique Key constraint created a Nonclustered Index by default
Ready to start your tutorial with us? That's great! Send us an email and we will get back to you as soon as possible!