Clusters are an optional method of storing table data. A cluster is a group of tables that share the same data blocks because they share common columns and are often used together.
For example, the EMP and DEPT table share the DEPTNO column. When you cluster the EMP and DEPT tables, Oracle physically stores all rows for each department from both the EMP and DEPT tables in the same data blocks.
SQL Server has two types of indexes: clustered index and non-clustered index.
Cluster index is a type of index which sorts the data rows in the table on their key values. In the Database, there is only one clustered index per table.
A clustered index defines the order in which data is stored in the table which can be sorted in only one way. So, there can be an only a single clustered index for every table. In an RDBMS, usually, the primary key allows you to create a clustered index based on that specific column.
When you create a table with a primary key, SQL Server automatically creates a corresponding clustered index based on columns included in the primary key.
For below table, a clustered index will be created automatically.
CREATE TABLE part_prices( part_id int, valid_from date, price decimal(18,4) not null, PRIMARY KEY(part_id, valid_from) );
If you add a primary key constraint to an existing table that already has a clustered index, SQL Server will enforce the primary key using a non-clustered index:
ALTER TABLE parts ADD PRIMARY KEY(part_id);
SQL Server will create a non-clustered index for the primary key.
CREATE CLUSTERED INDEX index_name
ON schema_name.table_name (column_list);
A Non-clustered index stores the data at one location and indices at another location. The index contains pointers to the location of that data. A single table can have many non-clustered indexes as an index in the non-clustered index is stored in different places.
For example, a book can have more than one index, one at the beginning which displays the contents of a book unit wise while the second index shows the index of terms in alphabetical order.
Parameters | Clustered | Non-clustered |
---|---|---|
Use for | You can sort the records and store clustered index physically in memory as per the order. | A non-clustered index helps you to creates a logical order for data rows and uses pointers for physical data files. |
Storing method | Allows you to stores data pages in the leaf nodes of the index. | This indexing method never stores data pages in the leaf nodes of the index. |
Size | The size of the clustered index is quite large. | The size of the non-clustered index is small compared to the clustered index. |
Data accessing | Faster | Slower compared to the clustered index |
Additional disk space | Not Required | Required to store the index separately |
Type of key | By Default Primary Keys Of The Table is a Clustered Index. | It can be used with unique constraint on the table which acts as a composite key. |
Main feature | A clustered index can improve the performance of data retrieval. | It should be created on columns which are used in joins. |
This article is contributed by Akd. If you like dEexams.com and would like to contribute, you can write your article here or mail your article to admin@deexams.com . See your article appearing on the dEexams.com main page and help others to learn.