Indexes in Azure SQL Database v12

Since Azure SQL Database is just SQL Server when you get under the covers (although, you’ll never get under the covers), you’d think that it would just have the same rules and structures as SQL Server itself. Due to the nature of the shared servers, the distributed eco-system and probably a whole bunch of stuff that I just don’t know about since I don’t work there, they’ve had a different set of rules and indexes available for Azure SQL Database. That is until all the work that has come out with v12. Let’s talk about the differences that remain and what you can expect when you migrate or create a database in Azure SQL Database when it comes to indexes.

Traditional Indexes

For versions prior to v12, one of the fundamental requirements was that every table have a clustered index. You can create a table without one, you just can’t add data to that table prior to v12. Other than that requirement though, for the most part, a clustered index is a clustered index. A nonclustered index is a nonclustered index. They both have the exact same structures and pretty much everything else in conjunction with the earthed SQL Server product. They have identical statistics, well, mostly. In a lot of my testing I’ve noticed some differences that crop up between what’s in Azure and what’s on the earthed server even for identical data sets. I suspect that’s an artifact of “Azure First” development leading to some differences between what’s there and what is currently in SQL Server 2014. By and large though, they behave in an identical fashion.

There is one small difference between the two products. On SQL Server (earthed) you can have up to 32 columns and 900 bytes. Same thing for clustered index keys in Azure. Nonclustered index keys have an Increased key size, up to 32 columns and 1700 bytes. That’s right, you can create bigger nonclustered keys in Azure than in SQL Server. As with all limits, please remember, this is a limit, not a goal. Making your keys less and less narrow will result in reduced performance.

Columnstore Indexes

Documentation can be a little sketchy sometimes for Azure SQL Database. One example, my favorite document for Columnstore Indexes is the SQL Server Columnstore Index FAQ, which states that Columnstore Indexes are not yet supported in Azure. If I run this code on a Standard pricing tier database:

SELECT  *INTO    dbo.TransactionHistoryArchiveFROM    Production.TransactionHistoryArchive;CREATE CLUSTERED INDEX ClusteredColumnStoreTestON dbo.TransactionHistoryArchive(TransactionID);GOCREATE CLUSTERED COLUMNSTORE INDEX ClusteredColumnStoreTestON dbo.TransactionHistoryArchiveWITH (DROP_EXISTING = ON);GO

 I’m going to get an error:

Msg 40536, Level 16, State 32, Line 13'COLUMNSTORE' is not supported in this service tier of the database. See Books Online for more details on feature support in different service tiers of Windows Azure SQL Database.

Easily fixed. I have another piece of code I can run from PowerShell:

Set-AzureSqlDatabase -ConnectionContext $context -Database $db -Edition Premium

Now, if I rerun the CREATE CLUSTERED COLUMNSTORE command, it will succeed. That’s because the Columnstore indexes are only currently supported in the Premium tier. The documentation for the CREATE command does provide this information. Columnstore indexes were added to v12 and aren’t available in prior versions.

XML Indexes

You can now add XML Indexes in any tier of Azure SQL Database. This was added with version v12. I took the same database as before back down to Standard. I can then run CREATE XML SCHEMA COLLECTION and use that when running a CREATE TABLE command. From there, you can create an XML index. Just as an example, I created a schema, table and a couple of indexes on it. Now, when I run the query within Azure SQL Database and capture the execution plan:

I have XML indexes at work within the execution plan. XML indexing works exactly as it would on your earthed version of SQL Server because it is the same functionality. Best of all, you don’t have to be running your database in Premium to take advantage of XML Indexes.

Full-Text Search (in Preview)

As of this writing (June 2016), the Full-Text index is available as preview technology within all tiers of Azure SQL Database v12. You do need to be aware that anything listed as Preview may not be available in all regions. Also, they have occasionally changed functionality between the Preview and the release, although I suspect this will basically go out as is.

There are some aspects of the behavior of Full-Text that are not supported. The first thing to keep in mind, at all times, is that you’re working on a platform, not a server. That means that many of the service and server level settings you would normally have access to with Full-Text will not be available. Obviously you won’t be able to enabled, disable or modify the service itself. More importantly, you won’t be able to add filters, such as one for Word. Finally, a few pieces of functionality haven’t yet been enabled. Semantic search, thesaurus and property lists are unavailable. I’ll add one more little bit of functionality that tripped me up initially. You can’t run BULK LOAD statements from T-SQL to load the documents in. Permission for that is not a part of Azure SQL Database.

For a lot more great information, I’d suggest reading this Azure Blog post.

No Index At All

Heap tables. I’m not a fan. However, I can understand the need. For example, If I were to run this piece of code in a v11 Azure SQL Database:

SELECT  *INTO    dbo.NewTableFROM    dbo.Resource AS r;

I would generate an error:

Msg 40510, Level 16, State 1, Line 1Statement 'SELECT INTO' is not supported in this version of SQL Server.

But if I run this in v12, it works just fine. In fact, you may have noticed I used a SELECT … INTO statement earlier to create a table.

You can create a table in a v11 database like this:

CREATE TABLE dbo.NewerTable (     ID INT IDENTITY(1, 1)        NOT NULL,     Value VARCHAR(50)    );

That will succeed. When you try to add rows to the table though, you’ll get this error:

Msg 40054, Level 16, State 1, Line 13Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again.

The same code works perfectly in v12.

Conclusion

As you can see, you can create pretty much any index you need including leaving the index off the table entirely. I didn’t even mention the fact that spatial indexes are also supported because that’s actually old news. You have the ability to tune your structures through the addition of a wide range of indexes and index types. While some functionality is reduced in some places, the vast majority of the behavior you expect is now available. The number of limiting factors for Azure SQL Database that might prevent you finding some places where it might apply within your environment are shrinking.