Monday, March 10, 2008

SQL Server: XQuery/XPath, Retrieval Functions

Even after all this time (since the release of SQL Server 2005 which introduced the XML data type) I still notice users having trouble with XQuery/XPath functions when processing (or rather trying to process) XML data in SQL Server, that's why I've decided to dedicate a few more posts to the subject. The ulterior motive here is me learning and testing what (I think) I know. ;)

I've introduced the SQL Server 2005 implementation of XQuery/XPath functions in an earlier post.

In this post we'll be looking at "pure" retrieval functions – in contrast to predicate-only functions. The difference between the former and the latter comes from the limitations emerging from the way the XQuery/XPath standard(s) have been implemented in SQL Server 2005. As most users have already found out (the hard way) the use of some functions is limited to XPath predicates. This time we'll be focusing on those that do not suffer from this limitation. You can read more about the supported XQuery/XPath functions in Books Online. Also note that all retrieval functions can also be used inside XPath predicates.

Have you ever encountered the following exception when using XQuery/XPath functions in SQL Server 2005?

XQuery [query()]: The XQuery syntax '/function()' is not supported.

Take a good look at the examples below – you'll notice that no XPath expression uses the XPath/function() syntax. Although this is valid syntax for XML queries according to the W3C XQuery/XPath Recommendations it is not currently allowed in SQL Server unless used inside XPath predicates (e.g. this is allowed: XPath[function() = 'value']). But as mentioned before – we'll discuss predicates later.


Are we ready?

Almost. We need an XML sample (right-click + Save target) (built from the pubs sample database), and – since I assume most of us have left kindergarten a long time ago – an XML Schema that will provide us with what every data type needs: domain integrity.

Remember: untyped XML is synonymous with unnecessary problems.

Oh, and make sure the XML Schema Collection has been created in your favorite testing database *before* attempting to do the rest of this exercise.


I. Numeric functions

number(), ceiling(), floor(), round()

These four functions provide the basic functionality related to numerical data. If an XPath expression is passed as the sole argument to one of these functions then this XPath expression must evaluate to a singleton.

select Books.BooksCollection.query(
  'Price'
  ).value(
   'number(.)'
   ,'money'
   ) as Price
 ,Books.BooksCollection.query(
  'Price'
  ).value(
   'ceiling(.)'
   ,'money'
   ) as CeilingPrice
 ,Books.BooksCollection.query(
  'Price'
  ).value(
   'floor(.)'
   ,'money'
   ) as FloorPrice
 ,Books.BooksCollection.query(
  'Price'
  ).value(
   'round(.)'
   ,'money'
   ) as RoundPrice
 from @Books.nodes(
    '/Books/Book'
    ) Books (BooksCollection)

A word of caution: absent and/or nilled nodes return an empty sequence that cannot be implicitly converted to numerical data types – hence the use of the number() function for the Price column. Try removing the function and observe the consequences.

The number() function converts the data returned by the XPath expression to a value of type xs:double (equivalent to float in SQL Server), and in contrast with the data() function (discussed further down) properly returns null to the caller when the XPath expression evaluates to an empty sequence.


II. Aggregate functions

Contrary to the W3C XPath Recommendation aggregate XPath functions implemented in SQL Server 2005 do not accept multiple arguments. Only a constant or an XPath expression can thus be passed as the sole argument to any of the aggregate functions, and the XPath expression must evaluate to a singleton or a node set.

sum(), avg(), count()

select Books.BooksCollection.query(
  'sum(Book/Price)'
  ).value(
   '.'
   ,'money'
   ) as PriceTotal
 ,Books.BooksCollection.query(
  'avg(Book/Price)'
  ).value(
   '.'
   ,'money'
   ) as AveragePrice
 ,Books.BooksCollection.query(
  'count(Book/Price)'
  ).value(
   '.'
   ,'int'
   ) as BookCount
 from @Books.nodes(
   '/Books'
   ) Books (BooksCollection)

Consider the XPath expression used for the BookCount column. Does it return the actual number of books in the XML sample?


max()

