XQuery methods and determinism

If I store XML in an XML data type column its sometimes useful for performance to be able to hoist the value of a particular item to its own persisted computed column. The column can then be used in SQL queries without having to recalculate the XQuery each time its accessed. For example, if I want to use an attribute at the root named orderid:

CREATE TABLE dbo.xml_order (
  id INT PRIMARY KEY IDENTITY, — to enable XML Index creation
  order_doc XML,
  orderid — this should be obtained from XML document
)

I must first define a SQL Server UDF to calculate the value.

CREATE FUNCTION dbo.getorderid (@data XML)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
  RETURN @data.value('/*[1]/@orderid', 'INT')
END

and then use it:

CREATE TABLE xml_order (
  id INT PRIMARY KEY IDENTITY, — to enable XML Index creation
  order_doc XML,
  orderid AS dbo.getorderid(order_doc) PERSISTED
)

The "WITH SCHEMABINDING" is needed if I want to the column a persisted computed column. The persisted computed column requires the function to be deterministic (returns same output give same input).

This got me thinking…which types of XQuery statements are deterministic and what determines determinism in an XQuery statement? Turns out that ALL XQuery statements are deterministic, because the current SQL Server XQuery dialect doesn't contain non-deterministic XQuery functions like current-dateTime() and friends. Note that this could change as SQL Server XQuery adds (I hope) more of the standard XQuery functions and operators in future. But for now, all is safe and determnistic.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.