Indexes are Crucial on Azure SQL Database!

One of the issues that seem to come up time and time again, is the lack of a basic index strategy. Many Windows Azure SQL Databases fail under load because they lack indexes.

Continue Reading...

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.

Building Clustered Indexes on Non-Primary Key Columns in Azure SQL Database

Azure SQL Database doesn’t support Heap Tables. Thus creating tables in Azure SQL Database requires Clustered Indexes. By default Azure SQL Database creates the Clustered Index on the table’s Primary Key.

CREATE TABLE [dbo].[TestTable] (
    [Id]        UNIQUEIDENTIFIER NOT NULL,
    [FirstName] NVARCHAR (10)    NOT NULL,
    [LastName]  NVARCHAR (10)    NOT NULL,
    [Type]      INT              NOT NULL,
    [Timestamp] ROWVERSION       NOT NULL,
    PRIMARY KEY (Id));

Primary Keys often make good Clustered Indexes because data is regularly accessed through them. But there are times when the Primary Key just doesn’t cut it. Fortunately, you can define a Clustered Index on other columns.

CREATE TABLE [dbo].[TestTable] (
    [Id]        UNIQUEIDENTIFIER NOT NULL,
    [FirstName] NVARCHAR (10)    NOT NULL,
    [LastName]  NVARCHAR (10)    NOT NULL,
    [Type]      INT              NOT NULL,
    [Timestamp] ROWVERSION       NOT NULL,
    PRIMARY KEY NONCLUSTERED (Id));
CREATE CLUSTERED INDEX IX_TestTable on TestTable (FirstName, LastName);

Changing a clustered index in Azure SQL Database is possible but it comes with some constraints. Failing to satisfy the existing  constraints enforce by the index will result in “The new index definition does not match the constraint being enforced by the existing index”.

CREATE CLUSTERED INDEX IX_TestTable
ON TestTable ([Type],[FirstName],[LastName])
WITH (DROP_EXISTING=ON)
ON [Primary]

Unidirectional Many to Many Relationship With Entity Framework Code First on Windows Azure SQL Database

There are times when entities don’t need navigation properties on both sides of the relationship. This is an example you can use to map to an existing database or generate a brand new database on Windows Azure SQL Database.

The ERD shows a Many to Many relationship between the FlightLog and the Airport entities. The requirements for the current model has no need for Airport to have a Navigation Property of FlightLogs.

Using Entity Frame Work Code First, the following statement can be used in the EntityTypeConfiguration to configure the relationship.

HasMany(r => r.Airports)
    .WithMany() // No navigation property here
    .Map(m =>
        {
            m.MapLeftKey("FlightLogId");
            m.MapRightKey("AirportId");
            m.ToTable("BridgeTableForAirportsAndFlightlogs");
        });

FlightLog has many Airports, the relationship is held in an intermediate table called BridgeTableForAirportsAndFlightlogs. By not specifying a property for the inverse Navigation Property, a unidirectional Many to Many relationship is created.