Saw another Kent post on TechEd 2005. I'll be there too (mixed marketing slogan message with SQLPass). I'm doing a talk on “SQLCLR vs. T-SQL: Best Practices for Development in the Database”. Some folks on newgroups lately think that Microsoft may have mis-positioned this feature a bit. I disagree with this assessment. Come to the talk and see why.

Catching up on blog-reading. Kent Tegels wondered if I was speaking at SQLPass Community Summit. Yep, I am.

I'm doing a 2-day pre-con on (what else) What's new in SQL Server 2005 for developers. Also a tutorial session on XQuery for the “main conference”. I got “rained out” (to put it mildly) at last year's conference, maybe I'll have better luck this year.

See you there.

Speaking of SQL Profiler brought this to mind. The number one feature that brings a smile to every DBA's face:

GRANT ALTER TRACE TO [somedev]

No longer do you have to listen to developers ask “make me SA so I can run the trace”. Actually, brings a smile to devs too, no longer do they have to beg for it. Just brings the gate over to a more granular permission level. You still do have to ask for ALTER TRACE now...

I've always liked the graphic showplan in SQL Server query analyzer. The biggest hassle with it came when you wanted to send the plan to a friend. Or maybe MS support, but support is your friend too... right? You could send screenshots (which had the annoying habit of never displaying those hover-over stats) or go back to textual showplan.

SQL Server 2005 has XML showplan and I'd once gone as far as to attempt to write a transform to display things nicely. No need. You can do the following from SQL Server Management Studio.

1. Turn on the XML showplan
   -- show estimated plan
   SET SHOWPLAN_XML ON
   GO

   -- or execute statement and show real plan
   SET STATISTICS XML ON
   GO

2. This puts out your showplan as an XML data type column. Click the hyperlink to display the file.

3. Save the XML showplan file with the magic suffix .SQLPlan

4. Now when you double-click on the .SQLPlan file, it opens in SSMS as the interactive showplan with the hover-over stats.

Cool, eh? You can do a variation of this with SQL Profiler too. In fact its easier with SQL Profiler. Now you can mail the .SQLPlan to a friend. With full fidelity.

