Creating NONCLUSTERED INDEXES on Massive Tables in Azure SQL Database

There are times on Azure SQL Database when tables get to a certain size and that trying to create indexes results in timeouts.

A few months ago when I started to get these famous timeouts, I had reached 10 million records and I felt like I was looking for a needle in a hay stack!

This blog post is all about creating NONCLUSTERED INDEXES, I will try to go over best practices and reasons to keep in mind when you use them in Azure SQL Database.

Interesting Facts

• If a CLUSTERED INDEX is present on the table, then NONCLUSTERED INDEXES will use its key instead of the table ROW ID.
• To reduce the size consumed by the NONCLUSTERED INDEXES it’s imperative that the CLUSTERED INDEX KEY is kept as narrow as possible.
• Physical reorganization of the CLUSTERED INDEX does not physically reorder NONCLUSTERED INDEXES.
• SQL Database can JOIN and INTERSECT INDEXES in order to satisfy a query without having to read data directly from the table.
• Favor many narrow NONCLUSTERED INDEXES that can be combined or used independently over wide INDEXES that can be hard to maintain.
• Create Filtered INDEXES to create highly selective sets of keys for columns that may not have a good selectivity otherwise.
• Use Covering INDEXEs to reduce the number of bookmark lookups required to gather data that is not present in the other INDEXES.
• Covering INDEXES can be used to physically maintain the data in the same order as is required by the queries’ result sets reducing the need for SORT operations.
• Covering INDEXES have an increased maintenance cost, therefore you must see if performance gain justifies the extra maintenance cost.
• NONCLUSTERED INDEXES can reduce blocking by having SQL Database read from NONCLUSTERED INDEX data pages instead of the actual tables.

Creating a NONCLUSTERED INDEX

In order to look at a few examples lets start with the following table.

CREATE TABLE [dbo].[TestTable] (
    [Id]        UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
    [FirstName] NVARCHAR (10)    NOT NULL,
    [LastName]  NVARCHAR (10)    NOT NULL,
    [Type]      INT              DEFAULT ((0)) NOT NULL,
    [City]      NVARCHAR (10)    NOT NULL,
    [Country]   NVARCHAR (10)    NOT NULL,
    [Created]   DATETIME2 (7)    DEFAULT (getdate()) NOT NULL,
    [Timestamp] ROWVERSION       NOT NULL,
    PRIMARY KEY NONCLUSTERED ([Id] ASC)
);

GO
CREATE CLUSTERED INDEX [IX_TestTable]
    ON [dbo].[TestTable]([Created] ASC);

The table has a CLUSTERED INDEX on the Created column. The reasons why this might be an interesting choice for the CLUSTERED INDEX were discussed in my previous blog post about building Clustered Indexes on non-primary key columns in Azure SQL Database.

For the sake of this example, lets imagine that this table contains roughly 30 millions records. Creating a NONCLUSTERED INDEX on this table might result in a timeout or an aborted transaction due to restrictions imposed by Azure SQL Database. One of these restrictions, is that a transaction log cannot exceed 1GB in size.

On Azure SQL Database, you must use the ONLINE=ON option in order to reduce locking and the transaction log size. Furthermore, this option will greatly reduce your chances of getting a timeout.

CREATE INDEX [IX_TestTable_Country_City_LastName_FirstName] 
    ON [dbo].[TestTable] 
    ([Country] ASC,[City] ASC,[LastName] ASC,[FirstName] ASC)
    WITH(ONLINE=ON);

Creating a Filtered NONCLUSTERED INDEX

A where clause is added to the index. This results in a Filtered index and greatly helps to create smaller and more effective INDEXES. I recommend taking a closer look at this type of index because bytes are precious on Azure SQL Database.

CREATE INDEX [IX_TestTable_Type_LastName_FirstName_where_Type_greater_than_1] 
    ON [dbo].[TestTable] 
    ([Type] ASC,[LastName] ASC,[FirstName] ASC)
    WHERE [Type] > 1
    WITH(ONLINE=ON);

Creating a Covering NONCLUSTERED INDEX

The include clause can be used to include columns that are selected by the queries but that are not filtered upon through where clauses. The include clause can also be used to include columns that cannot be added to the INDEX key.

CREATE INDEX [IX_TestTable_Covering_Index] 
    ON [dbo].[TestTable] 
    ([Type] ASC,[LastName] ASC,[FirstName] ASC)
    INCLUDE([City], [Country], [Id])
    WITH(ONLINE=ON);