Grant Fritchey has an intro-level article on clustered indexes:
You get one clustered index per table.
That bears repeating, you get one clustered index per table.
Grant follows up with good advice.
Comments closedCategory: Indexing
Derik Hammer has an article on non-clustered index design:
In general, non-clustered indexes are a positive force in your database. Indexes are a form of data duplication, however, which costs disk space. In addition, non-clustered indexes need to be maintained. This can increase the number of writes which occur during INSERT or UPDATE operations and increase the number of index rebuilds or reorganizations that need to be performed.
Create non-clustered indexes to support all of your queries but be careful not to create duplicates and regularly purge indexes which are no longer necessary.
Worth reading the whole thing.
Comments closedKendra Little discusses unique constraints versus unique indexes:
Unique constraints and unique nonclustered indexes have a lot in common: unique constraints are implemented with a unique index behind the scenes.
While unique nonclustered indexes have a couple of additional features, I can still think of a scenario where it might be best to use unique constraints.
The value I see in unique constraints over unique indexes is semantic: it says to everyone, “I’m supposed to be unique by virtue of my existence.” They’re part of the data model, whereas indexes are performance optimizations to handle common data paths.
Comments closedUwe Ricken discusses a downside to leaving an active table as a heap:
The simplest way of removing empty data pages from a heap is via the exclusive locking of the table. But keep in mind that there is no such thing as a free lunch. Blocking the table from other processes will lead to a system that will not scale. As long as the DELETE process locks the table, no other process can access the table. The only exception is the “SNAPSHOT ISOLATION” but explaining the pros and cons of this technique is outside the scope of this article.
The next example (with the new created table) demonstrates the behavior.
The section on lock escalation was rather interesting.
Comments closedDaniel Hutmacher makes an argument for when to use a heap instead of a clustered index:
I’m an outspoken advocate of always using a clustered index on each and every table you create as a matter of best practice. But even I will agree that there’s a case for using the odd heap now and then.
Read on for indicators that you might be better served with a heap.
Comments closedKendra Little explains the rules behind how large a non-clustered index row can be:
So make sure you really need all that junk in your nonclustered index trunk. Er, key.
But even with the expanded size of key columns, sometimes I get asked a question: do columns that “secretly” get added to the key of a nonclustered index count against the maximum allowed nonclustered index key length?
You can read the short answer, but I recommend reading the full post.
Comments closedKenneth Fisher digs into the debate on surrogate keys versus natural keys:
A natural key is one constructed of data that already exists in the table. For example using latitude and longitude in a table of addresses. Or the social security number in a table of employees. (Before you say anything, yes, the social security number is a horrible primary key. Be patient.)
My personal preference is to use surrogate keys most of the time and put unique constraints (or unique indexes) on the natural key. There are some occasions in which I’d deviate, but ceteris paribus I’d pick this strategy..
Comments closedBrent Ozar generates 999 indexes:
The CHARACTER_MAXIMUM_LENGTH <> -1 OR IS NULL stuff is because I don’t want to try to index NVARCHAR(MAX) fields, but I do want to index NVARCHAR(50) fields.
The ORDER BY is because I wanted to get a wide variety of leading fields. If I just ordered by c1.COLUMN_NAME, I wouldn’t get all of the first fields covered in the first 999 indexes. (And I’m not even using a large table.)
Sometimes I think I’ve worked on systems which used this script to build indexes. But then I read the index names: “dta.” And it all makes sense.
Comments closedSlava Murygin writes a spatial query which shows database fragmentation:
As you can see, I definitely have a lot of free space, but my data are so spread across the file and especially up to it’s border, that there is no way to make file size smaller.
If we zoom at the very tail we can figure out the names of tables at the very end of the file, which prevent file from shrinking:
This looks quite a bit like the old Windows 95 defrag tool. I like it.
Comments closedJeremiah Peschka looks at a scenario in which a heap might be superior to a clustered index:
In this case, we have to assume that
Event IDs may be coming from anywhere and, as such, may not arrive in order. Even though we’re largely appending to the table, we may not be appending in a strict order. Using a clustered index to support the table isn’t the best option in this case – data will be inserted somewhat randomly. We’ll spend maintenance cycles defragmenting this data.Another downside to this approach is that data is largely queried by
Owner ID. These aren’t unique, and oneOwner IDcould have many events or only a few events. To support our querying pattern we need to create a multi-column clustering key or create an index to support querying patterns.
This result is not intuitive to me, and I recommend reading the whole thing.
Comments closed