My parents tried to instill in me "good manners" as if they were universal laws, to be obeyed at all times. Of course, one soon learns that manners are entirely optional, but also that they really do make social interactions easier and more pleasant. I just wish more people would remember that last part when designing databases or writing SQL.
If you can avoid violating SQL Server's relatively few 'laws' (ones that result is actual syntax errors), you can do pretty much anything you want. It is designed to be flexible in how you work with it. This is where the manners part comes in. As DBAs, architects and database developers, we've all encountered the occasional database that 'feels wrong' from the moment we peek inside. It works, it has broken no laws, but just like any unmannered person, it does not interact much with others, and is atrociously hard to manage.
In any shared database, you must become a social creature, co-habiting by means of a set of manners that is established by the Atomicity and Isolation principles of ACID. Good manners dictate that you use transactions as much as is necessary to avoid leaving a mess of half-finished work to trip up other processes, but don't create transactions that run so long that other users can't access the tables and data they need. Good manners also means writing SQL code using patterns of development suitable for the design of the underlying schema (relational versus dimensional), and in harmony with the optimizer. If you request certain columns in a table that have been indexed for a particular usage, the optimizer kindly works out how to limit how long you wait to a blink of an eye. If you forget your manners and try to fetch one row out of a billion row table with no indexes at all, the optimizer will try to oblige as best it can, but you will have plenty of time to ponder your thoughtlessness.
Having good database manners is not just about writing code that 'plays nice' with the optimizer. It extends to writing code that is 'friendly' to other database programmers. This means formatting your code and leaving documentation for the next poor person who gets saddled with working on it. I've seen queries that tackle essentially the same database task written in a staggering variety of ways. Sometimes, it's one complex statement, using multiple derived tables, CTEs, subqueries and so on. At other times, the programmer has broken down the query into several statements, with temporary tables and variables all over the place. Which way is more mannerly? I often have no idea; it depends on the individual situation. And very often, the programmer lacked the manners to give me any help. There is nothing more dispiriting than spending days trying to figure out some horrible-looking, completely undocumented code, rewriting it in a nicer, more maintainable form, only to find that the original version, nasty as it looked, was more efficient!
Of course, database manners encompass way more what I've had space to cover here. There are manners regarding releasing code, handing it off to the support team, when to add and when remove indexes, and so on. Often, organizations, like parents, will attempt to elevate these manners to the stature of 'law'. In truth, however, we should adopt good manners purely to make our lives easier, and to make database interactions nicer for programmers and users alike.
Louis Davidson (Guest Editor)
