Several parts of SQL Server look as though they were started and then suddenly abandoned. The classic example is the TEXT datatype. Phil Factor has a theory on most things, and in this case it’s that the programmer responsible for implementing the TEXT datatype at Microsoft succumbed to the unbearable pressure of the job, one day, leaping up in his cubicle, throwing off all his clothes, and flinging himself through the window, shouting ‘I must join the penguins’. As a memorial to their departed colleague, his sorrowful team left the code in SQL Server at exactly the point it had been abandoned.

Anyone who has tried to use the TEXT (or IMAGE) datatype will immediately sympathize with Phil’s theory. It has so many restrictions placed on it, and so many quirks, that using it is like training a mule to jump through hoops.

Two other examples spring to mind, of features that seem to have been abandoned in mid-development. The first is the SSMS Templates. The macro processor that allows actual values to be substituted for placeholders is valuable, but curiously primitive in its implementation; more like a ‘proof of concept’ exercise. The datatype field doesn’t seem to be used at all, leaving just a crude system of string substitution with no attempt at validation. It is odd that such a useful system has never been developed further.

The second is SQLCMD. Anyone who bases a robust scripting system on SQLCMD soon becomes frustrated by its limitations. Why are there so many differences between the commands in the command-line version of the tool and the SSMS ‘SQLCMD mode’? Why can’t variables be used in expressions? Even the simplest conditional statement is impossible. One cannot perform any operations such as concatenation or simple maths on values within variables.  This is no harder than a second-year undergraduate project.

I wonder if the SQL Server product is now so colossal that not even Microsoft’s highly-advanced project management techniques can prevent the odd component from falling through the cracks. The TEXT data type has, to general relief, been replaced by VARCHAR(MAX), but the issues with SSMS Templates and SQLCMD remain.

Templates provide arguably the first, and best, productivity aid that a database developer should use and yet, under-developed and under-publicized by Microsoft, they remain a mystery to all but a few. SQLCMD is extraordinarily powerful, and many DBAs reach for it rather than SSMS, only to become frustrated by the inconsistencies and weaknesses of its scripting variables.

It reminds me of those, somehow elegiac, “work in progress” signs that one sometimes encounters on a remote back road, where no-one seems sure what work was planned or whether it will ever be completed. We encourage your own nominations for Simple-Talk’s ‘SQL Server TumbleWeed’ award. The winning nomination, added as a comment to the editorial blog, will receive a $50 Amazon voucher, and three runners-up will get a Simple-Talk gift pack.

Cheers,

Tony.