snaps & snippets

See "The First One"

RSS Feed
MVP – Windows Server System – SQL Server

Current posts | Archives | Links | Popular | Technorati | Company site

Snaps | Snippets

Friday, July 20, 2007

Six Months

»Become a better developer in six months.« Define 'better'. :)

I've been tagged by Denis 'The SQL Menace' Gobo to share my views on how I would go about becoming a better developer in the next six months.

(Self-)education is the first thing that comes to mind, and the majority of the tag-attack "victims" agree that reading and learning (and writing about it) directly lead to being better at development; myself included. However, the kind of literature that I consider important (in view of my current work) may have nothing or at least very little to do with software or database development. As some of you know, my principal calling is law and legal science, so if I plan on being better at SQL development at the moment then I'll have to brush up on these:

  • Legal philosphy and legal theory;
  • Core legal concepts in individual legal disciplines (property law, obligations, criminal law, civil and criminal procedure – to name but a few);
  • Legal argumentation; and
  • The institutions of law (judiciary, legislature, executive – including police and military).

But the key (IMHO) to improving the professional skill-set lies elsewhere. »Staying hungry« is an old principle that I find highly beneficial to self-development.

What am I talking about? Allow me to illustrate. Every boxing coach will work hard at keeping "the champ" fit to fight and at or near his peak. But a good coach will also keep the boxer from reaching the peak of his skills *before* the big match – for instance: stopping a practice fight before time, changing sparring partners to distract the boxer, or doing other more or less annoying things. In other words: keeping the champ "hungry" as well as fit.

Law? Hunger? Boxing? What do these have to do with development and being better at it?

All I'm really trying to say is that spending time away from the workplace, away from work, and – most importantly – away from the PC screen actually provides a distraction that might seem insignificant or even counter-productive but is in effect a great way of getting the creative juices flowing. I'm not saying the brain should be switched off – not at all! Keep your brain active, but do something different. And NO! Watching TV simply won't do.

I see Adam is thinking along the same lines.

I've mentioned before (I believe it was in one of the newsgroups) that I keep a guitar next to my desk. What do *you* do while waiting for SSMS or SSIS to finish, especially with longer operations?

I'm not going to tag anyone (I tend not to be a menace ;), but I'd still be interested in what you think or what you'd do to become better at whatever you do professionally.


Friday, July 06, 2007

SQL Server 2005 XML Methods, Part Four, The Existential Quandary

We've seen SQL Server 2005 XML data retrieval methods in previous posts. In particular we've seen how they perform compared to T-SQL retrieval methods, and – quite frankly – the results were somewhat disappointing.

There is an XML retrieval method that we haven't discussed yet. There are two reasons why not:

  • It's not really a retrieval method as much as it is a way of testing whether nodes and/or data exists in a given XML instance; and
  • It's not as flexible as the other methods since it takes testing query conditions out of the »good and safe« relational world and into the depths of the XML (under)world. In other words: as XML is by definition case-sensitive, so is the method we'll be looking at in this post.

Cut to the chase!

I'm of course referring to the exist() XML method. Its purpose is to test whether a given XQuery expression against a given XML instance returns a node set or not.

The method accepts an XQuery expression as its sole parameter, and returns a scalar value:

  • 1 if the XQuery expression returns a nonempty result;
  • 0 if it returns an empty result; or
  • null if executed against a null XML instance.

You can find all the details in Books Online. Incidentally, have you tried the new SQL Server 2005 Books Online Scoped Search yet?

To provide a better understanding of what our example is aimed at here's the original query:

select Person.Contact.ContactID as ContactID
 from Person.Contact
 where (Person.Contact.MiddleName = @middleName)

Our objective is to retrieve a list of ContactID values for contacts in the Person.Contact table of the AdventureWorks database with »Z.« as their MiddleName.