Since .NET 2.0 beta2 was released I've received a few inquiries about what happened to tracing SNAC (that's SQL Native Client). Looking at the adonetdiag.mof file, the SQLNCLI.1 entry (that's SNAC) was removed. I also got a solution/workaround from Glenn Johnson, who asked the question, then provided the answer faster than I could fly from Portland to San Jose and figure it out myself.

The workaround for this is to:
1. Start with adonetdiag.mof that was posted in my article. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/tracingdataaccess.asp
2. Edit this .mof file to replace to entries for System.Data.SNI.B1 with System.Data.SNI.1 from the new .mof file provided with beta2 . This file is in the Framework directory where .NET is installed.
3. Also change the file "ctrl.guid.adonet.beta1" from the article downloads to put in the new GUID for System.Data.SNI.1 (replace System.Data.SNI.Beta1). Run this scripts from the article as before.

What appears to have happened is that:
a. SQLNCLI.1 entry was removed from adonetdiag.mof (SNAC is not part of .NET, so this makes sense)
b. The GUID for SNI tracing has changed.

Happy tracing. Thanks Glenn.

When I did my first demonstration with the combined SqlClient and SqlServer provider in the April CTP version of SQL Server, I was a bit surprised. I wrote a simple stored procedure to run in the server, exactly the way I've always written it to run on the client (modulo sending results back to the client):

// error handling elided
SqlConnection conn = new SqlConnection("context connection=true");
SqlCommand cmd = new SqlCommand("select * from authors", conn);
conn.Open();
SqlContext.Pipe.ExecuteAndSend(cmd);
cmd.Dispose();
conn.Dispose();

I was surprised because this produced the error:

System.Security.HostProtectionException: Attempted to perform an operation that was forbidden by the CLR host

The protected resources (only available with full trust) were: All
The demanded resources were:SharedState

After a little experimentation, I discovered they what was causing my problems was using Dispose(). Interestingly, I didn't technically need to use Dispose() (all .NET instances are available for garbage collection when the procedure invocation ends) and, in addition, using the C#/VB.NET "Using" contruct worked fine.

using (SqlConnection conn = new SqlConnection("context connection=true"))
using (SqlCommand cmd = new SqlCommand("select * from authors", conn))
{
  conn.Open();
  SqlContext.Pipe.ExecuteAndSend(cmd);
}

After consulting the Reflector, the two methods are different because the "using" feature calls IDisposable::Dispose on the SqlCommand/SqlConnection itself (after casting). The direct Dispose() call generates a call to ComponentModel.Dispose. Both SqlConnection and SqlCommand inherit (eventually) from System.ComponentModel.Component. That's where the shared state (and the exception) comes in.

Watch out for this. Using "using" (that's Using-End Using in VB.NET) is your best bet.

The last blog entry brings up the question of what I mean when I say something is "unsupported" in SQLCLR. Becuase I've said that J# is "unsupported". This doesn't mean that it won't ever work or that you couldn't actually get technical support for it, given enough time, energy, and money.

Technically, its possible to run almost *anything* in SQL Server if you catalog it as UNSAFE. But if your library doesn't follow SQL Server's rules for reliability and does something that could compromise the stability of the server, its appdomain could be unloaded as a last resort. Oh. Only a subset of the BCL are supported; to see this subset, create a Visual Studio Database/SQL Server project, and choose "Add Reference". Note that only a subset of the base class libraries appear. These are the ones that have been hardened according to the SQL Server reliability guidelines. Note that this contains the support libraries for VisualBasic.NET and Managed C++ (C# uses no language-specific support libraries), but not for J#. Because of the COM interop, they'd have to almost completely rewrite it to be compliant. That's what I mean by unsupported, I don't mean that it technically isn't accomplishable. Note also that there's no J# Database/SQL Server project in Visual Studio. That's a clue. And although *managed* C++ is a supported language, you have to compile with a special /safe switch, which enforces reliability limitations.

On the other hand, it's always been my contention that the most unsafe CLR code is safer than an extended stored procedure. Extended stored procedures are analogous to tweaking with the kernel of an operating system; there has to be extended test/maintanance plan because, unless you're the SQL Server team, you don't "own" the code you're running under. There's now notes in the BOL, under Programming Extended Stored Procedures, that read: "This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work" and "Use CLR Integration instead.".

Looking at the Feb CTPNotes file again, there's another change that caught my eye. DROP ASSEMBLY has changed with respect to dependent assemblies. In past, you had to drop assemblies one at a time, so if assembly A called assembly B, you first dropped A then B. Now dropping A drops B automatically if B has the "is_visible" flag is false for B.

Although the looks like a convenience change at first, it actually solves a problem people run into when they go outside the mainstream of the supported BCL assemblies. If, for example, you used a library of your own that had a reference to System.Drawing (it's a library that draws an icon, but you don't intend to use this portion in SQL Server), this triggered a set of BCL references, some of them circular references. The only way to catalog something like this to SQL Server entailed cataloging as UNSAFE and (because of dependencies) also cataloging many unsupported base class libraries. But you couldn't drop it all because of the circular references.

Another example of this is using the J# language. Because J# support libraries use COM interop, the most trivial J# program (I just added two numbers together) must be cataloged as UNSAFE and results in 4 J# support libraries and 6 or so unsupported base class libraries being cataloged as dependencies. It's now possible to drop the J# assembly and have it drop all the associated assemblies at the same time.

The data access team (known as DataWorks) has started up a team blog. Some of the individual team members, like Angel Saenz-Badillos and Sushil Chordia, have been blogging for a while, this one has posts from all members.

One of the first posts was information about the upcoming SqlClient and SqlServer provider unification by Pablo Castro. The unified provider isn't in Feb CTP, but will be in an upcoming release soon. Pablo mentions that SqlConnection and other classes that can be shared between providers will be, but that SqlContext will still be retained for in-database- specific classes. By my calculations, that leaves (as in-database-specific):

SqlPipe - encapsulates a data stream back to the client
SqlTriggerContext - provides information in a SQLCLR trigger
WindowsIdentity - used for impersonation when accessing external resources (e.g. files) where you need a Windows identity

The only thing that I'll miss is the SqlDefinition/SqlExecutionContext classes. I'd been told those won't be in for this time; hopefully they're in the next major release.

They'll also be an upcoming in-depth article when the unified provider ships.

Thanks, Pablo! And Alyssa!

In this last entry on Service Broker enhancements I inadvertantly referred to the new poison message handling as poison conversation handling. Well, maybe it wasn't so inadvertant. So what's the difference between Service Broker's poison message handling and traditional poison message handling?

A poison message is a fact of life in transactional messaging. When a message is received from a queue, often some database action occurs as part of the same transaction. If the database action fails (say, insert of a row based on a field in the message that happens to be a duplicate key) the message is put back on the queue. Where it is received again... If the database condition that caused the first rollback to happen hasn't been resolved, the transaction will roll back again..and again..hence the term posion message.

Usually poison message handling shunts the message off to a dead letter queue. Where it can be safely ignored while the application goes on. Oh. The problem with this is: suppose the message you are ignoring is a million-dollar order. Or the executive's December check. The database transaction may have rolled back because overflow occurred on an internal variable (especially with extremely large dollar figures). I've personally seen the “executive December check overflows payroll counters“ one, back in the days of COBOL. They used fixed point decimal just like SQL/RDBMSs do today.

Since the primitive concept of Service Broker is the conversation, not the message, the message should not be ignored.
You could lose the million dollar order. Or produce cranky executives. The programmer who designed such an app (and didn't watch the dead letter queue) could be fired. There's something wrong with the conversation, it should be shut down.

The new "posion message handling" actually goes further than that. After 5 receives of the same message, Service Broker shuts down *the queues on both sides of the conversation*. You can recover from this by:
1. Either end the conversation or recieve the message without a rollback
2. And reenable the queues

You can still implement your own poison message handling, using any of the suggestions we described in our "First Look" book. You have 4 retries to do something on your own, before the automatic poison behavior kicks in.

I've been doing some experimenting with the new SQL Server Service Broker features in Feb CTP. You can read about them in the CTPNotes.doc file; I won't repeat the information here. The features are:

1. Improved Endpoint Security - authentication option NONE is not longer supported
2. DEFAULT message type and contract
3. Poison conversation handling

DEFAULT message type and contract came about due to feedback that the DDL to create a simple broker applications consisted of too many pieces. You needed to define MESSAGE TYPEs, CONTRACT, QUEUE, and SERVICE to define the simplest application. The first time this behavior change was described to me (it was some of my students among those who complained about the complexity after all), I thought they were going to loosen things up a bit to work without a contract. But broker uses contracts to enforce conversation integrity. In order to receive a message, a service has to be defined with a contract that's enforced when messages are being put on the queue. No contract, no user messages can be received. Hmmm...how would they do it?

You can now define a broker SERVICE by only defining QUEUE and SERVICE objects. However, the SERVICE must be defined to use a new built-in contract named [DEFAULT]. This contract specifies that a built-in MESSAGE TYPE, also called [DEFAULT], can be sent by either side (by ANY). When you issue a BEGIN CONVERSATION DIALOG without a contract, it uses the [DEFAULT] contract, not NO contract. When you SEND a message without an explicit MESSAGE TYPE it sends the [DEFAULT] message type.

So you're NOT using contract-less and message type-less conversations, you're using a specific contract and message type called [DEFAULT]. You just don't have to define them yourself.

There's a code example is the Feb CTPNotes.doc file (which is why you should always “read the readme file”), try it out for yourself and see.

When reading the CTPNotes file from the new Feb CTP build I stumbled across the fact that the XML schema that contains SQL data types (http://schemas.microsoft.com/sqlserver/2004/sqltypes) is now built-in to the server. Although this may not mean much to most people, it gave me the chance to try something that Dan Sullivan thought up for the first rev of our SQL Server 2005 class. It works now.

One of the enhancements to SELECT...FOR XML is ability to request that the XML it produces be prepended by an XML schema that describes it. A recent change allows you to choose the namespace for that schema. Dan's idea was to add the prepended schema to create a schema collection. After storing the FOR XML outside in an XML schema-valid column, you could make updates to the column that would be validated by the schema. You'd set this up like this:

declare @x xml
select @x = (select * from authors for xml auto, type, xmlschema('urn:authors')).query('*[1]')
create xml schema collection authorsxsd
as @x
go

create table authorsxml (
 id int primary key identity, -- primary key required if XML index needed
 authors xml(authorsxsd))
go

declare @x xml(authorsxsd)
set @x = (select * from authors for xml auto, type, xmlschema('urn:authors')).query('/*[position()>1]')
insert authorsxml values(@x)

The XML Schema produced in the first step will now validate any information entered or updated in the table.

Why the Feb CTP change makes this work is FOR XML....XMLSCHEMA uses the SQL data types schema that's now built in. In previous betas, you could use this schema (error: not built in) or add the schema manually (error: it is built in [but it wasn't]). Thanks SQL Server 2005 XML folks, for this.

In the new Feb CTP release, how your implement a table-valued function in SQLCLR has been re-architected. This is in the readme (CTPNotes) This was done because implementing ISqlReader was quite complicated and overkill for most scenarios. Chapter 3 of our book "A First Look at SQL Server 2005 for developers" contains a very simple TVF (Bernoulli) implemented using ISqlReader. It contains over 400 lines of code. Many of the methods are stubbed-out because they are never used, but must exist to satify the interface definition. Using the new implementation this method would be less than 15 lines of code.

The new TVF implementation requires three pieces:
1. The SqlFunction attribute with the new field FillRowMethodName.
2. This attribute is applied to a method that returns either IEnumerable or IEnumerator.
3. FillRowMethodName points to a DIFFERENT method (in the same class) that has a special signature.

The methods in steps #2 and #3 have to be public static. Many of the collection classes in the BCL (e.g System.Array) implement IEnumerable or IEnumeration already, or you can write your own implementation.

The FillRowMethodName method has the following signature:

public static void FillIt(Object o, out int col1 , out int col2...)
   where the first arg is object returned by method in step #2
   where the varargs arguments (col1, col2....) are the columns that will be returned.

MoveNext is called on the underlying IEnumerator (in each case) until it returns false. Each time MoveNext returns a value, the FillRowMethod is called. This generates the rows. The number of columns is determined by the exact signature of the FillRowMethod. In this example, a 2-column table is returned.

Interestingly, the 2-nth arguments in your FillRowMethohd must be declared as "out" variables in C#. In my cursory testing, if they are declared as "ref", the method failed with the error: "argument n cannot be NULL" when the TVF implementation calls your FillRowMethod. This is interesting for VB.NET programmers because there is no direct variable qualifier keyword that corresponds to C#'s out. Or is there?

When .NET was first released a friend of mine, Jose Mojica, published "The C# & VB.NET Conversion Pocket Reference". And it names the following VB.NET equivalent for "out":

Imports System.Runtime.InteropServices
' signature of a FillRowMethod
Shared Sub FillIt(o as Object, <Out()> ByRef col1 as Integer, <Out()> ByRef col2 as Integer...)

Works great, Jose, my VB.NET TVF is working fine. If you're doing cross language work in .NET, I highly recommend this book.

Hi all. Not much blogging out of me lately. I've been on vacation and, in between, I've been teaching SQL Server 2005 at Microsoft Sydney to some of Australia and New Zealand's finest, including Russell Darroch, Greg Low, Chris Hewitt and Brent Challis. During the class Greg worked up his all-encompasing trigger to prevent cataloging objects in the master database. Probably partially inspired by seeing my do this a few times by mistake in demos. Some other folks in the class worked on *their* application specific features inspired by the encryption built-ins (e.g. encryptbykey), XQuery functionality, FOR XML PATH, and Service Broker. Thanks folks, I had a great time.

More people answer the question "what's the feature you most want to hear about?" asking about Service Broker each class. It's amazing to see Broker's "recognition curve" increase steadily since I started teaching SQL Server 2005 in...uh...August 2003. And to watch momentum building for this release in general.

By now, I'm sure you've heard that there's a new CTP (Commnunity Technology Preview) released this week. I've got the CTPNotes file. Be SURE to read this one carefully. There's a lot of new stuff in this build. I'll be home mid-week to start on it in earnest.

Right now I'm just back from watching the sun go down at Manly Beach. Morning was spent navigating the waves and getting myself sunburnt, then it started sprinkling rain around 3. Cleared up right after dinner. It's going to be hard to leave summer.. oh that's right.. its autumn here already.

More technical content shortly. Got some blog comment responses to catch up on too...later.

My cohort, Dan Sullivan, has released the Service Broker Explorer on his Service Broker Developer's Spot website. It a graphic user interface for Service Broker that has some “topology map” features and configuration features and some management features for Service Broker objects. According to Dan:

“It lets you drill into Sevice Broker and add and control elements of Service Broker with a GUI. It's just meant for use to learn about Service Broker, it is not for use in a production system.“

Version 1 of what promises to be a very cool utility.

Just catching up on my blogging before a little vacation next week.

Browsing through the SQL Server BOL from the December CTP, I came across some information on something called "plan guides". There is info on some stored procedures that create and manage plan guides, a database option (in ALTER DATABASE) and a system view that lists plan guides. However, none of this these are active yet in the actual product. Let's hope this is another example of documentation being ahead of things (a la EXCEPT and INTERSECT support), because these sound interesting. According to BOL...

A plan guide is a database object that associates query hints with certain queries in the database. You can create a plan guide (using sp_createplanguide) for a SQL statement or batch. The statement can be standalone or specified to be part of a certain stored procedure. The plan guide specifies an OPTION clause specifying query hints to be applied whenever the statement is executed.

Plan guides must first be "enabled" on in a database (using ALTER DATABASE) before they can be used. Then you turn them "on and off" by sp_controlplanguide enable/disable. When a matching query is detected the hints are automatically “put in place“.

Sounds VERY cool for query plan afficianados. You can have configurable query hinting without touching your queries in the application code. And turn it on or off at will. Only thing is, NONE of it works in the December CTP. Any of the stored procedures produce "not found" message, as does the ALTER DATABASE keyword and the system view. Maybe the BOL IS a little ahead again.

I had a few spare cycles to do some reading recently, and thought I would check out the new Unified Dimensional Model (UDM) that can be used with Analysis Services 2005. I started by listening to a webcast by Amir and Ariel Netz. Interesting stuff about datamarts, data warehouse, and specialized metadata model proliferation. And the strengths of reporting against both relational and OLAP data. Although MOLAP cubes are still with us, AS2005 seems to be becoming a reporting clearinghouse, a "UDM server".

The only thing that struck me a bit strange was the concept of using live RDBMSs to feed UDM data caches as an adjunct to or replacement for datamarts and data warehouses. I've been spending a lot of time lately talking to DBAs who are concerned that features such as SQLCLR and in-database web services might blur the "focus" of a database, and make management more complex because of resource contention/sharing. I'd think that a UDM connection to a live database (rather than a reporting only database copy) might complicate management, sharing, and contention issues even more.

Reading more about this in SQL Server BOL, there IS a section on using database mirroring and snapshots to support reporting. So maybe they're not talking about a reporting connection to a live OLTP database, something that hasn't been done (with OLTP performance in mind) for a while. Maybe it's all done with mirrors.

In the last blog entry I talked about using System.Transactions in SQLCLR code. But don't try this yet, the keyword here is *will* be used. I base this on a few bugs that I filed on System.Transactions/SQLCLR being closed as “this will be fixed in beta 3”. And a statement on a public newsgroup by Pablo Castro (who would know better than Pablo?) that you'd roll back in a SQLCLR trigger by using: Transaction.Current.Rollback().

But don't try this yet. Even in the latest CTPs, using SQLCLR and System.Transactions yields some nasty messages referring to methods in EnterpriseServices.dll and fails. If I had to guess, this support would be completed about the same time as the merge of the SqlClient and SqlServer data providers. Watch this space.

There's been a lot of interest in the new System.Transactions.dll assembly lately. Especially from users of SQL Server 2005. This is based around two functionality points.

The first point of interest is that you will use System.Transactions to use transactions in SQLCLR procedural code in SQL Server 2005. In the beta 2 implementation of the SqlServer data provider, transactional coding had to use two different code paths based on whether a transaction was already started before your procedure was called. There was a section in the "First Look at SQL Server 2005 for Developers" book on this, transaction handling seemed rather complex. Using System.Transactions will make this simpler and more elegant.

The second point is that SQL Server 2005 has a feature known as promotable transactions. When you use a single connection to SQL Server 2005 and a System.Transactions TransactionScope, a local transaction is started. If SQL Server 2000 is used, or more than one database connection is used, the same TransactionScope starts a distributed transaction. Which is a few times slower than a local transaction.

After starting a local transaction with SQL Server 2005, another connection is opened in the same TransactionScope, the original local transaction is promoted to a distributed transaction, because now a distributed transaction is needed. Hence the name promotable transactions.

It is important to remember, however, that the transaction is still scoped to the *connection*. The usual cool TransactionScope demo shows a local transaction on SQL Server 2005 instance #1 being promoted to distributed when you open a second connection to a *different* database instance. It will be also be promoted if you open a second SqlConnection to *the same instance*.  Each connection has a different transaction space (lock space), even if you are using promotable transactions. Therefore, you need a distributed transaction with two connections to the same database. Even if the connection string and other environment is exactly the same.

To "knit" two lock spaces togther you'd need something fairly drastic, a la sp_getbindtoken and sp_bindsession. And they're not doing that.

The reason why this is puzzling (I was recently reminded by a student from a recent class) is that, in MTS/COM+ you could flow transactions by composing method calls, like this:

void DoTransfer(int accta, int acctb, double amt)
{
  DoWithdrawal(accta, amt);
  DoDeposit(acctb, amt);
}

Both DoWithdrawal and DoDeposit would open a connection in MTS/COM+. System.Transactions has some COM+-like transaction composition properties. But if both DoWithdrawal and DoDeposit each open a separate SqlConnection with enlist=true in the connection string (its the default), promotable transactions won't help, they'll be running a *distributed* transaction. If you really want promotable to mean: multiple operations, one database == local transaction, you'll have to pass the SqlConnection object around too. This makes things complex, because SqlConnections aren't "agile". They don't pass from process to process, for example.

Transaction is scoped to the connection (modulo sp_bindsession).

People (especially DBAs) want to see what those pesky appdomains are doing in SQLCLR.

Back in beta1 there was a system function, master.sys.fn_appdomains(), that showed which appdomains were running and which assemblies were loaded in the appdomains, number of bytes used, etc. In beta2 this view stopped working and, although you can watch appdomains being created and destroyed in the SQL Server log, I'd always missed master.sys.fn_appdomains().

You can get this information and more in the Dec CTP build:

-- appdomains
select * from sys.dm_clr_appdomains
-- loaded assemblies
select * from sys.dm_clr_loaded_assemblies

-- You can even get managed code execution statistics for currently executing queries
select command, exec_managed_code from sys.dm_exec_requests

master.sys.fn_appdomains is still around, but it doesn't return anything any more. Look for more CLR statistics in the dynamic management views (and elsewhere) in future betas.

I'm back home again after being on the road three weeks out of the last four. Internet access was good, except for one hotel. I watched the person in front of me at checkin:

Guest: How do you access the high-speed internet you mention in your ad?
Clerk: Unplug the phone jack from the wall, replace it with your PC plug.
Guest: Then what?
Clerk: Dial your ISP.
Guest: I don't have an ISP here.
Clerk: There is a list of them on the internet.

I didn't listen any further.

During my travels, it appears that I acquired a throat infection that makes it difficult to talk. This gives me a chance to use one an analogy from Ball Four by Jim Bouton. He was speaking of baseball pitchers, but... An instructor with a throat infection is "like a tiddly-winks champion with a hangnail".

Anyhow, back home, got real high-speed internet. Technical content coming...

Here's the answers to the question from Fun With static XQuery evaluation - 2

-- start with a schema collection

CREATE XML SCHEMA COLLECTION ages AS
'<xs:schema
   xmlns:xs="http://www.w3.org/2001/XMLSchema"
   targetNamespace="urn:ages"
   xmlns:tns="urn:ages">
<xs:element name="age" type="xs:int"/>
</xs:schema>
'
GO

DECLARE @x xml(ages)
SET @x = '<age>12</age>'
-- fails ??!
SELECT @x.query('string(/age)')
GO

This fails because there can be more than one <age> element and fn:string requires a singleton or empty sequence.

--- These work ---

-- this query restricts it to the first age element
DECLARE @x xml(ages)
SET @x = '<age>12</age>'
SELECT @x.query('string(/age[1])')
GO

-- this restricts the variable to XML documents. Fragments disallowed.
-- This means there can be only ONE (or zero) age elements.
-- No subscript is needed on the query then.
DECLARE @x xml(document ages)
SET @x = '<age>12</age>'
SELECT @x.query('string(/age)')
GO

The second one was a bit harder if you haven't run across the (document schemacollection) construct. Remember that XML data type can contain documents or fragments. Putting "document " before the schema collection name in any typed XML declaration restricts instances to an XML document (ie, single root element). The default is "content" so:

declare @x xml(content ages)    -- use ages xml schema collection, allow fragments or documents
declare @x xml(document ages) -- disallow fragments; documents only
declare @x xml(ages)               -- equals using "content"

Note that you can only enforce "document only" using this keyword with TYPED XML. It's not supported on untyped XML instances. You can do the equivalent enforcement with an untyped XML column in a table by using an XML check constraint, like this:

create table foo (
  xmlcol xml constraint mycontr
         xmlcol.value('count(/*)', 'int') = 1 and xmlcol.exist('/text()')=0

Hope you've enjoy this foray into static typing and XQuery. Because this is a "implementation decision" you won't find much about this in the W3C spec. The best information about this is in the excellent XML Best Practices for Microsoft SQL Server 2005 document.

BTW, in case you collect W3C specs for your own offline reference (like I do), bear in mind that the final SQL Server 2005 implementation of XQuery will be aligned with the W3C July 2004 XQuery spec series. XQuery is still a W3C "work in progress". SQL Server 2005 implements a subset of the functions and operators, adds functions to access T-SQL variables and SQL columns, and also implements static typing. So it's not a 1-to-1 match with the spec, but if you like W3C specs, July 2004 is the one you want. For now.

After the last two entries, you might be thinking "I guess I can never use text() as a node test with typed XML again". Not so. The error message reads: 'text()' is not supported on simple typed or 'http://www.w3.org/2001/XMLSchema#anyType' elements. So what's left? Mixed content, for one thing. Mixed content consists of a mixture of text and also embedded subelements.

If we change the schema to allow mixed content (this schema also allows a particular subelement):

CREATE XML SCHEMA COLLECTION mixedage AS
'<xs:schema
xmlns:xs="http://www.w3.org/2001/XMLSchema"
targetNamespace="urn:ages"
xmlns:tns="urn:ages">
  <xs:complexType name="age" mixed="true">
    <xs:complexContent mixed="true">
      <xs:restriction base="xs:anyType">
         <xs:sequence>
           <xs:element name="dogyears" type="xs:int"/>
         </xs:sequence>
      </xs:restriction>
    </xs:complexContent>
  </xs:complexType>

<xs:element name="age" type="tns:age"/>
</xs:schema>
'

Then the text() node test works with typed XML just fine:

DECLARE @x xml(mixedage)
SET @x = '
<ag:age xmlns:ag="urn:ages">This is the age in dog years<dogyears>3</dogyears></ag:age>'
-- now it works OK
SELECT @x.query('
declare default namespace "urn:ages";
/age/text()')
GO

Reference back to the previous entry. Now that we know the rules, let's try them out:

-- snip --

Data(),text() and string() accessors

XQuery has a function fn:data() to extract scalar, typed values from nodes, a node test text() to return text nodes, and the function fn:string() that returns the string value of a node. Their usages are sometimes confusing. Guidelines for their proper use in SQL Server 2005 are as follows. Consider the XML instance <age>12</age>.

Untyped XML: The path expression /age/text() returns the text node "12". The function fn:data(/age) returns the string value "12" and so does fn:string(/age).

Typed XML: The expression /age/text() returns static error for any simple typed <age> element. On the other hand, fn:data(/age) returns integer 12, while fn:string(/age) yields the string "12".

-- snip --

Try this:

DECLARE @x xml
SET @x = '<age>12</age>'
-- works as expected
SELECT @x.query('data(/age)')
GO

DECLARE @x xml
SET @x = '<age>12</age>'
-- fails
-- Msg 2211, Level 16, State 1, Line 6
-- XQuery [query()]: Singleton (or empty sequence) required, found operand of type 'element(age,xdt:untypedAny) *'
SELECT @x.query('string(/age)')
GO

Oh. This message looks familiar. It turns out that XQuery functions are strongly typed also. Here's the definition of fn:string and fn:data:

fn:string($arg as item()?) as xs:string

fn:data($arg as item()*) as xdt:anyAtomicType*

The "item()*" means that data takes a sequence of 0-n items. "item()?" means that string only takes a sequence of 0-1 item. Let's fix it then.

SELECT @x.query('string(/age[1])')

Cool. Here's the test for comprehension. Let's try this with typed XML.

-- start with a schema collection

CREATE XML SCHEMA COLLECTION ages AS
'<xs:schema
   xmlns:xs="http://www.w3.org/2001/XMLSchema"
   targetNamespace="urn:ages"
   xmlns:tns="urn:ages">
<xs:element name="age" type="xs:int"/>
</xs:schema>
'
GO

DECLARE @x xml(ages)
SET @x = '<age xmlns="urn:ages">12</age>'
-- fails as expected
SELECT @x.query('
declare default namespace "urn:ages";
/age/text()')
GO

DECLARE @x xml(ages)
SET @x = '<age xmlns="urn:ages">12</age>'
-- works as expected
SELECT @x.query('
declare default namespace "urn:ages";
data(/age)')
GO

DECLARE @x xml(ages)
SET @x = '<age>12</age>'
-- fails ??!
SELECT @x.query('string(/age)')
GO

Why does the last query (against strongly typed XML) fail, even though there is a schema? How can you fix it? There are two different "right answers".

There's been lots of puzzled faces lately when I try to explain doing XQuery against strongly typed XML (XML typed by a SCHEMA COLLECTION) vs untyped XML. The largest FAQ is why when I have this document:

<person>
  <name>bob</name>
  <age>51</age>
</person>

using the value function (after assignment to @person) @person.value('/person/age', 'int') returns my favorite error:

Msg 2389, Level 16, State 1, Line 6
XQuery [value()]: Operator 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

Huh? Although you know by looking at the document that there is only one age element, the XQuery parser uses static evaluation. It doesn't read your document (or read your mind) and assumes there can be more than one age element. After all, there's no schema to enforce the singleton age element, I could have 3 or 4 ages. It doesn't want to guess and be wrong at execution time. Using:

@person.value('/person[1]/age[1]', 'int')

works. I can see why age[1] is required, but why person[1]? Doesn't XML have a single root element? Actually, no. SQL Server 2005 supports fragments (well-formed, multiple root) as well as documents. Fragment support is part of the XQuery 1.0/XPath 2.0 data model.

Most people get by that. The real fun starts when you do examples using untyped XML and XPath expressions with the text() node test. text() works just fine when using untyped XML, but fails against typed XML with simple content. Here's an example (the result of a discussion with Dan Sullivan):

CREATE XML SCHEMA COLLECTION root AS
'<xs:schema
   xmlns:xs="http://www.w3.org/2001/XMLSchema"
   targetNamespace="urn:geo"
   xmlns:tns="urn:geo">
<xs:element name="Root" type="xs:string"/>
</xs:schema>
'
GO

-- UNTYPED
-- this works
DECLARE @x  xml
set @x = '<g:Root xmlns:g="urn:geo">asdf</g:Root>'
select @x.query('
 declare namespace g="urn:geo"
 /g:Root/text()')

-- TYPED
-- Msg 9312, Level 16, State 1, Line 4
-- XQuery [query()]: 'text()' is not supported on simple typed
-- or 'http://www.w3.org/2001/XMLSchema#anyType' elements,
-- found 'element(g{urn:geo}:Root,xs:string) *'.

DECLARE @x  xml(root)
-- same document
set @x = '<g:Root xmlns:g="urn:geo">asdf</g:Root>'
select @x.query('declare namespace g="urn:geo"
/g:Root[1]/text()')

But why? Isn't text() a node test that returns the value of a text() node. After casting about in XQuery specs, and SQL BOL, I finally came across this in the XML Best Practices paper.

-- snip --

Data(),text() and string() accessors

XQuery has a function fn:data() to extract scalar, typed values from nodes, a node test text() to return text nodes, and the function fn:string() that returns the string value of a node. Their usages are sometimes confusing. Guidelines for their proper use in SQL Server 2005 are as follows. Consider the XML instance <age>12</age>.

Untyped XML: The path expression /age/text() returns the text node "12". The function fn:data(/age) returns the string value "12" and so does fn:string(/age).

Typed XML: The expression /age/text() returns static error for any simple typed <age> element. On the other hand, fn:data(/age) returns integer 12, while fn:string(/age) yields the string "12".

-- snip --

Well, that was confusing. But now I think I get it. When does a element not have a text() node (or more preicsely, not allow the text() node test)?? When it's a strong-typed query using a simple type element...that's when. But why? Although I know the rules now, I'm still somewhat baffled.

This is getting pretty long, more on this topic in a bit...

A new feature of SQL Server 2005 that has been fairly well publicized is the ability, on Windows 2003 operating systems, to enforce password stregth, expiration, and lockout policies on SQL Server logins, as the operating system enforces them on Windows logins. The way that this works is that SQL Server calls NetValidatePasswordPolicy, a Win32 function available on Windows 2003. So if I have a machine policy (either standalone or more likely inherited from a domain policy) that a password must be at least 8 characters long, the following DDL will fail:

CREATE LOGIN bob WITH PASSWORD = 'bob'

you need:

CREATE LOGIN bob WITH PASSWORD = 'bob000000'

However, did you realize that password on other secrets will follow policies as well? For example:

CREATE APPLICATION ROLE somerolename WITH PASSWORD = 'aaa'
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'aaa'
CREATE CERTIFICATE foo WITH SUBJECT = 'foo', ENCRYPTION_PASSWORD = 'aaa'
CREATE SYMMETRIC KEY skey WITH ALGORITHM = DES ENCRYPTION BY PASSWORD = 'aaa'

will all fail for the same policy reasons. The lone straggler, at least as of Dec CTP is ASYMMETRIC KEY. This works...

CREATE ASYMMETRIC KEY akey WITH ALGORITHM = RSA_512
 ENCRYPTION BY PASSWORD = 'a'

One of my students last week noticed that using a Service Broker object name (like a CONTRACT, SERVICE, and MESSAGE TYPE name) with the wrong case caused an error message. That's because Service Broker object names are case sensitive by deisgn. Because these identifiers can go over the wire, and you can't predict the collation of the database instance on the other side, they have to go by binary collation. Even when the objects are defined in a single database, you can't assume that's the only place they'll be used. Thanks to Roger Wolter for clarifying this...

Keep this in mind when you define a SERVICE (with the required associated contract) for query notifications or event notifications, as well as in your own broker apps.