Notes on GetSchemaTable

I recent found myself using the SqlDataReader.GetSchemaTable method. I made some notes detailing what it will return for various data types in SQL Server 2005.

Notes on GetSchemaTable

			Numeric
ColumnName	DataType	ColumnSize	Precision	Scale	Notes
bigint	Int64	8	19	255
bit	Boolean	1	255	255
decimal	Decimal	17	18	0
int	Int32	4	10	255
money	Decimal	8	19	255
numeric	Decimal	17	18	0
smallint	Int16	2	5	255
smallmoney	Decimal	4	10	255
tinyint	Byte	1	3	255
float	Double	8	15	255
real	Single	4	7	255
datetime	DateTime	8	23	3
smalldatetime	DateTime	4	16	0
char	String	20	255	255
varchar	String	20	255	255
text	String	2147483647	255	255	isLong
nchar	String	20	255	255
nvarchar	String	20	255	255
varchar(max)	String	2147483647	255	255	isLong
nvarchar(max)	String	2147483647	255	255	isLong
ntext	String	1073741823	255	255	isLong
timestamp	Byte[]	8	255	255	IsRowVersion
xml	String	2147483647	255	255	isLong

I created these notes through the simple method of creating a table with every possible data type. Or rather, every data type that I am interested in, which is very nearly the same thing--I mean, who uses varbinary, really? I then used the GetSchemaTable method on my new table, and inspected the results.

There is a page on MSDN that appears to contain this information, but upon closer inspection, it must be talking about something else entirely. For instance, it claims that the SQL Server data type "varchar" has no mapping whatsoever, not even as a System.String.

Certainly, this makes sense upon further reflection: it's possible to put any varchar value into a string, but not the other way around. Still, it's not terribly useful if you just want to know what sort of data type you can reasonably expect to get out of a given column.

I further note that the MSDN documentation states that the NumericPrecision column should be null for non-numeric data types, but this is simply not true.

Flattening recursive XML data in SQL Server 2005

I recently had to flatten an XML data set that looked something like this:

<topic id="1" name="planets">
  <topicList>
    <topic id="2" name="jupiter">
      <topicList>
        <topic id="3" name="ganymede" />
        <topic id="18" name="callisto" />
        <topic id="92" name="io">
          <topicList>
            <topic id="21" name="europa" />
          </topicList>
        </topic>
      </topicList>
    </topic>
    <topic id="7" name="saturn">
      <topicList>
        <topic id="11" name="titan" />
      </topicList>
    </topic>
  </topicList>
</topic>

I needed to produce a result set that preserved the parent/child relationship between each topic. In other words, I needed to know that Callisto belonged to Jupiter when I finished. At first, I thought this was clearly a job for recursion. I envisioned leaping deftly from each topic to each of its children in turn. Indeed, with SQL Server 2005 and common table expressions, this is quite possible:

Declare @xml XML;
Set @xml = '.... (see above) ....';
With topics (id, parent, name, children)
As (
  Select
    @xml.value('/topic[1]/@id', 'integer') As id,
    Cast(null As integer) As parent,
    @xml.value('/topic[1]/@name', 'varchar(50)') As name,
    @xml.query('/topic/topicList') As children

  Union All

  Select
    child.node.value('@id', 'integer') As id,
    topics.id As parent,
    child.node.value('@name', 'varchar(50)') As name,
    child.node.query('topicList') As children
  From topics
  Cross Apply topics.children.nodes('/topicList/topic') As child(node))
Select id, parent, name
From topics;

As with all recursive CTEs, this one is broken into two parts. The rows returned by the first half are used as starting points for the recursion in the second half. In this case, the first half returns only a single row:

Select
  @xml.value('/topic[1]/@id', 'integer') As id,
  Cast(null As integer) As parent,
  @xml.value('/topic[1]/@name', 'varchar(50)') As name,
  @xml.query('/topic/topicList') As children

Result:

id	parent	name	children
1	NULL	planets	{xml}

The second half handles the recursion part. The secret here is in the final column. Each topic row includes a reference to its own children. The recursive portion of the CTE works on each of these children, one by one. Each child further includes a reference to its own children, and so the cycle continues. Thus, each recursion builds on the rows produced by the previous recursion.

Results:

cycle	id	parent	name
1	1	NULL	planets
2	2	1	jupiter
2	7	1	saturn
3	18	2	callisto
3	3	2	ganymede
3	92	2	io
3	11	7	titan
4	21	92	europa

As interesting as this is, it is not terribly performant. But fear not: a better solution exists! Recall that each XML node contains information concerning its position within its document; it has references to its children, siblings, and parent nodes. Rather than trying to recursively walk the XML tree, then, it is possible to simply get a list of all "topic" nodes and derive the parent information from the node itself. This results in a much simpler query:

Select
  topics.topic.value('@id', 'integer') As id,
  topics.topic.value('../../@id', 'integer') As parent,
  topics.topic.value('@name', 'varchar(50)') As name
From @xml.nodes('//topic') As topics(topic)

Yup, that's all. The @xml.nodes('//topic') finds every <topic/> node, no matter how deeply nested it is. Then the parent id is found simply by looking at each topic's ancestors: topics.topic.value('../../@id', 'integer').

Quick, easy, and simple!

SQL Server Subquery Quirk

Here's an interesting quirk of SQL Server 2005 that bit me today.

Consider this fictional query:

Select pageID, givenName
From phy.Contacts
Where pageID In (
  Select pageID
  From (
    Select id
    From oth.Pages
    Where [Hidden] = 0) As [subquery])

Here there are two levels of subqueries here.  The innermost level returns a set of [id] values.  The next level up is asking the innermost level for a set of [pageID] values.  Alas, the innermost query does not contain a [pageID] column.

However, this query executes anyway, without any errors, and appears to return decent results.  This surprised me: the middle-level query is looking for a column that doesn't exist.  I would have expected an error message in this case, not a result set.

This behavior is explained in a tiny "Caution" box in MSDN:

If a column is referenced in a subquery that does not exist in the table referenced by the subquery's FROM clause, but exists in a table referenced by the outer query's FROM clause, the query executes without error. SQL Server implicitly qualifies the column in the subquery with the table name in the outer query.

So apparently, the middle-level query is pulling the pageID column from the OUTER query instead.  Fascinating.