Do tables in a SQL Azure Database need to have a primary key?

Standard

Answer: No.

Though SQL Azure does need that a table has a clustered index. So to this end, Let’s write some TSQL code and cement this concept in our brain. So as a part of the test, we would create a table with no primary key – However we would certainly create a clustered index on one of the column. So let’s get started:

OK, so here’s the TSQL code to create a table named ‘InternationalStudentList’:

[code language=”sql”]create table InternationalStudentList (StudentName varchar(30),HomeCountry varchar(30), DegreeProgram varchar(30))
go
[/code]

Now let’s insert some data into this table. Here’s the query:

[code language=”sql”]
insert into internationalstudentlist values(‘Paras Doshi’,’India’,’Masters in IT and Management’)
go
[/code]

If you run the above query – you would get the error:

Msg 40054, Level 16, State 1, Line 1
Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again.

So we know that a table in SQL Azure requires a clustered index, right? So, Let’s create one!
Let’s assume that the InternationalStudentList would be queried a LOT to answer the question: “List all StudentName whose HomeCountry is X” – So based on this information let’s create a clustered index on HomeCountry Column. Here’s the TSQL code:

[code language=”sql”]
create clustered index cix_internationalstudentlist_homecountry ON
InternationalStudentList(HomeCountry)
go
[/code]

Once a clustered index is created, Try inserting data again. And you would see that a row would get successfully Inserted!
Run a Select Command to verify that:

SQL Azure Result of a SQL select command

And as you can see, we were able to insert a row in a table. Remember that this table did not have a primary key But we did create a clustered index. The Goal of the Post is achieved here, But just want to show you the Query Plan for a Query that looks like:

[code language=”sql”]
select StudentName,HomeCountry,DegreeProgram from InternationalStudentList
where HomeCountry = ‘India’
go
[/code]

sql azure clustered index seek

Note that I have run this queries on Management portal for SQL Azure, You can run it in SSMS too. But the goal is to show you that we have clustered index seek and that’s one of the way to tune queries. Not going into details in this post – And That’s about it for this post. Your feedback is welcome.

And Let’s connect! Here are few people networks that I am active on:

paras doshi blog on facebookparas doshi twitterparas doshi google plusparas doshi linkedin

I Look forward to Interacting with you!

Forum Q&A: Best method to Migrate on premise to SQL Azure

Standard

Question:

What is the best method to use for copying an “On Premise SQL DB” to SQL Azure. I’ve used the generate script wizard method but it takes quite awhile to get everything setup.  I was looking a DAC Pack but I’m a little nervous using a command utility that you could easily use to wipe out a database on accident.  Looks like a great tool though. What do you recommend? Or, is the Generate Script method the best way to go? I noticed that SQL Azure requires Clustered indexes on all tables. I would imagine that this might be a hindrance to automated migration tools.

My Answer:

Generate script wizard is a great tool for smaller databases.

For bigger databases, You should consider using a combination of Generate script wizard for migrating schema and then BCP for data.

And http://blogs.msdn.com/b/sqlcat/archive/2010/07/30/loading-data-to-sql-azure-the-fast-way.aspx  has some great info.

And as far as i know, the SQL Azure Migration wizard creates scripts that does clustered index creation (if it does not exist) – so yes, that is taken care of.

And for more information on various options for schema migration and data migration that you have – I would suggest an article from solidq Journal: http://www.solidq.com/sqj/Pages/2011-May-Issue/Migrating-Data-into-Microsofts-Data-Platform-SQL-Azure.aspx. It also covers best practices while migrating data to SQL Azure.

Link:

http://social.msdn.microsoft.com/Forums/en-US/ssdsgetstarted/thread/ebb9f077-2a89-4044-bf57-212d29b48a01/