T-SQL: Beware the hardcoded switch

This months T-SQL Tuesday is brought to us by Adam Machanic (Blog|Twitter) - and as is his right, has chosen to go for Wednesday rather than Tuesday just to keep everyone on their toes. The subject is around c**p code, something which we're all guilty of in some form of another either as a result of laziness or lack of knowledge.

My contribution is fairly short and sweet today and something I'm often guilty of: The hardcoded lookup value embedded within a Stored Procedure or Function to control logic. You'll have seen this before (certainly if you've ever worked with me!)

IF @TheIDOfThisPerson = 15
EXEC dbo.UseThisStoredProcedure
ELSE
EXEC dbo.UseThisOtherOne

I often do this as a "temporary" hack to test some code or demonstrate a proof of concept, but it shouldn't come as a surprise to me when this finds its way into production. Fortunately, its been rare when the issue has caught me out in any serious way but I often find myself fixing stuff in a test environment after promoting code through the Development tiers because the ID values don't match across all servers. There is almost always a better way to achieve this and its often only an extra few clicks on the keyboard but somehow it still creeps into my game and other developers I work with.

I don't think anyone consciously CHOOSES to write something poorly but as we're constantly learning, its an inevitable byproduct. But lets embrace it. Just as long as we don't beat ourselves (or each other) about it, we can use it in a positive way as a learning point. While a developers goal is often to write less code, I prefer to focus on writing less C**P code and I don't think you have to sacrifice one over the other.

Admin: Sizing your transaction log

You will often hear DBA folk telling you how you need to size your transaction log appropriately and, like data files, avoid shrinking it. Why is this?

Well, lets try using a finance theme with a Credit Card analogy which records all your purchases but it also has a limit. When you fill your credit card and reach your limit you either need to pay off the balance or extend the limit by asking/begging your issuer for a bigger limit. Its possible of course (and will probably be successful) but will take up some of your time and may involve the provider asking some awkward questions. Of course, there may be times when you need to extend your limit, perhaps when you're moving house but there is a cost to it and its something you want to avoid doing too frequently. Hopefully when you set up your credit card, you do so with an appropriate limit based upon knowledge of your spending habits.

All of this is the same with a transaction log. It too has a limit which is its size and when it gets full it will either stop taking transactions or ask the operating system for more space (if set to Autogrow). Think of auto grow as akin to asking your credit card provider for that extension which will most probably granted but not without effort, here the time it takes to provision the file growth. While the file is growing, transactions will be queued up meaning your application can't process its requests and things will start to get ugly. Alternatively, rather than extend the limit of your log, you can perform a transaction log backup and "clear the balance" or if your database is in SIMPLE recovery mode, this behaviour is done on checkpoint.

Your ideal scenario is to have auto grow set on your log but with a maximum size set on the file (you don't want this credit card brining down the whole of the bank!!) and ideally it will be sized at a level which won't require too many file growth operations. You want to avoid shrinking the physical file as you don't want to repeatedly go through the "limit extension" process as its just unecessary cost. If you know your app needs 10GB, then just set it at that.