Please Vote: Windowing Enhancements in SQL Server

Mike C — Fri, 20 Nov 2009 03:45:00 GMT

Back in January 2007, SQL guru Itzik Ben-Gan posted a series of MS Connect enhancement requests concerning windowing function enhancements. Those who have used the ROW_NUMBER() , RANK() , DENSE_RANK() , and NTILE() functions on SQL 2005 and 2008, you...(read more)

Practical SQL Server XML: Part 2

Mike C — Wed, 04 Mar 2009 02:28:00 GMT

In response to a challenge from Pinal Dave, a fellow MVP and a winner of an award for his blog at the MVP Summit 2009, I am writing a couple of articles to demonstrate practical uses for SQL Server’s XML capabilities. (Dave’s great blog is at http://blog.sqlauthority.com/).

Dave posted the first post in this series over at http://blog.sqlauthority.com/2009/03/17/sql-server-practical-sql-server-xml-part-one-query-plan-cache-and-cost-of-operations-in-the-cache/, describing how to shred the XML query plans in the SQL Server query plan cache. In this post I’ll talk about sending multiple parameters, and even parameter sets, to SQL Server via XML. SQL Server MVP Jacob Sebastian is finishing up the series with a post on his blog at http://blog.beyondrelational.com/2009/03/practical-sql-server-xml-part-three.html.

One of the most common requests in the SQL Server newsgroups is “how do I send a delimited list of values to my stored procedures?” There are many creative answers, including SQL MVP Erland Sommarskog’s popular article “Arrays and Lists in SQL Server” (http://www.sommarskog.se/arrays-in-sql-2005.html). Erland touches on the method of XML shredding that I’ll cover here.

For this example I’ll use the AdventureWorks 2008 database. This sample database is available for download from CodePlex (http://sqlserversamples.codeplex.com/) for SQL Server 2008. For this example I’ll first create a procedure that inserts a single record that must be put in two separate tables:

CREATE PROCEDURE Person.AddPerson

@PersonType nchar(2) = NULL,

@NameStyle dbo.NameStyle = NULL,

@Title nvarchar(8) = NULL,

@FirstName dbo.Name = NULL,

@MiddleName dbo.Name = NULL,

@LastName dbo.Name = NULL,

@Suffix nvarchar(10) = NULL,

@EmailPromotion int = NULL,

@EmailAddress nvarchar(50) = NULL

BEGIN

-- Get a new BusinessEntityID

DECLARE @BusinessEntityID int;

INSERT INTO Person.BusinessEntity(rowguid)

VALUES (NEWID());

SET @BusinessEntityID = SCOPE_IDENTITY();

-- Insert Person

INSERT INTO Person.Person

(

BusinessEntityID,

PersonType,

NameStyle,

Title,

FirstName,

MiddleName,

LastName,

Suffix,

EmailPromotion

)

VALUES

(

@BusinessEntityID,

@PersonType,

@NameStyle,

@Title,

@FirstName,

@MiddleName,

@LastName,

@Suffix,

@EmailPromotion

);

-- Insert Email Address

INSERT INTO Person.EmailAddress

(

BusinessEntityID,

EmailAddress

)

VALUES

(

@BusinessEntityID,

@EmailAddress

);

END

The procedure simply accepts several parameters that reflect a single Person record. The first step is to insert a row into the Person.BusinessEntity table to get a business entity ID. Then the remaining parameter values are inserted into the Person.Person and Person.EmailAddress tables. Simple enough. But what if you want to insert 2 Person records? You can round-trip to the server twice with two calls to this procedure, or you can double up the number of parameters and add the extra code in the procedure to deal with it.

But then again, what if you want to add 100 Person records? How about a thousand or a million? That’s a lot of round trips to the server or a lot of extra parameters to add (SQL Server actually maxes out at around 2,100 parameters). If you have SQL Server 2008 and you’re using a .NET client you can use table-valued parameters. But what if you’re not using a .NET client or SQL 2005 is your target database? In that case you can use XML to accomplish the same thing.

Using XML to add data to a single table is normally a pretty straightforward shredding operation, but we’re actually touching three different tables in this case. And one of the tables generates an identity value that the other two tables need. This means the code is not so simple as a straight single-table insert, especially if you decide not to use cursors. Here’s my XML version of adding a Person:

CREATE PROCEDURE Person.AddPersonXML

@People xml = NULL

BEGIN

-- Generate and store all new business entity IDs

DECLARE @TemporaryIDs TABLE

(

BusinessEntityID int NOT NULL

);

-- Number the XML nodes and generate business entity IDs

WITH NodeNumbers

(

SELECT

ROW_NUMBER() OVER (ORDER BY C.value('PersonType', 'nchar(2)')) AS NodeNumber

FROM @People.nodes('//Person') T(C)

)

INSERT INTO Person.BusinessEntity

(

rowguid

)

OUTPUT

inserted.BusinessEntityID

INTO @TemporaryIDs

(

BusinessEntityID

)

SELECT NEWID()

FROM NodeNumbers nn;

WITH PersonNodes

(

SELECT

ROW_NUMBER() OVER (ORDER BY C.value('PersonType[1]', 'nchar(2)')) AS JoinID,

C.query('.') AS PersonData

FROM @People.nodes('//Person') T(C)

BusinessEntityIDs

(

SELECT

ROW_NUMBER() OVER(ORDER BY BusinessEntityID) AS JoinID,

BusinessEntityID

FROM @TemporaryIDs

GetPersonData

(

SELECT

bei.BusinessEntityID,

pn.PersonData.value(N'(Person/PersonType)[1]', N'nchar(2)') AS PersonType,

pn.PersonData.value(N'(Person/NameStyle)[1]', N'dbo.NameStyle') AS NameStyle,

pn.PersonData.value(N'(Person/Title)[1]', N'nvarchar(8)') AS Title,

pn.PersonData.value(N'(Person/FirstName)[1]', N'dbo.Name') AS FirstName,

pn.PersonData.value(N'(Person/MiddleName)[1]', N'dbo.Name') AS MiddleName,

pn.PersonData.value(N'(Person/LastName)[1]', N'dbo.Name') AS LastName,

pn.PersonData.value(N'(Person/Suffix)[1]', N'nvarchar(10)') AS Suffix,

pn.PersonData.value(N'(Person/EmailPromotion)[1]', N'int') AS EmailPromotion

FROM PersonNodes pn

INNER JOIN BusinessEntityIDs bei

ON pn.JoinID = bei.JoinID

)

INSERT INTO Person.Person

(

BusinessEntityID,

PersonType,

NameStyle,

Title,

FirstName,

MiddleName,

LastName,

Suffix,

EmailPromotion

)

SELECT

BusinessEntityID,

PersonType,

NameStyle,

Title,

FirstName,

MiddleName,

LastName,

Suffix,

EmailPromotion

FROM GetPersonData;

WITH PersonNodes

(

SELECT

ROW_NUMBER() OVER (ORDER BY C.value('PersonType[1]', 'nchar(2)')) AS JoinID,

C.query('.') AS PersonData

FROM @People.nodes('//Person') T(C)

BusinessEntityIDs

(

SELECT

ROW_NUMBER() OVER(ORDER BY BusinessEntityID) AS JoinID,

BusinessEntityID

FROM @TemporaryIDs

GetPersonData

(

SELECT

bei.BusinessEntityID,

pn.PersonData.value(N'(Person/EmailAddress)[1]', N'nvarchar(50)') AS EmailAddress

FROM PersonNodes pn

INNER JOIN BusinessEntityIDs bei

ON pn.JoinID = bei.JoinID

)

INSERT INTO Person.EmailAddress

(

BusinessEntityID,

EmailAddress

)

SELECT

BusinessEntityID,

EmailAddress

FROM GetPersonData;

END

You notice in this example that we first have to get business entity IDs for each person. I use a table variable and the DML OUTPUT clause to grab the business entity IDs ahead of time. Then I shred the XML in two steps, once to put relevant data in the Person.Person table and another to put the email addresses in Person.EmailAddress. The procedure call looks like this:

EXEC Person.AddPersonXML N'<Person>

<FirstName>George</FirstName>

<LastName>Jetson</LastName>

<EmailAddress>George.Jetson@spacelysprockets.com</EmailAddress>

</Person>

<LastName>Flintstone</LastName>

<EmailAddress>Fred.Flintstone@sladegravelpit.com</EmailAddress>

</Person>';

As you can see, we can easily add as many people as you want with a single XML parameter. That is, so long as you respect the 2.1 GB size limit on SQL Server LOB data types like XML. Also since you are using XML you can use any client application in any language that can generate XML character data; you’re not limited to just any .NET-specific functionality.

You can verify that the people above were added with a few simple queries:

SELECT *
FROM Person.Person
WHERE BusinessEntityID > 20777;

SELECT *
FROM Person.EmailAddress
WHERE BusinessEntityID > 20777;

SELECT *
FROM Person.BusinessEntity
WHERE BusinessEntityID > 20777;

The results look like the following:

In the third article of this series at http://blog.beyondrelational.com/2009/03/practical-sql-server-xml-part-three.html, MVP and author Jacob Sebastian will show you how to add XML Schema validations to your xml data type parameter sets.

Michael Coles: Sergeant SQL : query, SQL

Please Vote: Windowing Enhancements in SQL Server

Practical SQL Server XML: Part 2