When designing a clustered index, you should consider that some data types are generally better than other data types to be used as clustering keys. In addition, the IDENTITY integer values are narrow, due to its small size, unique, if you enforce the column uniqueness with a constraint, and static, as they are generated automatically by the system and not visible to the users.
Although the GUIDs values, that are stored in the uniqueidentifier columns, are commonly used as clustered index key, there are some challenges that accompany that design. The main challenge that affects the clustered index key sorting performance is the nature of the GUID value that is larger than the integer data types, with 16 bytes size, and that it is generated in random manner, different from the IDENTITY integer values that are increasing continuously.
The large size and randomness generation of the GUID values will always lead to the page splitting and index fragmentation problems, which negatively affect the clustered index usage performance. The Character data types can be also used, but not recommended, as clustered index keys. This is due to the limited sorting performance of the character data types, the large size, non-increasing values, non-static values that often tend to change in the business applications and not compared as binary values during the sorting process, as the characters comparison mechanism depends on the used collation.
Even though the Date data types are not unique, it has a small size and provides good sorting performance, especially for the queries that search for data ranges.
SQL Server allows you to specify the type of the index that will be created automatically when you create a UNIQUE constraint to be clustered index, if there is no clustered index created on that table, due to the fact that, only one clustered index can be created per each table.
Having no clustered index defined in the previous table, we can create a clustered index using the SQL Server Management Studio by browsing the table on which we need to create the clustered index on, then right-click on the Indexes node under that table and from the New Index option choose the clustered index type, as shown below:. From the displayed New Index dialog box, the name of the table on which the index will be created, and the type of the index will be filled automatically.
You need to provide the name of the index, following your company naming convention, the uniqueness of that index key values and from the Add button you can choose the column or list of columns that will participate in that index key, as shown below:. You can also perform the same task from the Table Designer, by right-clicking on the table on which the index will be created and choose Design option as below:.
From the appeared dialog box, click on the Add bottom to add a new clustered index, by setting Create As Clustered to Yes, Specify the index name, the clustered index key uniqueness and the list of columns, with the proper sorting order, that will be included in the clustered index key, as shown below:. Next Continue. Home Testing Expand child menu Expand. SAP Expand child menu Expand. Web Expand child menu Expand. Must Learn Expand child menu Expand.
Big Data Expand child menu Expand. Live Project Expand child menu Expand. Note, that SQL server sorts the indexes efficiently by using a B-tree structure. This is a tree data structure that allows SQL Server to keep data sorted, to allow searches, sequential access, insertions and deletions, in a logarithmic amortized time.
This methodology minimizes the number of pages accessed, in order to locate the desired index key, therefore resulting in an improved performance. A heap table has not its data sorted. In the case of a clustered index, the data are sorted on the key values columns of the index.
The SQL server is now able to locate the data by navigating down from the root node, to the branch and finally to the leaf nodes of the B-tree structure of the index. The later approach is much faster, when you want to filter or sort the data you want to retrieve. A non-clustered index, on the other hand, is a completely different object in the table. It contains only a subset of the columns. Furthermore a non-clustered index can be allocated to a different FileGroup, which can utilize a different physical storage in order to improve performance even more.
Such statements can take longer to execute, in the presence of indexes, as it alters the data in the table, thus to the indexes too. It has to add new rows in a table with a clustered index. In such case the table rows may need to be re-positioned!
The clustered index needs to order the data pages themselves! Since non-clustered indexes are located in a separate location, there can be multiple non-clustered indexes per table. The rest of the syntax remains the same as the syntax for creating a clustered index. The above script will create an index which contains the names of the patients and the address of their corresponding records as shown below:. Since a database has to make two searches, first in the non-clustered index and then in the actual table, non-clustered indexes can be slower for search operations.
This is pretty obvious. If you need to create multiple indexes on your database, go for non-clustered index since there can be only one clustered index.
If you want to select only the index value that is used to create and index, non-clustered indexes are faster. However, if you want to select other column values such as age, gender using the name index, the SELECT operation will be slower since first the name will be searched from the index and then the reference to the actual table record will be used to search the age and gender.
On the other hand, with clustered indexes since all the records are already sorted, the SELECT operation is faster if the data is being selected from columns other than the column with clustered index. Rather only the non-clustered index needs updating. Since, non-clustered indexes are stored at a separate location than the original table, non-clustered indexes consume additional disk space.
If disk space is a problem, use a clustered index. The primary key column is an ideal candidate for a clustered index. Acuity has offices in London and Guildford, Surrey. Cloud Monitoring Smarter.
0コメント