Paging With SQL Server “Denali”

One of the more common tasks in a data-bound application is to display data in a “page-able” user interface – often some sort of data grid. So for example, you might want to create a Web application that shows a list of available products, but which limits the list to ten products at a time. The user then “pages” through the data by clicking “Next Page” or something similar. There are loads of user interface design and implementation patterns that enable this kind of functionality, but they usually either involve fetching all of the data from the database and caching it locally in the application, or implementing some sort of “current page” tracking functionality to adjust the query used to retrieve the data on each page.

SQL Server “Denali” introduces a new way to manage paging within the SELECT statement itself. More specifically, you can use the new OFFSET and FETCH keywords in the ORDER BY clause to limit the query results to a specific page of data. The OFFSET keyword is used to indicate the “starting row” of the results (i.e. the number of rows to skip before this page), and the FETCH keyword is used to indicate the number of pages to be returned (i.e. the page size). For example, the following query skips 20 rows in the underlying dataset and then returns the next 10 rows:

SELECT so.SalesOrderID, so.OrderDate, c.CustomerName
FROM SalesOrder so
JOIN Customer c ON so.Customer = c.CustomerID
ORDER BY SalesOrderID ASC
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY

For this to work predictably, the ORDER BY clause must include a unique column (or combination of columns) and the underlying dataset must not change between queries.

With this functionality, you can implement an effective paging solution that tracks the position of the first row in the current page, the first row in the next page, and the first row in the previous page. For example, the following stored procedure retrieves the requested page of data based on page size and offset parameter values, and then returns the first row positions for the next and previous pages:

CREATE PROCEDURE GetSalesOrders(@PageSize int, @Offset int,
                                @NextPage int OUTPUT, @PrevPage int OUTPUT)
AS
-- Retrieve the requested page of data
SELECT so.SalesOrderID, so.OrderDate, c.CustomerName
FROM SalesOrder so
JOIN Customer c ON so.Customer = c.CustomerID
ORDER BY SalesOrderID ASC
OFFSET @Offset ROWS
FETCH NEXT @PageSize ROWS ONLY

-- Set the row position markers
SET @NextPage = @@ROWCOUNT + @Offset
SET @PrevPage = @Offset - @PageSize
GO

You can then call this stored procedure to navigate forward and backward through the data like this:

DECLARE @StartRow int = 0, @Next int, @Prev int
EXECUTE GetSalesOrders 10, @StartRow, @Next OUTPUT, @Prev OUTPUT
SET @StartRow = @Next
EXECUTE GetSalesOrders 10, @StartRow, @Next OUTPUT, @Prev OUTPUT
SET @StartRow = @Next
EXECUTE GetSalesOrders 10, @StartRow, @Next OUTPUT, @Prev OUTPUT
SET @StartRow = @Prev
EXECUTE GetSalesOrders 10, @StartRow, @Next OUTPUT, @Prev OUTPUT

This code calls the stored procedure 4 times, retrieving the initial page (with an offset of zero), the next two pages, and then the second page again, as shown in the results here:

SalesOrderID OrderDate  CustomerName
------------ ---------- ------------------------------
1            2010-01-20 Kasumi Fernandez
2            2010-01-21 Rod Dechamps
3            2010-01-22 Jane Dechamps
4            2010-01-23 Freddy Dechamps
5            2010-01-24 Pierre Dechamps
6            2010-01-25 Kasumi Dechamps
7            2010-02-01 Rod Smith
8            2010-02-01 Jane Smith
9            2010-02-01 Freddy Smith
10           2010-02-01 Pierre Smith

(10 row(s) affected)

SalesOrderID OrderDate  CustomerName
------------ ---------- ------------------------------
11           2010-02-01 Kasumi Smith
12           2010-02-01 Rod Jones
13           2010-02-01 Jane Jones
14           2010-02-01 Freddy Jones
15           2010-02-01 Pierre Jones
16           2010-02-01 Kasumi Jones
17           2010-02-01 Rod Yamamoto
18           2010-02-01 Jane Yamamoto
19           2010-02-01 Freddy Yamamoto
20           2010-02-01 Pierre Yamamoto

(10 row(s) affected)

SalesOrderID OrderDate  CustomerName
------------ ---------- ------------------------------
21           2010-02-01 Kasumi Yamamoto
22           2010-02-01 Rod Fernandez
23           2010-02-01 Jane Fernandez
24           2010-02-01 Freddy Fernandez
25           2010-02-01 Pierre Fernandez
26           2010-02-01 Kasumi Fernandez
27           2010-02-01 Rod Dechamps
28           2010-02-01 Jane Dechamps
29           2010-02-01 Freddy Dechamps
30           2010-02-01 Pierre Dechamps

