Press "Enter" to skip to content

Category: Indexing

Inline Non-Clustered Indexes

Published 2015-11-30 by Kevin Feasel

If you’re using SQL Server 2014, you get the benefit of writing inline non-clustered indexes.  Denny Cherry has more:

As for the syntax it’s pretty straight forward. Below is a sample table with a couple of indexes, one created on the column c2 and one created on C1 and C2. Now sadly include columns aren’t supported with inline indexes, but hopefully that’ll show up in a future version of SQL Server.

This was added for In-Memory OLTP support, and I like it.  For more on Denny’s comment about tempdb performance, check out a slide deck Eddie Wuerch used to teach people (including me) about temp table reuse.

Comments closed

Clustered Indexes Do Not Guarantee Physical Order

Published 2015-11-06 by Kevin Feasel

Gail Shaw on how clustered indexes do not guarantee physical order:

Do indexes (clustered or non-clustered) define the physical storage order of the rows?

No, absolutely not.

What indexes do is provide a logical ordering, a collection of pointers, that allow the storage engine to retrieve data from an index ordered by the index key, but that’s logical ordering, it specified nothing regarding the physical ordering.

Read the whole thing.

Comments closed