Writing Unmaintainable Sql

I wrote this list of advice a couple of years ago, a few weeks after I was assigned the task of maintaining the kernel of a corporate information system written in TransactSql. Unfortunately, all the advice is based on real experience... -- DmitryJemerov

First of all, do not create a separate directory for the sources. It is best to store the source code together with the sources of a project you were working on two years ago, especially if that project contains at least 200 source code files. This will make it much harder for your enemies to find and steal the sources.
Be creative in placing procedures in source code files. Add every new procedure in a random file, preferably in the middle. Another good trick is to sort all the procedures in the largest source file alphabetically. However, doing this too often will spoil most of the fun. If you take all these measures, the complexity of your system will stunningly impress someone to whom you tell how the system works and show the control flow in the code.
Never delete anything from the code. Some day it may turn out to be useful! If a part of a procedure is no longer necessary - comment it, if an entire procedure is not necessary - leave it there. The larger your sources are, the more imposing they will look.
Avoid overhead caused by the EXEC statement. The more functionality you place in one procedure, the less time will be spent on calling other procedures from it. This point is especially important for the largest and the most complex procedure of your code.
Save keypresses and the memory of SQL Server. Never give names longer than 6 characters to variables and temporary tables. The names of the most important variables should be only one character long; choose that character randomly.
Another way to save SQL Server memory is to store several totally unrelated values in the same variable. On the other hand, if your procedure doesn't use any variables, it's better to have a couple of variables introduced just in case...
Speaking of the comments. Writing no comments at all is poor programming style. However, you shouldn't waste your precious time on writing verbose and detailed comments! Restrict yourself to the bare minimum. A good example is a comment /* select the values */ before a SELECT statement. Another effective trick is to put a comment /* temporary procedure, used for debugging */ before some of the most important procedures.
Never assign the same type to all numeric fields in a table. Be creative and try to optimize the code by assigning types with maximum variety. After all, everybody knows that cursors work too slowly!..
The last advice, the most important one. Remember: the undocumented features of your system are the most undocumented features in the world! The more pleasant surprises your API will contain, the more interesting the job of the programmers and testers will be!

Not that modular code is always a big win in TransactSQL: Stored procedures and triggers can't be nested deeper than 16 levels. In practice, we found that Sybase 4.9x versions failed at somewhere around 12 or 13 levels.

I worked on a business system a few years ago that did practically all business logic in triggers and stored procedures. To ship an order, for instance, you'd write the general ledger transaction for it which, as a side effect (read: trigger) would update inventory, change business objects, etc. We ran into all the hard limits on stored procedure size and nesting depth.

The project failed (of course! ;-) but not for technical reasons (really!). Seems the project never really made any business sense, and eventually they realized that they didn't have sufficient money to build the system anyway. -- JeffGrigg

Doing everything in the database and exploiting it to the full is one way to avoid DatabaseImpedanceMismatch. Usually wholehearted avoidance is in the other direction and the database just gets used as a dumb store of marshalled objects.

Actually, we never encountered the nesting level problem. That system didn't use any triggers at all (all the business and other logic was coded in stored procedures), and I would say that we never had more than about 5 levels of nesting. We had a class hierarchy with a system of event handlers, and everything was done by calling several event handlers in a row. Many of the event handlers didn't call any other procedures at all.

The project did fail, and partly because of technical reasons. But the main problem was not the nesting level: it was the scalability. Basically, all the processing of each user's action was done in one big transaction that sometimes accessed half of the tables in the database and took many seconds to complete. Imagine how well it worked when there were 10 users.

-- DmitryJemerov

Never delete anything from the code. Some day it may turn out to be useful! If a part of a procedure is no longer necessary - comment it, if an entire procedure is not necessary - leave it there.

In an environment where there is no source control, this is actually a Good Idea. Sadly, such environments are all too common even today (see MicrosoftAccess for an example).

"The project failed (of course! ;-). "

Hmmm. I was involved in writing an integrated trade feed/workflow tool that worked like this. It was fantastic, bullet-proof reliable and ran virtually without support (maybe 5 min a week?). The users loved it. The last I heard over 2m trades had gone through it.