(10 row(s) affected)

SalesOrderID OrderDate  CustomerName
------------ ---------- ------------------------------
11           2010-02-01 Kasumi Smith
12           2010-02-01 Rod Jones
13           2010-02-01 Jane Jones
14           2010-02-01 Freddy Jones
15           2010-02-01 Pierre Jones
16           2010-02-01 Kasumi Jones
17           2010-02-01 Rod Yamamoto
18           2010-02-01 Jane Yamamoto
19           2010-02-01 Freddy Yamamoto
20           2010-02-01 Pierre Yamamoto

(10 row(s) affected)

Note that this simple example doesn’t handle the issue of “falling off the end” of the underlying dataset, so an attempt to move forward beyond the last page will return an empty result set, and an attempt to move backward to a position before the first row in the dataset will result in an error (since the OFFSET value cannot be less than zero). You could easily add some basic validation checks in the stored procedure to account for this (for example, resetting @PrevPage to 0 if it becomes negative or setting @NextPage to COUNT(*) - @PageSize if it gets larger than the underlying dataset).

You can download SQL Server “Denali” CTP1 from here, a script to create the sample database I used for the above example from here, and the paging example shown in this article from here.

del.icio.us Tags: SQL Server Denali

SQL Server “Denali” – Promising the Earth!

I’ve previously posted several articles about spatial data support in SQL Server, and as I continue my exploration of SQL Server “Denali” CTP1, I’ve encountered a few interesting new enhancements to the geometry and geography data types. I won’t go into full details here, because Ed Katibah and Milan Stojic have already saved me the trouble by writing a whitepaper that provides comprehensive round-up of the changes in this release; however, I do want to make a few observations relating to the new enhanced support for curved lines and shapes.

SQL Server “Denali” introduces a few new spatial shapes, including CIRCULARSTRING, COMPOUNDCURVE, and CURVEPOLYGON. A CIRCULARSTRING line is a sequence of an odd number of at least three points, which are connected to form a curved arc. For example, consider the following Transact-SQL:

DECLARE @g geography = 'CIRCULARSTRING(-4.115 55.778, -3.399 56.990, -2.237 54.009)'

This creates a geography instance that represents a curved line like this:

Compare this to the line produced by using the LINESTRING shape, as shown here:

DECLARE @g geography = 'LINESTRING(-4.115 55.778, -3.399 56.990, -2.237 54.009)'

Of course, both of these lines are “open”. You can create a closed CIRCULARSTRING line by defining at least five points and making the final point in the line the same as the first, as shown here:

DECLARE @g geography = 'LINESTRING(-4.115 55.778, -3.399 56.990, -2.237 54.009, -3.168 53.863, -4.115 55.778)'

A COMPOUNDCURVE is a curved shape that is composed of one or more CIRCULARSTRING arcs and linear sections that are contiguously joined by having the final point in each segment the same as the first point in the next segment. For example, the following Transact-SQL creates a COMPOUNDCURVE shape from two CIRCULARSTRING arcs and a linear section. Note that you do not specify a keyword for the linear sections.

DECLARE @g geography = 'COMPOUNDCURVE(
                          CIRCULARSTRING(-4.000 55.000, -4.500 54.500, -4.000 54.000),
                          (-4.000 54.000, 1.000 54.000),
                          CIRCULARSTRING(1.000 54.000, 1.500 54.500, 1.000 55.000))'

A CURVEPOLYGON is a surface area that is formed by a closed curved line, which can be defined by a CIRCULARSTRING or a COMPOUNDCURVE. When working with the geometry data type, the points in the curved area can be defined in any order, but when using he geography type, you must observe the “left foot rule”, which dictates that you must describe the shape as if you were pacing it out on the ground and the “inside” of the shape is always on your left. For example, here’s a Transact-SQL statement that defines a CURVEPOLYGON based on a CIRCULARSTRING:

SELECT geography::Parse('CURVEPOLYGON(

                           CIRCULARSTRING(-4.889 55.844,

                                          -3.924 55.738,

                                          -2.731 56.058,

                                          -4.201 56.134,

                                          -4.889 55.844)

                                       )')

This defines an area in the UK within the so-called “central belt” of Scotland, as shown here. Note that the points describe the shaded area, which is what would be on your left if you paced out the area from point to point in the sequence in which they are specified.

Now, what happens if I reverse the order of the points as shown here?

SELECT geography::Parse('CURVEPOLYGON(

                           CIRCULARSTRING(-4.889 55.844,

                                          -4.201 56.134,

                                          -2.731 56.058,

                                          -3.924 55.738,

                                          -4.889 55.844)

                                       )')

