THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

SQL Azure differences

If you prefer wizards and designers, SQL Azure may cause you some frustration because they’re not there. But if you like T-SQL scripting, you’ll be in your element with SQL Azure. Here’s what happens in SQL Azure when you try to create a new table:

image

Since it’s likely that existing SQL Server scripts will be used to create tables in SQL Azure, I decided to put a script to the test. In SQL Server 2008 R2, I created a table that has one column for every SQL Server data type. I scripted the table and tried to run the script on SQL Azure.

CREATE

TABLE dbo.allDatatypes(

abigint bigint NULL,
abinary binary(50) NULL,
abit bit NULL,
achar char(10) NULL,
adate date NULL,
adatetime datetime NULL,
adatetime2 datetime2(7) NULL,
adatetimeoffset datetimeoffset(7) NULL,
adecimal decimal(18, 0) NULL,
afloat float NULL,
ageography geography NULL,
ageometry geometry NULL,
ahierarchyid hierarchyid NULL,
aimage image NULL,
aint int NULL,
amoney money NULL,
anchar nchar(10) NULL,
antext ntext NULL,
anumeric numeric(18, 0) NULL,
anvarchar50 nvarchar(50) NULL,
anvarcharMAX nvarchar(max) NULL,
areal real NULL,
asmalldatetime smalldatetime NULL,
asmallint smallint NULL,
asmallmoney smallmoney NULL,
asql_variant sql_variant NULL,
atext text NULL,
atime time(7) NULL,
atimestamp timestamp NULL,
atinyint tinyint NULL,
auniqueidentifier uniqueidentifier NOT NULL,
avarbinary50 varbinary(50) NULL,
avarbinaryMAX varbinary(max) NULL,
avarchar50 varchar(50) NULL,
avarcharMAX varchar(max) NULL,
axml xml NULL
) ON PRIMARY TEXTIMAGE_ON PRIMARY

Msg 156, Level 15, State 1, Line 40
Incorrect syntax near the keyword 'PRIMARY'.

It didn’t work because SQL Azure abstracts away filegroups, which probably doesn’t bother people who only use the defaults for filegroups. Removing ON PRIMARY TEXTIMAGE_ON PRIMARY allows the script to run successfully on SQL Azure.

Although geography, geometry, and hierarchyid are implemented with the CLR, user defined CLR is not allowed in SQL Azure. As we can see from running the corrected script, SQL Azure supports all SQL Server data types, but that doesn’t mean everything works exactly the same way as it does on SQL Server. If you need to insert guids, newsequentialid() is very helpful in preventing index page splits during inserts. It’s not supported in SQL Azure, so you’ll have to use newid() instead.

auniqueidentifier uniqueidentifier NOT NULL default newsequentialid(),

Msg 40511, Level 15, State 1, Line 32
Built-in function 'newsequentialid' is not supported in this version of SQL Server.

Also, ROWGUIDCOL is not supported.

auniqueidentifier uniqueidentifier NOT NULL ROWGUIDCOL,

Msg 40514, Level 16, State 6, Line 1
'ROW GUID COLUMN' is not supported in this version of SQL Server.

You can’t use extended properties to document a table in SQL Azure. Most people don’t do this, so it’s not a big deal.

EXEC

sp_addextendedproperty N'MS_Description', N'This column is used as the primary key.'
, 'SCHEMA', N'dbo'
, 'TABLE', N'allDataTypes'
, 'COLUMN', N'_uniqueidentifier'
GO
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'sp_addextendedproperty'.

Given this background, you know to expect some differences between the SQL Server version of the AdventureWorksLT database and the SQL Azure version. Since we are so familiar with AdventureWorks databases and there are so many examples written based on them, it’s important to know how similar the SQL Azure version is. This isn’t such an easy thing to do today because of a lack of SQL Azure compatible tools. Using a special build of SQL Compare that Red Gate provided to me (take the link and answer the survey to get your own copy), I was able to get a nice visual representation of the differences.

image

We can see several differences including a lack of triggers and foreign keys in the SQL Azure version of AdventureWorks. This could lead you to the erroneous conclusion that triggers and foreign keys are not supported in SQL Azure. Rest assured, they are supported. I ran the scripts to create the triggers and foreign keys in AdventureWorksLTAZ2008R2 and they worked just fine.

Other differences between SQL Server and SQL Azure can be seen in right-click menu options. I think Arnie and Allen are probably upset about the lack of PowerShell support.

image

image

 

Full-Text Indexes, Policies, and Facets are not supported in SQL Azure.

 

image

image

Published Sunday, September 12, 2010 10:28 PM by John Paul Cook

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Buck Woody said:

Last Updated: 07/01/2011 It’s time for another certification, and we’ve released the 70-583

July 1, 2011 5:35 PM

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is a Technology Solution Professional for Microsoft's data platform and works out of Microsoft's Houston office. Prior to joining Microsoft, he was a Microsoft SQL Server MVP. He is experienced in Microsoft SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. John is also a Registered Nurse who graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. He volunteers as a nurse at safety net clinics. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement