Manners, learned as soon as possible in life, give us social skills without us having to figure them out. While our parents and teachers made us feel as if they were laws, manners vary from laws in one important way. Laws are hard and fast rules that exist naturally (like gravity or relativity), or from government to protect us from one another. Manners simply make social interactions pleasant.

Without manners, we would never be able to get along with others in society. As a computer scientist, it will turn out that manners play a far greater role in our decisions than laws ever will. Laws will be generally be enforced by a compiler providing you with descriptive error messages like “syntax error near ‘.’.” Try as you may, if you don’t hunt down that violation of the compiler’s laws, you will not proceed. If you can avoid violating the laws, you can do anything you want to. This is where manners start to figure in.

Every user interface and every operating system defines a certain set of language and visual cues that you will come to learn and follow soon after you start to program with it. For example, pick up your cell phone and check out a few apps that you regularly use. If you think about which are easiest to use, they most certainly have a common set of control mechanisms defined by the OS designers. The rest of the apps you use will be the ones where you constantly do something wrong with, because things feel “wrong.”

Microsoft SQL Server is, in a sense, an operating system: For the DBA/SQL programmer, we don’t really have visual cues to work with, but patterns of development based on engine suitability and interacting with other human programmers. SQL Server has a core set of patterns it optimizes for. However, since it is in fact a product that wants to attract as many customers as possible it is in fact very flexible in what it will allow. It doesn’t legally enforce rules, but relies on manners. Many terrible-looking and awful-feeling databases are in use this very day that are simply atrociously created, and you probably know at least one. These databases are generally hard to manage and administrate, but they do work: they’ve broken no laws. Of course, like almost any unmannered human will tell you, they generally don’t interact with others very much. So in order to make things work as well as possible, there are manners we should understand and adopt.

So what are these database-manners? First up, we have high-level database-design patterns such as relational design, and dimensional design. They are complimentary patterns, with relational design being ideally suited for capturing transactions from a client. Dimensional design is a reworking of the database that is meant to make the relational data more easily reported on. Together they work nicely, and successfully if you follow simple principles. Each has its own way of working, and somewhat byzantine etiquette about how to index, how to tune usage, etc.

You have to mind your “please and thank you”s with the optimizer too. Writing your queries in one way against a dimensional structure will let it recognize that you are querying a star schema and reply to your query faster. If you apply columnstore indexes to your structures, it really kicks that pattern into gear. If, instead, you try to apply a columnstore index to an OLTP table, you will either get slow single-row lookup times (clustered) or you will violate the law that says you can’t modify the table. If your manners kick in, and you use certain columns in a table that have been indexed for a particular usage, the optimizer kindly works 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 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. By taking time to understand what the optimizer does best, based on how you have designed your database, you will interact with it in a way that is most harmonious for you both.

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. The isolation principle says that transactions are as isolated from one another as you want them to be. You can turn off locking, or lock the entire database with every transaction. It is truly up to the developer, and how mannerly they program. Even if you don’t know that you are affecting the isolation properties, the way that you fetch and modify data will have an effect on what data other users can modify and fetch as well. Atomicity means that every set of operations that you do as a group (in a transaction,) must either complete or not complete. Good manners would say that you use transactions as much as is necessary to avoid leaving a mess of half-finished work for other processes to trip up on, but no more or other users suffer.

The last type of manners that I will touch on in the blog today is the sort of manners regarding the interaction of programmers. There are many queries that can be written as one complex statement, with multiple derived tables, CTEs, subqueries, etc; or they can be broken down into several statements with temporary tables and variables all over the place. Which way is more mannerly? I have no idea, as in far too many cases it is based on an individual situation to know. The manners here regard formatting your code and leaving documentation for the next poor person who gets saddled with working with your code. Too often you see code that looks horrible, even if it may turn out to be amazingly efficient. So you spend 3 days trying to figure it out, only to rewrite it in a nicer to look at but far less efficient version, left to wonder what you did wrong.

Does this list cover all of the manners that you need to follow for SQL development, even at a high level? Not even close as there are manners regarding releasing code, handing it off to the support team, when to add and when remove indexes, etc. Often these manners are elevated to morals for an organization, but truly we are doing them because it makes life easier on us, not that the computer system can’t handle it.