The “left foot rule” clearly tells SQL Server to include everything on my left side as I pace out the shape, and since the Earth is a sphere, this shape actually describes the entire surface of the planet except for the “hole” defined by the points in the CIRCULARSTRING.

 

In previous releases of SQL Server, this would have caused an error because shapes larger than a hemisphere were not supported. However, in SQL Server “Denali”, you can create a shape that covers as much of the surface of the Earth as you like, so this code is perfectly valid. Clearly, the message here is that you need to be very careful when defining surface areas (be they CURVEPOLYGON or regular POLYGON shapes) to apply the “left foot rule” to include the surface area you actually intend to, and not the rest of the world!

Speaking of which, one other new feature worth mentioning is the inclusion of a FULLGLOBE shape, which returns a surface area that covers, you guessed it, the full globe. For example, the following code returns the area of the planet’s surface in square kilometres:

DECLARE @theEarth geography = geography::STGeomFromText('FULLGLOBE', 4326)
SELECT @theEarth.STArea()/1000000

The Spatial Reference Identifier (SRID) 4326 specifies that the WGS84 standard model of the earth’s shape should be used, which means that the result of the call to the  STArea method is returned in square metres, which we then divide by 1000000 to get the answer in square kilometres.

And just in case you’re interested, the Earth’s surface is just a little over  510,065,621 km2!

del.icio.us Tags: SQL Server Denali

Contained Databases in SQL Server “Denali”

OK, so here’s a common scenario. You’ve developed an application that uses a SQL Server database on a development machine, and now you need to deploy the application (and its database) to a staging or production environment. Of course, you can generate scripts to recreate the database on another server, or you could simply back it up and restore it to the other server. However, while that would successfully move the database, it would not take across any server-level objects that the application depends on, such as logins or Agent jobs. SQL Server 2008 R2, introduced Data-Tier applications as a way to package up databases with their server-level dependencies and deploy them to another server (which I covered in a previous blog post), and this is a great step forward, but to be honest, wouldn’t it be nice if the application database was simply self-contained with no need to rely on server-level objects at all?

Well, in SQL Server “Denali”, you get your wish in the form of Contained Databases. When you create a database, you now have the option of setting its containment type property to partial, which enables you to create “contained” objects that would normally be defined at the server-level within the database itself – most notably logins. The “partial” containment type value specifies that the database can use a mixture of contained and non-contained objects (so for example, traditional server-level logins can be used to access the database as well as contained logins) – a further option of “full” containment (which disallows the use of non-contained objects) is promised but not supported in the current CTP 1 release.

So let’s see an example of how to create and use a contained database. First, you need to enable contained databases in the server. You can do this in the Server Properties dialog box as shown here, or you can use the following Transact-SQL code:

sp_configure 'show advanced options', 1 ;

GO

RECONFIGURE ;

GO

sp_configure 'contained database authentication', 1;

GO

RECONFIGURE ;

GO

sp_configure 'show advanced options', 0 ;

GO

RECONFIGURE ;

GO

After you’ve enabled contained databases, you can create one like this:

CREATE DATABASE [MyContainedDB]

CONTAINMENT = PARTIAL

GO

Or alternatively, you can use the New Database dialog box in SQL Server Management Studio and set the Containment type property as shown here.

 

 

 

 

 

Now that you have a contained database, you can create users within it. The important point here is that you can create users that do not map to server-level logins, so the traditional dependency between a user object in a database and a login object in the server instance where that database is hosted is broken. To create a contained SQL Server user that has a password, you can use a Transact-SQL CREATE USER statement in the contained database like this one:

USE [MyContainedDB]

GO

CREATE USER [MyContainedUser] WITH PASSWORD = ‘5up3r53cret’

GO

Or, you can create a contained user for a Windows account like this:

CREATE USER [DEVBOX\MyAppAccount]

GO

Of course, you can also use the graphical tools in SQL Server Management Studio to create a contained user, as shown here:

Note that if you want to use a SQL Server user, you need to enable so-called “mixed mode” authentication at the server-level – even though there’s no server-level SQL Server logins.

As stated before, contained users do not rely on server-level logins, they exist only within the contained database. This means that when you want to connect to the contained database from a client application, you need to specify the database name in the connection string or the connection will fail because SQL Server will attempt to default to the master database and try to authenticate your credentials as a server-level login. In a typical client application, you can specify the database in a connection string like this:

“SERVER=mysqlserver; DATABASE=MyContainedDB; USER ID=MyContainedUser; PASSWORD=5up3r53cret”

