I’ve been playing around with the SQL Azure CTP for a little bit, and generally, it’s going well. However, as with any new technology, there are plenty of things to learn. I’m planning a series of posts around SQL Azure to share what I’m learning about it. And yes, there will be some SSIS thrown in there, too – what good is a database in the cloud if you can’t get your data in and out?
One of the first things I did was create a new database and some tables (rather obvious, I suppose – you can’t really do much in SQL without that). Something that you will likely encounter immediately when creating tables is the difference between what you can do in SQL Server and what SQL Azure supports. Primarily, it’s related to physical options affecting the storage. As a comparison, here’s the script that SQL Server Management Studio generates if you right-click on a table and choose Script Table As…Create To.
USE [MyDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MyTable](
[MyKey] [int] IDENTITY(1,1) NOT NULL,
[MyString] [varchar](30) NOT NULL,
[UpdateID] [int] NOT NULL,
[UpdateDate] [datetime] NOT NULL,
CONSTRAINT [pkMyTable] PRIMARY KEY CLUSTERED (
[MyKey] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [akMyTable] UNIQUE NONCLUSTERED (
[MyString] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY])
ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MyTable] ADD
CONSTRAINT [dfUpdateDate] DEFAULT (getdate()) FOR [UpdateDate]
GO
Here’s the same CREATE TABLE script, but trimmed down to just the items SQL Azure supports:
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MyTable](
[MyKey] [int] IDENTITY(1,1) NOT NULL,
[MyString] [varchar](30) NOT NULL,
[UpdateID] [int] NOT NULL,
[UpdateDate] [datetime] NOT NULL,
CONSTRAINT [pkMyTable] PRIMARY KEY CLUSTERED (
[MyKey] ASC)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF),
CONSTRAINT [akMyTable] UNIQUE NONCLUSTERED (
[MyString] ASC)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF))
GO
ALTER TABLE [dbo].[MyTable] ADD
CONSTRAINT [dfUpdateDate] DEFAULT (getdate()) FOR [UpdateDate]
GO
This is all documented in the SQL Azure documentation on MSDN, under the Transaction SQL Reference. And, as expected, most of the options that aren’t supported are related to physical storage.
One item that does stand out a bit, though, is USE. The USE statement is supported, but only if it references the current database, as in USE MyDatabase when you are connected to MyDatabase. Executing USE MyOtherDatabase when you are connected to MyDatabase will result in an error. Instead, you have to disconnect from MyDatabase and connect to MyOtherDatabase. It does make some sense not to allow users to switch databases in a multi-tenant model (I can picture all sorts of interesting hacks being created if that were possible). I do wonder, though, why it was included at all, as it is fairly useless in it’s current form. Maybe a future enhancement?
Anyway, if you are interested in SQL Azure, what this space for more updates as I continue working with it.