17:04
Unknown
Index Architecture:
A. Clustered index
Clustered indexes sort and store the data rows in the table or view based on their key values.
There can be only one clustered index per table because the rows themselves can be sorted in only one order.
Advantages
It increases the speed while we are trying to retrieve the data.
Limitations
Insertion and deletion will become a slow process as it uses physical index.
B. Non clustered index
It provides the logical index contains the non clustered index key values and each key value entry has a pointer to the data row that contains the key value.
We can have more than one non clustered index for a single table.
C. Unique Indexes
A unique index ensures that the indexed column contains no duplicate values. In the case of multicolumn unique indexes, the index ensures that each combination of values in the indexed column is unique. For example, if a unique index full_name is created on a combination of last_name, first_name, and middle_initial columns, no two people could have the same full name in the table.
Both clustered and nonclustered indexes can be unique. Therefore, provided that the data in the column is unique, you can create both a unique clustered index and multiple-unique nonclustered indexes on the same table.
A. Clustered index
Clustered indexes sort and store the data rows in the table or view based on their key values.
There can be only one clustered index per table because the rows themselves can be sorted in only one order.
Advantages
It increases the speed while we are trying to retrieve the data.
Limitations
Insertion and deletion will become a slow process as it uses physical index.
B. Non clustered index
It provides the logical index contains the non clustered index key values and each key value entry has a pointer to the data row that contains the key value.
We can have more than one non clustered index for a single table.
C. Unique Indexes
A unique index ensures that the indexed column contains no duplicate values. In the case of multicolumn unique indexes, the index ensures that each combination of values in the indexed column is unique. For example, if a unique index full_name is created on a combination of last_name, first_name, and middle_initial columns, no two people could have the same full name in the table.
Both clustered and nonclustered indexes can be unique. Therefore, provided that the data in the column is unique, you can create both a unique clustered index and multiple-unique nonclustered indexes on the same table.
Posted in:
SQL Server Tutorial
0 comments:
Post a Comment