Blog

One of the really great features of CFML is the ability to run SQL against a result set in memory.  This allows you to union separate results together or even apply additional filtering on an exiting result if you can't control what the DB gives you.  Like everything, there is a time and a place for this.  There are people who strongly dislike QoQ (query of queries) but my take is that I think they're great when used with relatively small data sets and unless performance profiling shows they are causing issues, I have no problems using them.  

My coworker found this interesting little SQL Server behavior today that was quite startling at first glance. It's like punching 2 + 2 into your calculator and having the result come back as 5. After pin-pointing the cause for the behavior, it ALMOST makes sense... except for the fact that it doesn't make sense at all. It involves a randomized sub-select and table variables.

Matt Woodward pointed out this Slash Dot article today about the accidental release of code from the Sequoia Voting Systems and a web site dedicated to studying that code. Apparently the Election Defense Alliance obtained a copy of the election data for Riverside County, California. It came in the form of a Microsoft SQL Server backup that was SUPPOSED to have all the code such as stored procs and triggers redacted. I wandered over to the "Sequoia Voting System Study Project" and scored me a copy of the data.

I've never kept too quiet about my affection for SeeFusion as a ColdFusion monitoring tool. I use it for debugging, performance monitoring, and basic metrics gathering. Here's an old note on the JDBC URL wrappers that I found myself digging up last week. I don't even think you can find this nugget on the official SeeFusion site.

A few days ago on CF-Talk Greg Morphis asked for a method to find the cheapest combination of phone plans that satisfy a customer's requirements for the number of lines being used and the amount of minutes to share between the plans. At first, I thought the answer could be derived directly, but since the plans and their relative price/minute values are essentially random, they create different price breaks that change as you add minutes and lines. This means one combination of plans might be the cheapest for 500 minutes, but once you increase that to 600 minutes, a completely different set of plans might come into play that are now the cheapest.

I found a very interesting behavior today with SQL Server 2000 SP4 that I'm most certain is a bug. I Googled for a while and found a couple old threads bringing up similar issues but they were without confirmation nor resolution. My actual use case was fairly complicated and confusing. I thought long and hard and came up with a simple example of the problem that still made a shred of sense.

I had one of those (all-too common) moments today that I spent scratching my head at a page of SQL code that was failing for no apparent reason. What's worse is the EXACT same code worked in another window. After a good deal of poking and prodding I figured out what was wrong and it involved my ANSI_NULLS setting

My last blog post was originally the start of this one, but I got so carried away talking about the different types of temp tables I split it off. Today's topic is when to create indexes on SQL temp tables-- before or after you add the data. Many people out there seem to hold the same opinion on the subject. I don't like taking other people's word and I also like doing things the hard way. Therefore I created a series of tests to see which way really was fastest.

Here's a quick note about SQL temp tables. In SQL Server, there are three kinds of temporary tables you can use. The first kind (my favorite) are known as table variables. They only exist in memory and are not written to disk (unless your OS is low on RAM and starts swapping). Table variable names start with an "@" just like other T-SQL variables and the syntax for one is like so:

Tonight I found myself needing to extract just the time from a datetime column in SQL Server 2000. After a bit of Googling, it became clear that there are about 1 million articles on how to extract just the date from a datetime, but not very many articles taking about just getting time. For reference, this is how I ended up doing it.