 |
| SQL |
A better consultant than I (or, at least, one better recognised as such!), Adam Machanic, has posted a challenge to those of us in the SQL Server community who claim to be honest. You can see it by clicking on the caption of the logo on the right (I can't make the logo itself a link, as it is really a link to the page itself!).
The challenge is to publish a blog post today (Aug 10th, 2011) talking about the bad code we've written. The aim is to provide people with examples of what not to do.
null values
One new thing you learn when you start out with databases is the concept of "I don't know". Rather like the famous statement "there are things that we know we don't know, and there are things that we don't know we don't know" implies, these unknowns have to be treated with care. One learns to use tri-valued logic (True, False, null (or "I dunno!"), for example). One also learns that, because of nulls, there are sometimes queries where the sums don't always add up as you might expect. Here's an example.
You have a table to report from. You've made it by pulling data from tables supplied by colleagues from their parts of the system. One of the fields defines the type of an item - let's say possible values are Computer, Printer, Router, and Unknown. As this is just a temporary table you're got in memory to organise the data for reporting from, you've not bothered with any referential integrity ...
Computer 2706
Printer 522
Router 47
Unknown 174-------- ----
Total 3449
So you create your report and all looks good, until you see that the sum of the items that you've produced doesn't match the total sum - in fact, it's 551 too small. The real error is not in the query here that outputs from a sproc for an SSRS report: it's in the underlying assumptions!
select Type, count(Type) as 'Quantity'
from @taReport
where Type is not null
group by Type
order by Type
The programmer didn't realise that sometimes the Type might be unknown, so added the where Type is not null clause quite reasonably to avoid the report row saying that there were zero entries with type "NULL" (and, anyway, that always showed a zero!).
Adding this line just before the select statement would convert the null values into Unknowns, which is really what they are!
update @taReport set Type = 'Unknown' where Type is null
The Unknowns number goes to 725 and the total to 4000, which is the correct total number of objects!
Moral of the Story: A database contains a representation of the real world. It isn't a complete representation, to be sure, but so far as it goes, it must be accurate and complete. In this case the programmer made an assumption about the data quality that wasn't warranted.
Obsolete Code
When one works with a database with some history, such as when one is hired into a company with an already-functioning system, it behooves one to enquire into the history of the system. In one project I was investigating the reasons behind the terrible performance of some queries. One reason that I found was the use of functions in the where clauses of these queries - a well-known bad practice. Investigating further, I found that the functions (there were two or three only, used in a number of places) were very simple, and easily replaced with an existing native function in SQL Server 2000.
It was only after I had tracked down and fixed these function calls that I had a chance to learn more about the system history. It had originally been written in Access only, and these functions didn't exist so had to be written by hand! When the database part of the system was moved to SQL Server the code was re-written, apparently without any concern for whether any pieces were actually necessary!
Moral: Think about what you're doing! you may be creating something totally unnecessarily!
TTFN
