Click here to monitor SSC

Simple-Talk columnist

Some Original Expressions

Published 27 May 2011 1:20 am

Guest Editorial for Simple-Talk newsletter

In 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.

5 Responses to “Some Original Expressions”

  1. BuggyFunBunny says:

    As high-core-count/SSD machines become the norm (that is, where SSD is the primary datastore), BCNF and higher normal forms become as fast, if not faster, than the flatfile schemas so beloved of coders. Just so much less data, and logically structured. In this emerging environment, procedural appendecies become less important.

    Drop some names. Codd laid out the RM as a closed logical data model. Pascal has said that a row is a business rule. Celko wrote a book, “Thinking in Sets”, demonstrating how to use data rather than code. The point is simple: logic expressed in code is really just embellished data, the logic is always an expression of some data comparison. Why not skip the middleman and just embed the logic in the data, as Codd laid out?

    The time will arrive where some engine (may be SQL Server, may be not) does implement the RM as fully as the SQL standard permits. The impediment has always been the mantra: “we must de-normalize for speed” which leads to flatfile schemas which in turn leads to iterative solutions. A vicious circle spins away. Remove that impediment, which the emerging machines do, as an engine which is fully SQL/RM compliant does, and declarative systems are now normal, so to speak.

    Moreover, the emergence of tablet/phone clients leads to a need for small data packages. The days of fifty line, 200 field screens are long gone. The data need be disassembled into more digestible bites, which can be picked, rather than typed.

    Throw in high speed AJAX-ian support, and we’re looking at a future that looks more like the days of VT-100s attached to a database on a unix machine; the input device is just that, the edit logic happens in the database directly, without having to provide an second version running in the client engine.

    Is that the Emerald City I see, Toto???

  2. paschott says:

    I don’t really have any ideas here, though I’ve been able to trick SSIS into generating XML output without scripting using some CASTs and exporting as a varchar(max). I hardly think that counts as something major. I did want to subscribe to the output of this list, though. This seems like it will be a very promising thread if we get more input.

    @BuggyFunBunny – I think you’re seeing the green of the screen with those VT-100s, not necessarily the Emerald City. :-) I appreciate the description, though; very colorful, even if that color is green or possibly amber.

  3. BuggyFunBunny says:


    I was describing the semantics of the constraint enforcement, not whether the client is pixels or characters. In fact, if you consider the issue, 99.44% of GUI input screens look just like their VT-100 predecessors, even down to being run by tab-order. To the extent that logic becomes directly declarative (data centric), and connection is high-speed, let the engine do the work.

  4. paschott says:

    I appreciate your descriptions – just thought it was funny as most of the VT-100′s I’ve seen have been green and you threw in the Emerald reference. :-) Totally agree that most GUI input screens are very tab-oriented. I don’t quite know how we’d do it in an intuitive fashion in another way. Forms, tabs, ordered fields – they just make sense.

    Data retrieval and display is a different story, of course – there are so many varied ways data can be displayed. Our biggest challenge now seems to be displaying more and more data at faster and faster speeds while staying on the same hardware. (Yeah – something’s eventually got to give.) But we digress from the topic.

    For us, we’ve pushed some things out to procedural code and caches, if only to reduce latency for static data that won’t change within a session, but _does_ change over time. We can do this with our hardware and database, but in order to keep things moving in an acceptable timeframe, we’ve pulled the relevant data, pushed it off of our database server, then combined it and cached it using C# and NoSQL stores. Frankly, there are times I’m a little amazed at what we’ve managed to do with our somewhat limited resources. It still feels like a battle between making things pretty and performant at the same time.

  5. BuggyFunBunny says:

    The Yellow Brick Road meme (we’re on it to get from flatfile/NoSql/OODB/xml/etc. datastores with client side constraint enforcement [back] to “dumb” terminals connected to smart engines running BCNF RDBMS) came to life before I’ve added the VT-100 allusion. Hadn’t done the green/green thing on purpose; hadn’t really noticed until you pointed it out. Serendipity, so to speak. They weren’t bad Singers, either.

Leave a Reply

Blog archive