When connecting from tools like SQL Server Management Studio, you can specify the database name in the connection dialog box like this:

The ability to create contained databases that include users with no dependency on server-level logins can be extremely useful when you need to move the database from one server to another. However, you should be aware of the security implications of using database-specific authentication that effectively bypasses the usual security management architecture. You can find out more about these security implications here.

del.icio.us Tags: SQL Server Denali

Getting Started with SQL Azure

Towards the end of last year, I was the lead author on Microsoft Learning course 10337A: Updating Your Microsoft SQL Server 2008 BI Skills to SQL Server 2008 R2. While this is a course primarily for BI developers, we included a module on SQL Azure; and to make the course work in a classroom, we created a Silverlight-based simulation that students can use to walk through the steps required to set up and use a SQL Azure database. Additionally, Hilton Giesnow presents a useful video introduction to SQL Azure on the MSDN site.

However, the release cycle for cloud-based technology moves even faster than that of traditional software products, and predictably enough, the Azure team at Microsoft has created a new version of the Web portal used to manage Azure platform subscriptions – including SQL Azure. So, I thought it might be useful to provide a short walkthrough based on the latest portal (before they go and change it again!)

To start with, you need to sign-up for an Azure subscription at http://www.microsoft.com/windowsazure/offers/. It may take as long as a couple of days to provision your account, but mine took less than half an hour. After you’ve signed up, you’ll be able to access the Azure portal using your Windows Live ID:

To create a SQL Azure server (which you can think of as a cloud-based computer running an instance of SQL Server – it’s not, but you can think of it like that!), click the New Database Server button in the toolbar (which I guess like everything else these days is probably more properly called the “ribbon”), which will display all of the Azure subscriptions your Windows Live ID is associated with that include SQL Azure services. In this case, I have a single subscription named CM Azure Subscription, which currently has no SQL Azure database servers defined in it.

No you can select the subscription in which you want to create the SQL Azure server, and click the Create button in the toolbar/ribbon, which will start a wizard, the first step in which is to select the geographical location where you want the server to be hosted:

After selecting a location, you need to specify the Administrator credentials for the server. There are some restrictions on login name and password complexity (for example, you can’t create a login named Administrator with the password password).

Next, the wizard prompts you to specify the firewall rules that control connectivity to your server. By default, nothing (including any other Azure services you may have) can access your server, so you’ll typically want to enable access for Windows Azure services as shown here:

Additionally, you’ll probably want to allow at least some computers to connect to the server across the Internet – even if initially this is limited to your own development workstation. To do this, you’ll need to add a firewall rule that specifies a range of IP addresses from which you want to enable connectivity. In this example, I’ve created a rule that allows connections from any computer on the Internet.

After completing the wizard, your SQL Azure server is provisioned with a name that looks like a random stream of characters and shows up in the Database section of the Azure portal as shown here:

Now that you have a SQL Azure server, you can select it in the Azure portal and create databases in it by clicking the Create button in the ribbon. Doing this results in a prompt for a name, edition, and size for your database. There are two editions available (Web and Business), each with their own range of possible sizes (and corresponding prices), so when you select an edition, the available sizes will reflect the sizes supported by that edition. In this example, I’ve created a 1GB Web edition database.

After creating your database, you can view its properties in the portal as shown here:

The latest version of the Azure portal includes a management tool that you can use to manage your SQL Azure database. To launch this, simply select the database you want to manage in the Azure portal and click Manage in the ribbon. The first time you do this you’ll be prompted to accept the terms and conditions for the management tool, and then the tool itself will open in a new browser window and prompt you to log in as shown here:

You can log in using the administrator credentials you specified when you provisioned the SQL Azure server, and start the manage the database by using the management user interface as shown here:

Additionally, if you have installed SQL Server Management Studio for any edition of SQL Server 2008 R2 (including the free Express edition), you can connect to your SQL Azure server and manage it by specifying the fully-qualified server name and SQL Server login credentials as shown here:

Note that the server name takes the form YourSQLAzureServerName.database.windows.net, and the login name takes the form YourAdminLoginName@YourSQLAzureServerName.

SQL Azure servers are displayed in SQL Server Management Studio like this:

From here, you can manage your SQL Azure database in a similar way to how you manage on-premise SQL Server instances, though you’ll find that there are some SQL Server features that are not supported by SQL Azure. You can also connect to your SQL Azure database from client applications in a similar fashion to on-premise SQL Server databases by specifying the fully-qualified SQL Azure server name in the connection string.

For more information about SQL Azure, see http://msdn.microsoft.com/en-us/library/ff937661.aspx.

del.icio.us Tags: SQL Azure