Playing with SQL Azure; part 1

For my own amusement and edification I've decided to learn about cloud computing by playing with SQL Azure. Within the last year, I've moved my personal information into the cloud (from Palm Pilot to a Nexus One Android phone with Google Docs, Remember the Milk, Google Contacts, etc) and am curious to see how that same switch could happen with databases.

Also, I just read the book The Big Switch: Rewiring the World, from Edison to Google. It's basically about how there's a very strong analogy between the move to cloud computing today and what happened about 100 years ago, when factories, instead of generating their own electricity, converted to buying their power from electrical plants. Interesting stuff. And I do believe that the movement to cloud computing is just as inevitable as the movement to buying power from dedicated electrical plants.

For my first step, I've set up a SQL Azure account (had to provide a credit card number, but they say they won't charge anything for three months). I've set up a database, and will be experimenting with it (downloading tools to access it, etc). My first task will probably be to go through the tutorials for SQL Azure.

Recursive common table expression 'TableName' does not contain a top-level UNION ALL operator.

I've started using CTEs (Common Table Expressions) a lot. I find they can make my code quite a bit easier to read. But just now, working on a SQL statement with a CTE, it took me a while to figure out why I was getting this error message:

Msg 252 
Recursive common table expression [TableName] does not contain a top-level UNION ALL operator.

It was a long and complex sql statement with multiple CTEs, which obscured the real problem. The little I found online wasn't helping either. I ended up going step by step, and stripping out everything from the SQL statement. Finally I ended up with something similar to this, which returns the error:

;With table1 AS
(
    Select field1 from table1 where field1 = 1
)
Select  * from table1

At this point it was obvious—you can't name your CTE with the same name as a table it's referencing. So the answer was to rename my CTE to something like this:

;With table1_filtered AS
(
    Select field1 from table1 where field1 = 1
)
Select  * from table1

There's other conditions that can cause this error to occur as well, but I didn't see this one described online anywhere. If you have this problem as well, feel free to comment.