Databases Reference
In-Depth Information
Another item you might run into is that SQL Azure doesn't support
heap tables
. A heap table is one without a
clustered index. SQL Azure currently supports only clustered tables.
The question then becomes, what changes need to be made in order to make the script runnable for SQL Azure?
You need to make some changes for your script to run under SQL Azure. Here's what to do:
1.
Delete all instances of
SET ANSI_NULLS ON
.
2.
Delete all instances of
ON [PRIMARY]
.
3.
Delete all instances of TEXTIMAGE_
ON [PRIMARY]
.
4.
Delete all instances of the following:
•
PAD_INDEX = OFF
•
ALLOW_ROW_LOCKS = ON
•
ALLOW_PAGE_LOCKS = ON
5.
In the Person table, modify the rowguid column, removing the
ROWGUIDCOL
keyword.
6.
Add a clustered index to any heap tables.
Appendix B discusses the need for these changes in detail. For now, here's a quick explanation:
•
ON [PRIMARY]
isn't needed because, as you learned in Chapters 1 and 2, SQL Azure hides
all hardware-specific access and information. There is no concept of
PRIMARY
or file groups,
because disk space is handled by Microsoft, so this option isn't required.
•
According to SQL Server Books Online (BOL) you can remove the entire
WITH
clause that
contains the table options. However, the only table options you really need to remove are
those listed in step 4 (
PAD_INDEX
,
ALLOW_ROW_LOCKS
, and
ALLOW_PAGE_LOCKS
).
Even though this table didn't use it, the
•
NEWSEQUENTIALID()
function isn't supported in SQL
Azure, because there is no CLR support in SQL Azure, and thus all CLR-based types aren't
supported. The
NEWSEQUENTIALID()
return value is one of those types. Also, the
ENCRYPTION
option isn't supported because SQL Azure as a whole doesn't yet support encryption.
•
SQL Azure doesn't support heap tables. Thus, you need to change any heap table into a
clustered table by adding a clustered index. (Interestingly, if you execute one statement at
a time, you can, in fact,
create
a heap table. However, any inserts into that table fail.)
One of the things the SQL Azure documentation suggests, and which is listed earlier, is to set the Convert UDDTs
to Base Types property to True. This is because user-defined types aren't supported in SQL Azure.
After you make the changes just described to your SQL script, it should look like the following:
/****** Object: Schema [Person] Script Date: 4/22/2012 3:38:28 PM ******/
CREATE SCHEMA [Person]
GO
/****** Object: UserDefinedDataType [dbo].[Name] Script Date: 4/22/2012 3:38:28 PM ******/
CREATE TYPE [dbo].[Name] FROM [nvarchar](50) NULL
GO
/****** Object: UserDefinedDataType [dbo].[NameStyle] Script Date: 4/22/2012 3:38:28 PM ******/
CREATE TYPE [dbo].[NameStyle] FROM [bit] NOT NULL
GO
/****** Object: Table [Person].[Person] Script Date: 4/22/2012 3:38:28 PM ******/
SET ANSI_NULLS ON
GO
