SQL Azure, CREATE TABLE, and USE

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.

One Comment

  1. http:// says:

    I tried connecting to SQL Azure using VS 2008. Still doesn’t work.

    I tried connecting with SSMS 2008. As reported, the connection fails but if you open a ‘query’ and connect in query mode, you kinda can get it to work.

    I then tried to create some test tables and ran into the same problem that a LOT of the stuff in my create table scripts has to be removed in order to get it to work.

    After I cleaned it up (I’ll go try again based on your notes above), it still didn’t let me create a table on Master. I looked around and it seems (not quite sure) I need to create an additional database, so I created ‘Test’ as a database (from the SQL Azure website interface). On SQL Azure I can see Master & Test.

    But I can’t connect to the Test database with SSMS.

    Nor can I connect to Master and switch to Test (your note aboute Use not working to switch databases explains that).

    Ugh.

Leave a Reply