Sorting the results of a MS SQL table-valued function
For whatever reason, MS SQL table-valued functions don't get the respect they deserve. Linq to entities pretends that they don't exist at all. I mean, seriously, how hard would it be to allow you to import them in the same way that you can import stored procedures?
Anyway, as a mechanism for abstracting your database schema, I like them. They are pre-compiled, strongly typed, and results cached, so they can get very fast. One thing they don't do well is return sorted values.
The following code won't work well when contained in a table-valued function:
This requires us to resort to doing something like this:
And even that result can be ignored sometimes. Consider a table that consists of a key, HierarchyId, and a description. We want to unwind the hierarchyId so that we have key, parent key, and description. This is necessary if we are working with the hierarchy outside of SQL because .net doesn't have any idea how to deal with hierarchyId, Geometry, Geography, and other newer (2008+) datatypes. It would sure be nice if Linq to Entities was updated to at least allow us to pass these items around as binary fields, if not the real .net types they are! But, I digress...
The following function won't return the results in the order we'd like it to:
One of the nice things about the HierarchyId data type is that when you sort by it, the parents and their children stay sorted in the right order; but not in this case.
The solution is to use an alternate method of sorting the results:
This sort is accepted by the function syntax, and will give us exactly the order we expect. And as a bonus, we can restore that order if we choose to sort the results by some other column. Otherwise, just ignore the RowNumber column that is returned.
Anyway, as a mechanism for abstracting your database schema, I like them. They are pre-compiled, strongly typed, and results cached, so they can get very fast. One thing they don't do well is return sorted values.
The following code won't work well when contained in a table-valued function:
SELECT * FROM Table1 ORDER BY Column1This requires us to resort to doing something like this:
SELECT TOP 100 PERCENT * FROM Table1 ORDER BY Column1And even that result can be ignored sometimes. Consider a table that consists of a key, HierarchyId, and a description. We want to unwind the hierarchyId so that we have key, parent key, and description. This is necessary if we are working with the hierarchy outside of SQL because .net doesn't have any idea how to deal with hierarchyId, Geometry, Geography, and other newer (2008+) datatypes. It would sure be nice if Linq to Entities was updated to at least allow us to pass these items around as binary fields, if not the real .net types they are! But, I digress...
The following function won't return the results in the order we'd like it to:
SELECT TOP 100 PERCENT
t1.key, t2.key parentKey, t1.description
from Table1 t1 join t2 on t1.Hier.GetAncestor(1) = h2.Hier
join Table1 t3 on h1.Hier.IsDescendantOf(t3.Hier) = 1
and h3.key = @topKey
ORDER BY t1.HierOne of the nice things about the HierarchyId data type is that when you sort by it, the parents and their children stay sorted in the right order; but not in this case.
The solution is to use an alternate method of sorting the results:
SELECT
ROW_NUMBER() OVER (ORDER BY h1.Hier) RowNumber,
t1.key, t2.key parentKey, t1.description
from Table1 t1 join t2 on t1.Hier.GetAncestor(1) = h2.Hier
join Table1 t3 on h1.Hier.IsDescendantOf(t3.Hier) = 1
and h3.key = @topKeyThis sort is accepted by the function syntax, and will give us exactly the order we expect. And as a bonus, we can restore that order if we choose to sort the results by some other column. Otherwise, just ignore the RowNumber column that is returned.
Comments