Category: Indexing

Shane O’Neill answers one of my favorite interview questions:

I used to think that this would be a complex requirement, possibly requiring aTRIGGER or two to check the inserted value against whatever is already there; but there is a way to have this functionality and have it the way that SQL Server normally would enforce a uniqueness on a column; by using a UNIQUE INDEX.

In case you’re thinking…

“Oh, a unique index doesn’t check what’s already there, is that it?”

I’m afraid that’s not the case.

This is one of my favorite uses of filtered indexes:  “limited” uniqueness.  In other words, I’m okay with an unlimited number of NULL values but all non-NULL values need to be unique.

Derik Hammer looks at the power of clustered indexes:

The data in a clustered index is logically sorted but does not guarantee that it will be physically sorted. The physical sorting is simply a common misconception. In fact, the rows on a given page are not sorted even though all rows contained on that page will be appropriate to its place in the logical sort order. Also, the pages on disk are not guaranteed to be sorted by the logical key either.

The most likely time where you will have a clustered index that is physically sorted is immediately after an index rebuild operation. If you are trying to optimize for sequential reads, setting a fill factor to leave free space on your pages will help limit how often you have pages physically out of order at the expense of disk space.

Derik also discusses four qualities for a good clustered index.  My preferred acronym is NUSE (Narrow, Unique, Static, Ever-increasing); Derik uses slightly different terms.

SQLWayne has a script to help find unused indexes:

Here’s some code that can show you what indexes are unused or empty.  An empty index just means that there’s no data in that table right now, it may always be populated later, so I would not drop an empty index.  Besides, how much space would an empty index take?

For my personal preferences, I order the output by table then index name, also I put a u.* at the end of the select statement so the more interesting usage stat columns can be seen.

If an index truly is unused, it’s a waste of resources.  The problem is, sometimes you’ll think an index is unused but it’s really a vital part of month-end reporting or used for the CEO’s favorite dashboard.

Kendra Little has a new video involving indexing:

Recently, I was thinking about nonclustered indexes in SQL Server, and how included columns are stored. Is SQL Server smart enough to optimize the storage for small indexes with includes? Find out in this free seven minute video.

It’s a short video, well worth your time.

Kendra Little provides a T-SQL script to script out all indexes on a database:

Sometimes you need to script out all the indexes in a database.

Maybe you’re concerned something has changed since they were last checked in.

Or maybe the indexes aren’t checked into source control, and you’re working on fixing that. (Important!)

Either way, sometimes you need to do it, and it’s not fun through the GUI. I needed to write some fresh demo code for this recently, and I needed it to give the details for partitioned tables using data compression, and I thought I’d share.

The fact that the built-in Generate Scripts does not include compression is annoying, but Kendra’s script does.  For bonus points, use Powershell to update scripts automatically with index changes and check them into your source control system of choice.

Jos de Bruijn notes that non-clustered index keys may be larger in SQL Server 2016 and Azure SQL Database:

SQL Server 2016 and Azure SQL Database have increased the maximum size for index keys with nonclustered indexes. The new maximum key size for nonclustered indexes is 1700 bytes. The maximum key size for clustered indexes remains 900 bytes.

95-99% of the time, we don’t want to get anywhere near 900 bytes (much less 1700 bytes), but we all have that one edge case.

Derik Hammer walks us through heap tables as part of his indexing series:

A heap is a grouping of unsorted pages which are not linked. Page anatomy is out of scope for this series since all types of indexed and non-indexed tables use the same page structure but I do encourage you to check out here and here to learn more.

A heap is comprised of one or more index allocation map (IAM) pages which point to the data pages which make up the heap. The only exception to this is when you have a row which has been updated and could not fit in its page anymore. In that case, you get a forwarding pointer to the row which has been moved to an existing page with space or a new page. It is possible for you to produce a chain of forwarding records if the row continues to need relocation by further operations.

This is a good introduction to heaps and will feed into later work on how different types of indexes work.

Derik Hammer is starting a new series on index types and has started with the venerable B-tree:

On more than one occasion I have been told that the B-Tree index is the index structure which was designed after the A-Tree index. Another common misconception is that it stands for Binary-Tree. As logical as those may seem, they are false. The ‘B’ in B-Tree does not actually have any specific meaning. Check out Ed McCreight’s explanation here (16:08) where he admits that the name discussion was never settled.

In its most basic form, the B-Tree index is a hierarchy of data pages (page structures lightly touched on in the next post of this series). The lowest level is called the leaf level, the highest level is the index root, and all levels in between are the intermediate levels. This structure is an improvement over the Binary Tree index because its balanced nature greatly improved the performance of maintenance operations such as, INSERT, DELETE, and UPDATE.

On the terminological point, I’d always heard that the “B” stood for “Balanced” because of the level flatness—in contrast to a “normal” tree, you wouldn’t have more than a pre-defined number of levels separation (usually one) between leaf nodes.