Many orthodoxies, or 'truisms', exist in IT; rules of programming that emerge in the light of shared experience and are then passed on from programmer to programmer and instilled as "the right way" to tackle a certain problem. The difficulty is that many of these truisms persist long after advances in technology have rendered them obsolete.

There is one persistent truism that always provokes vigorous head nodding amongst developers, and that is the assertion that the database is "not the right place" for any data presentation logic. According to this rule, SQL Server has no place in the presentation or rendering of data; its role is only to provide the raw datasets that can then be used in procedural code to represent the data on the screen.

However, with the introduction of the XML data type, as well as non-size-restricted types such as varchar(max), has SQL Server 2005 tipped the balance of the argument? Perhaps, now, there are circumstances where TSQL can make a strong contribution to the presentation of data

A while back, Simple-Talk published the Cross-Tab Pivot-table workbench, which showed one of several ways of providing cross-tab reports using TSQL. In our example, a stored procedure, spDynamicHTMLCrossTab, produced the HTML code which could be rendered directly on the browser. One of our readers responded by showing how it could be done using XML.

While well-received, there was the usual nervousness expressed by some readers, about TSQL code producing HTML. Nobody nowadays would bat an eyelid in surprise if you used SQL code to produce XML, which would subsequently be translated into HTML via XSLT. Why then strain at a gnat after swallowing a camel like this?

Sometimes, there will be cases where the insertion of extra logic in a .NET layer doesn’t seem to add much: If, for example, you wish to represent a hierarchical directory on a browser, such as an organisational list, is there anything intrinsically wrong, or morally suspect, in generating an XHTML fragment directly from a stored procedure, which could then be rendered on a browser? After all, the database knows all the factors which determine how a hierarchy should be represented.

Of course, there still remains a line which the database programmer should not cross. The database must never impose any restrictions in the way that data is rendered. The mark-up should be used to delimit the parts of the data in order to show the nature of the data, and its logical structure, without constraining the application programmer in any way.

An interesting comment was made at the Denver Code Camp last week that the interface between the database and the application developer should be a contract. This should be drawn up as part of the planning of a project in the light of the special demands and requirements of each project and team, rather than relying solely on existing orthodoxies.

We support this proposal, becasue we think it would force developers to constantly challenge these orthodoxies to make sure they are still true.What do you think? We'd love to hear from you and the best entry, made as a comment to this blog (you'll need to be signed in), will receive a $50 Amazon gift voucher.

Thanks to everyone for the fantastic response to the previous "How to layout SQL Code" editorial. The winner of the Amazon voucher is Tore. However, I give special mention to Alex Kuznetsov, louisducnguyen and billweh, who made it a tough choice.

Cheers,

Tony.