A primary key can also be the clustered index key. But a clustered index key can be another column other than the primary key. It is also the clustered index key. You can drop that clustered index. Then, create a clustered index based on Lastname , Firstname , and Middlename. The primary key is still the BusinessEntityID column.
If a column or columns appear in your WHERE clause in a lot of queries, this is a candidate for a clustered index. But another consideration is how wide the clustered index key is.
Too wide — and the size of each non-clustered index will increase if they exist. Remember that non-clustered indexes also use the clustered index key as a pointer.
So, keep your clustered index key as narrow as possible. If a large number of queries use the primary key in the WHERE clause, leave it also as the clustered index key. If not, create your primary key as a non-clustered index. But what if you are still unsure? Then, you can assess the performance benefit of a column when it is clustered or non-clustered. So, tune in to the next section about it. Good question. You need to check the logical reads and the execution plan of your queries.
Our short experiment will include copies of the following tables from the AdventureWorks database:. We have 2 copies of the Person table. The first will use BusinessEntityID as the primary and clustered index key. The second still uses BusinessEntityID as the primary key. The clustered index is based on Lastname , Firstname , Middlename , and Suffix. Then, we paste the results in statisticsparser. The reason? The table is sorted by BusinessEntityID.
Yet, the second table has the clustered index based on the name. The less logical reads, the faster the query. Something else happened based on the execution plan in Figure 4. It is not covered by any of the existing indexes. Then, SQL Server scanned it for the right last names and got the first name, middle name, and title. Remove the Title column, and the operator used will be Index Seek. Because the rest of the fields are covered by the non-clustered index based on Lastname , Firstname , Middlename , and Suffix.
It also includes BusinessEntityID as the clustered index key locator. Clustered indexes can be good for range queries. Is that always the case? The listing needs rows based on a range of BusinessEntityIDs from to Again, the clustered and non-clustered indexes of the 2 tables are intact. Clustered indexes proved their worth on range queries in this scenario. This also supports lower logical reads.
The culprit? All counters again indicate that using the EmployeeName character column as clustered index key is worse than using the ID and the GUID columns, as shown below:. The Time and IO statistics generated after executing the query will show that only 7 logical read operations are performed to retrieve the requested data based on the BirthDate range values, 0ms consumed from the CPU time and 47ms taken to execute the query.
It is clear from the result that, using the BirthDate Datetime column as clustered index key is the best choice when searching based on date range, as shown below:. Finally, if you try to drop the clustered index and create a new one using the EmployeeAddress character column:.
The SQL Server Query Optimizer decides to use the clustered index to retrieve the requested data, as it is more efficient, much faster and fewer resources consumer than scanning the whole table, in addition to having the data sorted in the index pages. Take into consideration that, when you create a clustered index on a table, all non-clustered indexes created on that heap table will be rebuilt to replace the row identifier RID with the clustered index key.
So that, it is better always to start with creating the clustered index then proceed with creating the non-clustered index over it. ID ASC. EmployeeName ASC. BirthDate ASC. EmployeeAddress ASC. Author Recent Posts. Ahmad Yaseen. Also, he is contributing with his SQL tips in many blogs.
View all posts by Ahmad Yaseen. Latest posts by Ahmad Yaseen see all. SQL Server index structure and concepts. SQL Server index design basics and guidelines. Designing effective SQL Server non-clustered indexes.
We can have as many nonclustered indexes on our tables as we want well, we max out at Create an index for every column! Essentially, every time you index some column s , you are duplicating the unique values in those column s so that they can be stored in sorted order in your index. We get speed and efficiency in our data lookups, but with the cost of losing disk space. You need to test and see for each table and set of queries what the optimal number of indexes is. Adding an additional index can absolutely destroy performance, so always test your changes!
However, if you need other columns of data from the row that you just looked up, SQL is going to have to use those index pointers to go find the rest of that row data somewhere else on disk. This can really add up and slow down performance.
What this basically does is in addition to storing the sorted values of your indexed column s , the index will also store whatever additional values you want to include as part of the index itself.
ALWAYS test your index changes, because what might improve one query may hurt another one already running on that table, and over time you will learn about all of those edge cases and how they affect index performance.
After reading each scenario, take a guess about what kind of index you would add and then read the answer to see what I would do in that scenario. Assume no indexes exist yet on these tables unless otherwise noted.
Thanks for reading.
0コメント