If you want to blog T-SQL Tuesday is a great way to get started. On the first Tuesday of each month (or possibly a bit earlier as in this case) someone presents a blogging idea. Everyone who wants to participate then comes up with something on that subject and posts it on the second Tuesday of the month. Well this month (the 81st TSQL Tuesday!) Jason Brimhall (b/t) has given us a somewhat more complicated subject than usual, even though it’s remarkably open ended. The subject was Sharpen Something. i.e. improve a skill or some knowledge.

Ok, so what skill was I going to sharpen? There are certainly enough of them where I need some work. Well, as sometimes happens, the choice was easy. I’d been working on sharpening my Azure skills anyway, but one of my managers asked me to go to a webinar on the subject for an upcoming project. I ended up going to Jes Borland’s (b/t) session on Azure and SQL Server on the Pass Data Architecture Virtual Chapter. Fortunately the VC sessions are recorded so you can go back and watch it yourself, or, like me watch it again.

Alright, so I’m sharpening my skills. So what did I get out of the webinar? Well, as with anything Jes does the answer is A LOT. In fact on my first pass I noted down six different things I wanted to look at in more depth. (Check out my blog ideas page.) So this is a great opportunity for the first one.

T-SQL Differences in Azure SQL Database

I used to think this was the real difference between SQL Server and SQL Database. I was wrong. Really wrong. But it’s a good place to start. Now from what I can tell everything in Azure is a moving target. There are constant changes so it’s important to know where the documentation is. In this particular case here it is: Azure SQL Database Transact-SQL differences.

It’s reasonable to assume (of course we know what making assumptions does right?) that the differences exist because SQL Databases are supposed to be as independent as possible from the windows server and to a lesser degree the instance. When you create an Azure SQL DB you create a Server (the instance) but you don’t specify a windows server. That’s because it doesn’t matter what server your instance is on. It’s independent. Well, because of that, some things just aren’t going to work.

Highlighting a few of the commands mentioned in the document:

Partially supported
These commands are supported, but only in part. Certain options aren’t available.

CREATE DATABASE – It says this is partially supported but to me it’s really a completely new command. (Compare to SQL Server CREATE DATABASE.) Options for Edition (basic, standard or premium), Pricing Tier (basic, S0, S1, P1, P2 etc) and the Elastic Pool. No options for things like location (of course), default language (surprising), trustworthy (not surprising) etc. The only things that I’m seeing from both are name, collation and size.

KILL – The main difference here is the inability to kill by UOW. The Unit of work id used with distributed transactions. Since we aren’t using distributed transactions this isn’t a surprise. The inability to see the status of a rollback caused by a KILL (WITH STATUSONLY option) is a bit surprising to me and I hope the option will be added soon.

CREATE VIEW – While this is listed under partially supported there doesn’t appear to be any difference for SQL Database. There is a bit of a difference when using SQL Data Warehoue and Parallel Data Warehouse. It’s just a stripped down version. No SCHEMABINDING, no ENCRYPTION etc.

Not supported at all
These are the commands that for one reason or another won’t work at all.

No cross database ownership chaining or TRUSTWORTHY – We saw that in the CREATE DATABASE command.

EXECUTE AS Logins – Since we are supposed to be limiting our server level access this makes sense. EXECUTE AS Users still appears to work normally.

Database Mail – I can see this one being technically difficult but I’m really hoping they get it working in the future.