I want to start out a series on what SQL Azure is, benefits and pitfalls, On premises vs Cloud data and so on. There are a bunch of cool benefits with putting the data in the cloud if your application resides there, SQL Azure delivers a high scalable, high performance and highly secure method of working with data for cloud services. Of course we still need our on premises, sql boxes, bare metal or virtual. But for online services the cloud delivers the technologies for us. And now also a highly competent SQL service.

SQL Azure got commercially available on the 17th of november well aligned with the Pre-Developer Conference in Los Angeles. Unfortunally I wasn't there drue to TechEd in Berlin and business. But I had alot of friends attending and speaking there, as with online content, so I guess in time I'll get all the goods from the event. Maybe next year...

SQL Azure is available in two editions, Web (1GB) and Business(10GB). To get you started you need to know how to create the database.

So here it is: (Snip out from books online) 

Syntax

CREATE DATABASE database_name           [(MAXSIZE = [1 | 10] GB )][;]

 Arguments

database_name

The name of the new database.

MAXSIZE

Specifies the maximum size of the database. In this release, SQL Azure provides two database sizes for users: 1 GB or 10 GB. If the MAXSIZE is not set, a default value of 1 GB is used.

This syntax diagram demonstrates the supported arguments in Microsoft SQL Azure Database.

SQL Azure does not support the following arguments and options when using the CREATE DATABASE statement:

• Parameters related to the physical placement of file, such as <filespec> and <filegroup>
  
• COLLATE on a user database
  
• External access options, such as DB_CHAINING and TRUSTWORTHY
  
• Attaching a database
  
• Service broker options, such as ENABLE_BROKER, NEW_BROKER, and ERROR_BROKER_CONVERSATIONS
  
• Database snapshot
   

Remarks

Databases in SQL Azure have several default settings that are set when the database is created.
CREATE DATABASE statement must be the only statement in a SQL batch.

Read more on SQL Azure, http://msdn.microsoft.com/en-us/library/ee336279.aspx.

Please remember to join the forum, http://forum.mssqlserver.se. As long as they are available you'll get t-shirts, http://www.mssqlserver.se/t-shirts.html, if you register and message me your postal address and size; chose between L, XL and XXL.

Have a great summer full of SQL!

KKReg.zip (1,32 kb)

I'd like to share some samples from the class "T618 Informationsbehandling med SQL". This is the design we discussed the last day.

For all you others, this is an example on how to do it based on the techDays-session. There are some minor adjustments in the script to be fixed, but it works  good enough. For example the fillfactor settings need to be calculated based on actual load and so on.

 Anyway, enjoy!

OrdersDB_PART1_V1.sql (9,20 kb)

To extract hierarcies based on parent-child relationships you can try with Common Table Expressions. This is a new feature from MS SQL Server 2005 and it can do some magic. And for retrieving hierarchies it's by far more effective than using a CURSOR.

WITH cte (LVL, EMPID, MGRID )
AS (
    SELECT
        1 AS LVL , EmployeeID AS EMPID, ManagerID AS MGRID
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT
        cte.LVL + 1, emp.EmployeeID, emp.ManagerID
    FROM HumanResources.Employee emp
    INNER JOIN cte ON emp.ManagerID = cte.EMPID
)
SELECT * FROM cte
ORDER BY LVL ASC

To explain how this works I'll simplify the CTE as a named and defined resultset. By naming it, it'll become addressable and this makes the magic. Now you can address previously retrieved rows. If we break down the query in the main parts it'll be easier to explain.

First the declarative part, WITH cte(LVL, EMPID, MGRID). This names the resultset to cte with the columns. Secondly we have the AS(...) that is the logic and will do the retrieval of rows. Third and finally the extraction of the data, SELECT * FROM cte ORDER BY LVL. This is the easy part.

If we look at the AS(...)-part. It consists of two SELECT-statements combined with a UNION ALL. The first SELECT-statement retrieves the top level of the hierarchy by retrieving the members that do not have a ManagerID, the 1 AS LVL symbolizes the top level. The second SELECT-statement joins the table against the resultset of the CTE based on cte.EMPID = emp.ManagerID, and in the same time we add 1 to cte.LVL to symbolize the level of the current member. The UNION ALL adds the rows retrieved in the second SELECT-statement with the resultset of the CTE. The second SELECT-statement is then repeated until there are no further rows to retrieve or if the MAXRECURSION-level is reached, by default 100. 
 
This second CTE retrieves the full hierarchy down to the level where a certain member resides.

WITH cte (LVL, EMPID, MGRID )
AS (
    SELECT
        1 AS LVL , EmployeeID AS EMPID, ManagerID AS MGRID
        FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT
        cte.LVL + 1, emp.EmployeeID, emp.ManagerID
    FROM HumanResources.Employee emp
    INNER JOIN cte ON emp.ManagerID = cte.EMPID
)
SELECT * FROM cte
WHERE LVL <= ( SELECT LVL FROM cte WHERE EMPID = 279 )
ORDER BY LVL ASC

The third example retrieves the direct chain of parents for a certain member. A higher LVL means in this example higher up in the hierarchy.

WITH cte (LVL, EMPID, MGRID )
AS (
    SELECT
        1 AS LVL, EmployeeID AS EMPID, ManagerID AS MGRID
    FROM HumanResources.Employee
    WHERE EmployeeID = 279
    UNION ALL
    SELECT
        cte.LVL + 1, emp.EmployeeID, emp.ManagerID
    FROM HumanResources.Employee emp
    INNER JOIN cte ON cte.MGRID = emp.EmployeeID
)
SELECT * FROM cte
ORDER BY LVL DESC