Blog

Category Filtering: 'SQL'

Remove Filter


Improving Lucee's Query of Query Support

Posted by Brad Wood
Sep 12, 2020 01:29:00 UTC

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.  

MS SQL Server Table Variable And Nested Select Gotcha (Bug?)

Posted by Brad Wood
Oct 28, 2009 05:54:00 UTC
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.

Sequoia Voting System Witch Hunt, err... Study Project

Posted by Brad Wood
Oct 21, 2009 08:15:00 UTC
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.

How To Get The SQL Server SPID Out Of SeeFusion

Posted by Brad Wood
Sep 22, 2009 05:19:00 UTC
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.

Phone Plan Matchup: SQL Brute Force Method

Posted by Brad Wood
Sep 07, 2009 17:54:00 UTC
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.

SQL Server 2000 NULL Bug When Left Outer Joining A Nested View

Posted by Brad Wood
Aug 21, 2009 08:37:00 UTC
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.

Bit By ANSI_NULLS- Are Your Nulls Antsy Too?

Posted by Brad Wood
Aug 05, 2009 07:39:00 UTC
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

SQL Server Temp Tables: When Do I Create My Indexes?

Posted by Brad Wood
May 13, 2009 07:31:00 UTC
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.

SQL Temp Tables

Posted by Brad Wood
May 03, 2009 07:05:00 UTC
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:

SQL Server: Extracting JUST Time From Datetime Value

Posted by Brad Wood
Mar 26, 2009 08:18:00 UTC
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.

Site Updates

Entries Search