SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Guest Editorial: On writing SQL

By Phil Factor,

Guest Editorial from Phil Factor

Seasoned developers are all slightly crazy, and I say that as a Developer myself. A long time ago, I remember realising with some surprise that I could hold, in my head, all the values of all the registers of an 8080 and Z80 chip whilst reading assembly source code. It made the chore of checking code so much easier, but I suspect that it didn't help my social life at all.

It is now a useless skill, as obsolete as fletching. A field-service engineer once told me he could whistle valid ASCII down an acoustic coupler as he had tested so many. Wasted talent! Not even devoted relatives would be impressed by that sort of trick now. The march of technology makes bone-heads of us all.

This thought occurred to me the other day when I was trying to explain to a C# developer how it was possible to debug long and elaborate routines in TSQL without the detail of what is happening in all the variables, or intermediate results. To get things running correctly without being able to set breakpoints seemed a strange alien trick to him. I was trying to introduce him to the art of developing a SQL Server-based application, and he had gone swivel-eyed in panic at the thought that he might have to program outside the nurturing and protective life-capsule of Visual Studio.

"How do you debug it?" he asked in amazement.

I must confess that I had to scratch my head a bit. Programming SQL is like riding a bike, think too hard of what you're doing and you fall off. It then occurred to me that I could, in fact, visualise the results of every SQL Expression as though I had a relational database in my brain. Inner joins and cross-joins were easy, but I have to admit that the more esoteric joins took time. I can 'eyeball' code quicker than it takes for Visual Studio to crank up, in all its baroque splendour. More difficult to describe is the ability to smell where the trouble is in code.

I'm not sure if this is the most important skill. I think that SQL programmers were developing by using regression tests long before it became fashionable again. This is not by choice: it is an essential when working on a stored procedure. First, long before the code is pummelled out on the keyboard, comes the thoughts "How on earth do I test this? What are the components of the algorithm? How do I validate each component? How do I check that each component works properly with the others?"

Do things in the right order and it all becomes easy. Test harnesses are like scaffolding. It is only after the routine works and the scaffolding is removed, one just marvels as to how it all got built.

I'm acutely aware that there are a number of tricks to being a highly productive SQL Programmer, and I cringe at the thought that I only know a few of them. So, what are they?

Phil Factor

Total article views: 444 | Views in the last 30 days: 1
Related Articles

Custom data flow component..

Developing ssis destination component


The Greatest Trick

In reading Gianpaolo Carraro's blog I came across this entry: The architect greatest trick?! In...


Stupid Coding Tricks: The T-SQL Mandelbrot

The bar for entry into CodeSOD is pretty straight forward: professionally-developed code that elicit...


Query Analyzer Tricks

Query Analyzer is a great tool for developing SQL code. There are a number of little tricks that can...


Stress Thoughts-Part 7

I’m going to finish up my thoughts on stress by talking about the philosophy I’ve evolved, some of t...

writing sql