select Books.BooksCollection.query(
  'max(Book/Royalty)'
  ).value(
   '.'
   ,'int'
   ) as LargestRoyalty
 from @Books.nodes('/Books') Books (BooksCollection)

min()

select Books.BooksCollection.query(
  'min(Book/YtdSales)'
  ).value(
   '.'
   ,'int'
   ) as SmallestYtdSales
 from @Books.nodes(
   '/Books'
   ) Books (BooksCollection)

III. String functions

What can I say about these five functions? Their names pretty much speak for themselves, don't they?

string()

select Books.BooksCollection.query(
  'string(Price)'
  ).value(
   '.'
   ,'nvarchar(20)'
   ) as LocalName
 from @Books.nodes(
   '/Books/Book'
   ) Books (BooksCollection)

Be careful when using the string() function: when the XPath expression passed as the argument evaluates to a node set, the data from *all* elements of the node set is concatenated into a single string.


string-length()

select Books.BooksCollection.query(
  'Title'
  ).value(
   '.'
   ,'nvarchar(80)'
   ) as Title
 ,Books.BooksCollection.query(
  'string-length(Title)'
  ).value(
   '.'
   ,'int'
   ) as TitleLength
 from @Books.nodes(
   '/Books/Book'
   ) Books (BooksCollection)

substring()

select Books.BooksCollection.query(
  'substring(Title, 4, 12)'
  ).value(
   '.'
   ,'nvarchar(12)'
   ) as TitleSubstring
 from @Books.nodes(
   '/Books/Book'
   ) Books (BooksCollection)

concat()

The concat() function is used to concatenate values into a single string. Note that at least two arguments are mandatory, otherwise an exception is raised. Empty sequences (e.g. returned by XPath expressions when no node is matched) are treated as zero-length strings.

select Books.BooksCollection.query(
  'concat(Title, " (", @TitleId, ")")'
  ).value(
   '.'
   ,'nvarchar(max)'
   ) as TitleAndTitleId
 from @Books.nodes(
   '/Books/Book'
   ) Books (BooksCollection)

contains()

I would expect the contains() function to be restricted to predicate use, but apparently it can also be used to retrieve data. Of course, it either returns true if the string passed as the second argument is found inside the string passed as the first argument, or false if it isn't.

select Books.BooksCollection.query(
  'Title'
  ).value(
   '.'
   ,'nvarchar(80)'
   ) as Title
 ,Books.BooksCollection.query(
  'contains(Title, "Computer")'
  ).value(
   '.'
   ,'nvarchar(max)'
   ) as Contains_Computer_InTitle
 from @Books.nodes(
   '/Books/Book'
   ) Books (BooksCollection)

Warning: if the string passed as the second argument is longer than 4000 characters, contains() returns an empty sequence. This is yet another limitation of the SQL Server 2005 XQuery/XPath implementation.


Update (2009-03-07)

SQL Server 2008 introduces two additional XPath functions that are not supported by SQL Server 2005.

lower-case() and upper-case()

These two functions can make it just a little easier to circumvent live with the fact that XML is case-sensitive.

