Will there ever be a good reason to replace a clustered index with a unique nonclustered index
I am clearing the database and am having heavy traffic issues. Reading and writing. But heavy traffic is reduced to just a few days a year.
I suspect one problem is that a lot of heavily used tables don't have a clustered index. But they do have a primary key and a unique, non-clustered index tied to it that is only on the primary key (no inclusions).
Is this a helpful approach anytime?
My guess: someone thought it would be faster if the db didn't need to insert all the columns from the clustered index (I would never do this, so I don't know the madness behind this method).
I am starting the challenge of applying clustered indexes on these tables, but wanted to do some smarter thought before I went too far to see if there is a scenario where such a strategy would help?
I would think the loss of clustered index connections outweighs any gain you might have?
source to share
I feel pretty confident saying no, this is not a profitable approach, ever. If the table only has nonclustered indexes, it is a heap table and apart from bulk insert operations there is no good reason to have a heap table. It's not just me saying that Microsoft's opinion is also .
I kept a large warehouse where it was nice not to have a primary key at all (because the index for it was taking up a huge amount of space without being used), but only a unique clustered index on DATETIME
plus a ID
. But you will notice whether the primary key or not, it had a clustered index, because not having one is just a bad idea.
source to share
Not. If you have a candidate key, use it as a clustered index. A unique, non-clustered index can be used for key searches and ensure uniqueness. This will use a clustered index and additionally for range scans and sort guarantees.
When heavy traffic is reduced to "several days", it indicates the time series in which queries are querying the latest ranges: totals and other aggregates for today, last day, last week, and so on. Creating the table by then, all of these queries work without having to scan the entire table, end-tot-end.
The primary key does not have to match the clustered index. Primary key is a logical concept useful for data modeling and referential integrity in a primary / foreign key relationship (strictly speaking, a foreign key can refer to any column (s), but most often it refers to a primary key).
The clustered index will determine the physical layout based on practical considerations around the most common queries, the type of range scans available and the key size tradeoffs.
Adding a clustered index will likely change some of the access patterns and could introduce new deadlock capabilities, but frankly, the chances are slim. Typically, a clustered index is dropped, which adds locks rather than adds one.
Finally, I wouldn't worry too much about the cost of the insert / update. Most applications have an overwhelming read-write ratio and faster reads make the application more responsive and faster. In addition, the read improvement is due to the fact that the useful index shows up as improvements in x100s (small scan compared to table end-to-end scan), while write degradation shows up as a fractional increase (for example, write time increases by 10-15%) usually invisible to the application.
source to share
Inserts are much faster if you don't have a clustered index, but there is still uniqueness with the primary key. Therefore, while the standard heap requires a query to search each record to find the one you want, the heap with a nonclustered primary key will stop after it finds the first result. While it's still slow, it's faster than nothing, and if you're doing 1M inserts in 1 query, well worth it.
Log tables are a prime example of where this is useful.
source to share