A SQL Azure tip a day (8) – Enterprise or Standard?

This is day 8 of my series, and I had to go back to the blog from day 3 to complain about the connection problem, please revisit day three and get back here. Is the SQL Azure platform Enterprise or Standard edition? We already know that Partitioning doesn’t exist, but we got Federation instead, and we already know that you can’t control the data compression.  Lets try some of my other favourite Enterprise features, starting with online rebuild of the indexes. ALTER INDEX pk_table1 ON dbo.table2 REBUILD WITH(ONLINE=ON) ; That worked fine, thats good! What about indexed views? Lets build an indexed view. I have created and populated a table, called Table1, with four columns and the script below is for the view. CREATE VIEW dbo.View1 WITH SCHEMABINDING AS SELECT ID, Column1, Column2, Column3 FROM dbo.Table1 GO  CREATE UNIQUE CLUSTERED INDEX ix_view1 ON dbo.View1 (Column3) GO  SELECT * FROM DBO.View1 WHERE Column3=8; SELECT * FROM DBO.View1 WITH(NOEXPAND) WHERE Column3=8; Take a look at the exeuction plan of the two selects.

As you can see, the index of the view is not used unless you specify the WITH(NOEXPAND) hint, and thats the behaviour of the Standard Edition. I guess thats not a problem, because I haven’t seen many peoples using indexed view in the first place, maybe because of a lot of misunderstandings. Some of the indexed views I have seen have been in Standard edition and the applications didn’t specify the NOEXPAND hint, and what good is that? It will only slow your system down. If you use indexed views, make sure you specify NOEXPAND in Standard and in SQL Azure! Otherwise I’ll come back and haunt you in your sleep! Well the answer to the question “standard or enterprise edition” is neither. It is Azure edition. What does it mean?  Well, it means that you’ve got some of the enterprise features and the rest is standard edition. You can rebuild your indexes in online mode, which is an enterprise feature, but you have to specify WITH(NOEXPAND) if you want to use an indexed view, like in the standard edition.

Get back tomorrow for more SQL Azure tips.