select Books.Book.query
  (
  'lower-case(Title)'
  ).value
   (
   '.'
   ,'nvarchar(80)'
   ) as LowerCaseTitle
 ,Books.Book.query
  (
  'upper-case(Title)'
  ).value
   (
   '.'
   ,'nvarchar(80)'
   ) as UpperCaseTitle
 from @books.nodes
   ('
   /Books/Book
   ') Books (Book)

IV. Miscellaneous functions

data()

We've mentioned the data() function before. It returns the data from the node(s) specified in the XPath expression in the appropriate type (if the node is typed). For details see the Remarks section in this Books Online article.

select Books.BooksCollection.query(
  'data(@TitleId)'
  ).value(
   '.'
   ,'varchar(6)'
   ) as TitleId
 ,Books.BooksCollection.query(
  'data(@PubDate)'
  ).value(
   '.'
   ,'datetime'
   ) as PubDate
 from @Books.nodes(
   '/Books/Book'
   ) Books (BooksCollection)

I've also mentioned the data() function when discussing the retrieval of XML attributes.


empty()

One might think this function would also be restricted to predicates (testing whether a node is empty or not), but how would we then make a node empty when modifying XML instances?

select Books.BooksCollection.query(
  'empty(Price)'
  ).value(
   '.'
   ,'nvarchar(max)'
   ) as EmptyPrice
 from @Books.nodes(
   '/Books/Book'
   ) Books (BooksCollection)

Books Online also suggests using the empty() function together with not() to compensate for the lack of the exists XPath function. I'll give an example when we discuss predicates.


true(), false()

Once again, not only are we allowed to test the state of a bolean type node in XML instances, but we can also modify its value using these two functions.

Both are mentioned here only as a reference.


not()

Essential to logical operations, wouldn't you agree?

select Books.BooksCollection.query(
  'not(Price)'
  ).value(
   '.'
   ,'nvarchar(max)'
   ) as NotPrice
 from @Books.nodes(
   '/Books/Book'
   ) Books (BooksCollection)

The query above returns true for books with no Price element.


local-name()

Now this is a function that causes many users migraines. But there's nothing actually wrong with it – provided the syntax limitations (mentioned earlier) are taken under consideration.

select Books.BooksCollection.query(
  'local-name(.)'
  ).value(
   '.'
   ,'nvarchar(max)'
   ) as LocalName
 from @Books.nodes(
   '/Books/Book[1]/*'
   ) Books (BooksCollection)

Look closely at the XQuery expression passed to the nodes function and the ones passed to the query and value functions. This is how the node name(s) can be retrieved in SQL Server 2005.


distinct-values()

This can be a very useful function, as demonstrated in another post.

select Books.BooksCollection.query(
  'distinct-values(Book/PubId)'
  ).value(
   '.'
   ,'nvarchar(max)'
   ) as PubId
 from @Books.nodes(
   '/Books'
   ) Books (BooksCollection)

Well, there are five more XQuery/XPath retrieval functions implemented in SQL Server 2005, but they deserve special attention, and we'll discuss them later. For instance, the predicate-only functions.


Mental aggregation

So, here they are, (most of) the XQuery/XPath functions implemented in SQL Server 2005 with examples of use.

Ergo:

  • Not all functions have been implemented;
  • Some functions are restricted to predicate use;
  • The XPath/function() syntax is not currently supported in SQL Server 2005;
  • Additional limitations may apply – Books Online is your friend.

ML


p.s. Regarding the BookCount column in the query demonstrating aggregate functions: count(Book/Price) only counts Book elements where the Price element is present, and does not return the number of *all* Book elements in the XML sample.

Posted by at No comments:

Sunday, February 24, 2008

SQL Server XQuery, Accessing Attributes

While browsing this blog's usage logs recently, I've noticed quite a few referrals from various search engines of users trying to troubleshoot problems they may have encountered when accessing XML attributes in a SQL Server XQuery.

Although the problem has been discussed in the past, and has even been documented in Books Online, some users out there still aren't familiar with a SQL Server 2005 limitation regarding the querying of XML attributes – and the error message itself isn't of much help either. That's why I've decided to point this problem out in my blog as well to help improve the visibility of this issue.

For example, when querying this XML:

declare @books xml

set @books = N'<xml version="1.0">
<Books xsi:noNamespaceSchemaLocation="PubsTitles.xsd">
 <Book TitleId="BU1032"PubDate="1991-06-12T00:00:00.000Z">
  <Title>The Busy Executive''s Database Guide</Title>
  <Type>business</Type>
  <PubId>1389</PubId>
  <Price>19.9900</Price>
  <Royalty>10</Royalty>
  <YtdSales>4095</YtdSales>
  <Notes>An overview of available database systems with emphasis 
on common business applications. Illustrated.</Notes>
 </Book>
 <Book TitleId="BU1111"PubDate="1991-06-09T00:00:00.000Z">
  <Title>Cooking with Computers: Surreptitious Balance Sheets</Title>
  <Type>business</Type>
  <PubId>1389</PubId>
  <Price>11.9500</Price>
  <Royalty>10</Royalty>
  <YtdSales>3876</YtdSales>
  <Notes>Helpful hints on how to use your electronic resources to 
the best advantage.</Notes>
 </Book>
</Books>'

...one would instinctively formulate the query like this:

select Books.Book.query('Title').value('.', 'varchar(max)') as Title
 ,Books.Book.query('@TitleId').value('.', 'varchar(6)') as TitleId
 ,Books.Book.query('@PubDate').value('.', 'datetime') as TitleId
 from @Books.nodes('/Books/Book') Books (Book)

However, referencing XML attributes this way is not allowed in SQL Server 2005, and this is the error message we would have encountered if we were to follow our instincts:

Msg 2396, Level 16, State 1, Line 26
XQuery [query()]: Attribute may not appear outside of an element

Not quite as eloquent as one would expect, wouldn't you agree?


fn:data() to the rescue!

By using the data() XQuery function like so:

select Books.Book.query('Title').value('.', 'varchar(80)') as Title
 ,Books.Book.query('data(@TitleId)').value('.', 'varchar(6)') as TitleId
 ,Books.Book.query('data(@PubDate)').value('.', 'datetime') as TitleId
 from @Books.nodes('/Books/Book') Books (Book)

...the query succeeds as expected:

Title                                                TitleId TitleId
---------------------------------------------------- ------- -----------------------
The Busy Executive's Database Guide                  BU1032  1991-06-12 00:00:00.000
Cooking with Computers: Surreptitious Balance Sheets BU1111  1991-06-09 00:00:00.000

This is true both for typed as well as untyped XML instances, and the function is, of course, documented in Books Online.


ML


p.s. I've decided on posting this example while preparing a more detailed overview of XPath/XQuery functions in SQL Server, which should follow soon.

p.p.s. The overview is divided into two parts:

Posted by at 7 comments:

Friday, January 11, 2008

What's wrong with COUNT(*)?

Counting – what's it all about? I'm sure you've heard about a trick that one should use when aggregating data using the COUNT system function in Microsoft SQL Server; specifically, how the performance of the function can be improved using a neat trick. But can we go beyond neat...?

The tests have been done on a SQL Server 2005 (SP2) instance; you're all kindly invited to also run them on an instance/version of your own choice.


Send in the rows

Let's start with some test data. I've provided a script that will do that for you, provided you have a testing database – which I'm sure you do (yes, tempdb is just fine).

The script creates a five column table and copies data into it from the AdventureWorks database, which I assume is available on the same server instance. If any of the above is not true in your particular case, you can still adapt the script as needed.

We end up with a brand new table containing 19,972 rows of data residing in 5,197 data pages.


The plan

We'll be counting rows (what a shock):

  • Query #1 – using no restrictions (i.e. counting all rows); 
    select count(*)
 from dbo.Contact
    and
  • Query #2 – using a restriction (i.e. counting rows corresponding to specific criteria). 
    select count(*)
 from dbo.Contact
 where (dbo.Contact.FirstName = N'Karl')

Get on with it!

If we execute the two queries above on our heap (remember, this new table has no keys and no indexes), we get these results:

Query Performance
(on a heap)		 Result
(row count)
CPU Reads
#1 76 5203 19972
#2 62 5203 22

Also take a look at the execution plans (Query #1 execution plan, Query #2 execution plan). Nothing too impressive, but understandable: without any indexes, the optimizer needs to access all the pages in order to get the actual row count. With Query #1 the optimizer has no options but to do a full table scan, and the restriction in Query #2 can't really improve the performance as it requires data to be tested in order to restrict the result set.

Now, let's add the primary key. We'll use the ContactID column and also make the primary key clustered (which is the default anyway, but I prefer explicit declarations):

alter table dbo.Contact
 add constraint pk_Contact
   primary key clustered
   (
   ContactID
   )
Query Performance
(clustered primary key)		 Result
(row count)
CPU Reads
#1 34 5208 19972
#2 68 5208 22

Ha! The number of reads has actually increased. Note that the clustered primary key actually takes up as many pages as the data itself. On the other hand, for Query #1 the stress on the CPU has decreased, while for Query #2 it seems that the clustered primary key can't really do much – data must be read and tested, and the referenced column is not part of the primary key.

Do you think making it part of the clustered primary key might make a difference? Do you think it would make a favorable difference? I've decided on not including the latter in this test as it affects the data model, and modifying the data model (especially, changing a primary key) just to improve performance is IMO far from being good practice. If you're curious enough you can do it yourself.1

Anyway, as expected (and quite imminent as well), the execution plans are different now (Query #1 execution plan, Query #2 execution plan) compared to before.

Let's see what happens if we add an index. Let's index the column used in the restriction of Query #2 – the FirstName column:

create nonclustered index x_Contact_FirstName
 on dbo.Contact
  (
  FirstName
  )
Query Performance
(cl. PK + noncl. FirstName)		 Result
(row count)
CPU Reads
#1 4 59 19972
#2 0 2 22

Well, well, who'd have thought...? The optimizer once again stands up to its name. Let's examine the execution plans (Query #1 execution plan, Query #2 execution plan). No longer is the use of the clustered primary key the optimal choice – the nonclustered index requires far less reading. After all, it resides on only 57 pages, and the optimizer takes full advantage of this fact – on both occasions.


Some Romans fear Asterix...

..others fear the asterisk like the plague. However, if we consider the fact that

select count(*)

simply is not in any way functionally equivalent to

select *

then this fear loses most of its substance. I will not go into any details this time of when and why not declaring columns in the SELECT clause is poor practice.

For comparison let's execute these two queries:

  • Query #4 – retrieving all rows (and all columns); 
    select *
 from dbo.Contact
    and
  • Query #5 – retrieving a subset of rows. 
    select *
 from dbo.Contact
 where (dbo.Contact.FirstName = N'Karl')

The execution plans:

If we now compare the execution plans of these last two queries with the execution plans above, we can observe one significant difference. When viewing the execution plan in SSMS park the mouse over the arrows leading from the very first operator (Table Scan or Clustered/Nonclustered Index Scan/Seek). What can we observe?

Simply counting rows does not require any data to be read from the data pages (except when values need to be tested in order to apply query restrictions).


What about that "neat trick"?

A proposed method of speeding up row counts that has been suggested by some members of the community from time to time, is to replace the asterisk as the argument of the COUNT function with a constant. This – believe it or not – is supposed to lower the I/O for the query. Frankly, I've found that suggestion somewhat amusing if not illogical, but was curious enough to put it to a test.

The two queries above using the "trick" look like this:

  • Query #6 – using no restrictions (i.e. counting all rows); 
    select count(1)
 from dbo.Contact
    and
  • Query #7 – using a restriction (i.e. counting rows corresponding to specific criteria). 
    select count(1)
 from dbo.Contact
 where (dbo.Contact.FirstName = N'Karl')
Query Performance
(on a heap)		 Result
(row count)
CPU Reads
#6 68 5203 19972
#7 76 5203 22

An equal number of reads, obviously, and a slight difference between CPU times compared to previous results for the heap.

Query Performance
(clustered primary key)		 Result
(row count)
CPU Reads
#6 64 5208 19972
#7 84 5208 22

Again, an equal number of reads, but this time it seems more processing is required when using the clustered primary key. Is the optimizer translating the constant?

Query Performance
(cl. PK + noncl. FirstName)		 Result
(row count)
CPU Reads
#6 2 59 19972
#7 0 2 22

Once more, an equal number of reads, but now it seems less processing is required when the nonclustered index is used.

The results are pretty inconclusive if you ask me, and the execution plans are the same as before:

One thing is certain, though, select count(1) does not perform any better than select count(*). And I've tried using other constants as well – no change. Is that another myth busted? Can anyone confirm this or prove otherwise?

As for me, I'm staying with Asterix. ;) And next time we'll look at another important fact regarding aggregate functions.


ML


*1 FYI: Destroying the primary key in this particular case results in an increased number of reads (5218).

Posted by at 2 comments:
Subscribe to: Posts (Atom)