One row corresponds to the given criteria (that's 1 row out of 19972 in 559 pages).

First of all, let's compare the performance of the query against the original table with only the clustered primary key and then with a covering index. The definition of the covering index is available in the footnotes of this post (just follow the links from the table).

Query Performance
Primary Key Only Covering Index
CPU Reads CPU Reads
Relational 6 107 0 3

Excellent. On both counts. Would anyone expect anything else on a table of this size?

War of the worlds?

Let's attempt at achieving our objective where the data actually resides. In Part Two of the series we've used this script to create two tables of customer data from the AdventureWorks database with each customer's first, middle and last names packed up in two different XML instances. Two tables will thus be created:

  • One with the data in an EAV XML model: 19972 rows in 12519 pages (299580 rows in 2020 pages used by XML indexes);
  • The other with the data in a strongly typed XML model: 19972 rows in 6930 pages (119832 rows in 733 pages used by XML indexes).

We'll be using two pairs of queries (one pair for each XML type): a pair where only the value of the sought node is parameterized, and another pair of queries with both the value as well as the name of the target node supplied through parameters. Once again, the queries are located at the bottom of this post. Oh, and you'll need this, too:

declare @elementName sysname
declare @middleName varchar(8)

set @elementName = 'middleName'
set @middleName = 'Z.'

Now, witness the power of exist.

Query Performance
All XML Indexes
CPU Reads
#1 4 19
#2 4 19
#3 0 7
#4 4 13

Not bad at all! Mind you that we've supplied the sought value in the correct (upper) case – »z.« (lower-case) would not have returned anything.

Judging by the number of reads, data access is minimal – processing can stop as soon as the condition is met, and no data actually needs to be retrieved from the XML. With appropriate XML indexes the XML instances do not have to be accessed at all.

Observe, how XML indexes have been used in this case by looking at the execution plans:

NB: I use the following prefixes for XML index names:

  • pxx_* – primary XML index;
  • axx_* – secondary XML index for path;
  • vxx_* – secondary XML index for value; and
  • rxx_* – secondary XML index for property.

The price of case-insensitivity

The exist method operates in the XML world, which means that the value supplied to the XQuery expression must be in the correct case. Supplying node names should not present a problem in this regard because we can rely on the given XML model (i.e. using XML Schema is highly recommended – we do want domain integrity, don't we?). This, however, most probably won't be enough to help us supply the data in the correct case. So, what alternatives do we have if we decide on using XML to store data in our SQL Server 2005 database?

  1. Limit the use of the exist method to tests where the case of the data is either unambiguous or irrelevant (i.e. only for numeric values);
  2. Supply the value in any case and then use an appropriate XPath function in the XPath/XQuery expression to compensate for the lack of proper casing;
  3. Keep a lower-case (or upper-case) copy of each XML instance (e.g. in a separate column or table or in an [indexed] view); or
  4. Move condition testing out of the XML (i.e. use the value XML method to extract data and test the conditions in T-SQL).

Let's forget about option 1 for a moment as it is as ridiculous as it sounds. Option 2 is not even possible at this time, since none of the appropriate XPath functions (lower-case(), upper-case() or translate()) are currently supported by SQL Server 2005. This leaves us with options 3 and 4 – both come at a price.

Keeping a lower-case copy of each XML instance as suggested by option 3 will inevitably increase the size of the data store, so we must ask ourselves whether the increased performance of searching XML instances using the exist XML method is worth the growth of the database. And there's also the question of maintenance: how will having to keep two copies of the same data affect inserts, updates and deletes (both the logic behind data modifications as well as the performance)?

The answers to both questions depend on the actual case, and may even in the near future become irrelevant if we consider the probability that the XPath/XQuery W3C Recommendations will be implemented in a future release of SQL Server (or be introduced through a service release).

Of course, we can always bring search out of XML and into the relational world, as suggested by option 4. Compare the following counters (and queries) with those presented earlier:

Query Performance
All XML Indexes
CPU Reads
#5 14176 514491
#6 17391 635503
#7 6882 147733
#8 12620 207649

"Isn't that nice..." he mumbled to himself sarcastically. Case-insensitivity doesn't come cheap.

Care to compare execution plans?

Pay special attention to the size of data in each step (also signified by the weight of the arrows). Heavy stuff.

Mental aggregation

Obviously, the exist XML method has merit – heck, it even outperforms pure T-SQL (in the absence of appropriate indexes). Nevertheless, the nature of XML, namely its case-sensitivity, quite significantly limits the usability of this otherwise highly efficient method.

And – I must say this, because I always do – SQL Server seems to prefer Typed XML (compared to the EAV XML model).


  • Covering index on the Person.Contact table of the AdventureWorks database:
    create nonclustered index x_Person_Contact_MiddleName
     on Person.Contact
  • Query #1: EAV, fixed XPath, parameterized value, test in XML:
    select ANV.AttributeId
     from dbo.AttributesNameValue ANV
     where (ANV.Attribute.exist(
       '/attributeCollection/attribute[data(name) = "middleName"]
        /value[data(.) = sql:variable("@middleName")]'
       ) = 1)
  • Query #2: EAV, parameterized XPath and value, test in XML:
    select ANV.AttributeId
     from dbo.AttributesNameValue ANV
     where (ANV.Attribute.exist(
       '/attributeCollection/attribute[data(name) = sql:variable("@elementName")]
        /value[data(.) = sql:variable("@middleName")]'
       ) = 1)
  • Query #3: Typed, fixed XPath, parameterized value, test in XML:
    select AT.AttributeId
     from dbo.AttributesTyped AT
     where (AT.Attribute.exist(
       '/attributeCollection/middleName[data(.) = sql:variable("@middleName")]'
       ) = 1)
  • Query #4: Typed, parameterized XPath and value, test in XML:
    select AT.AttributeId
     from dbo.AttributesTyped AT
     where (AT.Attribute.exist(
       '/attributeCollection/*[local-name() = sql:variable("@elementName")]
        [data(.) = sql:variable("@middleName")]'
       ) = 1)
  • Query #5: EAV, fixed XPath, parameterized value, test in SQL:
    select ANV.AttributeId
     from dbo.AttributesNameValue ANV
     where (ANV.Attribute.query(
       '/attributeCollection/attribute[data(name) = "middleName"]/value'
       ).value('.', 'varchar(32)') = @middleName)
  • Query #6: EAV, parameterized XPath and value, test in SQL:
    select ANV.AttributeId
     from dbo.AttributesNameValue ANV
     where (ANV.Attribute.query(
       '/attributeCollection/attribute[data(name) = sql:variable("@elementName")]/value'
       ).value('.', 'varchar(32)') = @middleName)
  • Query #7: Typed, fixed XPath, parameterized value, test in SQL:
    select AT.AttributeId
     from dbo.AttributesTyped AT
     where (AT.Attribute.query(
        ) = @middleName)
  • Query #8: Typed, parameterized XPath and value, test in SQL:
    select AT.AttributeId
     from dbo.AttributesTyped AT
     where (AT.Attribute.query(
       '/attributeCollection/*[local-name() = sql:variable("@elementName")]'
        ) = @middleName)

Wednesday, July 04, 2007

SQL Server 2005 Best Practices Analyzer

SQL Server 2005 Best Practices Analyzer has been released on July 2nd, and is available at this site:


2007 Microsoft MVP Award

I am extremely pleased to tell you, dear reader(s), that on July 1st I have been presented with the 2007 Microsoft MVP award in the »Windows Server System – SQL Server« category.

I would like to take this opportunity to thank everybody at the MVP Program for considering my participation in the communities to be worthy of this award.

I would certainly like to thank Dejan Sarka for introducing me to the depths of this ocean called SQL.

And of course I thank you, the readers of this blog and the members of the communities, for keeping me up late at night – learning. ;)

Thank you!