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...
Archives For Clustered Index
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.
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]