Some Original Expressions
- by Phil Factor
Guest Editorial for Simple-Talk newsletterIn a guest editorial for the Simple-Talk Newsletter, Phil Factor wonders if we are still likely to find some more novel and unexpected ways of using the newer features of Transact SQL: or maybe in some features that have always been there!
There can be a great deal of fun to be had in trying out
recent features of SQL Expressions to see if
they provide new functionality.
It is surprisingly rare to find things that couldn’t be done before, but
in a different and more cumbersome way;
but it is great to experiment or to read of someone else making that
discovery. One such recent feature is the
‘table value
constructor’, or ‘VALUES constructor’, that
managed to get into SQL Server 2008 from Standard SQL. This allows you to create derived tables of
up to 1000 rows neatly within select statements that consist of lists of row values. E.g.
SELECT Old_Welsh, number FROM (VALUES ('Un',1),('Dou',2),('Tri',3),('Petuar',4),('Pimp',5),('Chwech',6),('Seith',7),('Wyth',8),('Nau',9),('Dec',10)) AS WelshWordsToTen (Old_Welsh, number)
These values can be expressions that return single values,
including, surprisingly, subqueries. You can use this device to create views, or
in the USING clause of a MERGE statement. Joe Celko covered this here
and here. It can become extraordinarily handy to use
once one gets into the way of thinking in these terms, and I’ve rewritten a lot
of routines to use the constructor, but the old way of using UNION can be used
the same way, but is a little slower and more long-winded.
The use of scalar SQL subqueries as an expression in a
VALUES constructor, and then applied to a MERGE, has got me thinking. It looks
very clever, but what use could one put it to? I haven’t seen anything yet that
couldn’t be done almost as simply in SQL
Server 2000, but I’m hopeful that someone will come up with a way of solving a
tricky problem, just in the same way that a
freak of the XML syntax forever made the in-line production of delimited lists from an
expression easy, or that a weird
XML pirouette could do an elegant
pivot-table rotation.
It is in this sort of experimentation where the community of
users can make a real contribution. The dissemination of techniques such as the
Number, or Tally table, or the unconventional ways that the UPDATE statement
can be used, has been rapid due to articles and blogs. However, there is plenty
to be done to explore some of the less obvious features of Transact SQL. Even
some of the features introduced into SQL Server 2000 are hardly well-known.
Certain operations on data are still awkward to perform in
Transact SQL, but we mustn’t, I think, be too ready to state that certain
things can only be done in the application layer, or using a CLR routine. With
the vast array of features in the product, and with the tools that surround it,
I feel that there is generally a way of getting tricky things done. Or should
we just stick to our lasts and push anything difficult out into procedural
code? I’d love to know your views.
