Coder's Revolution - SQL

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

Tue, 27 Oct 2009 22:54:00 -0500

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. [More]

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

Wed, 21 Oct 2009 01:15:00 -0500

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. [More]

How To Get The SQL Server SPID Out Of SeeFusion

Mon, 21 Sep 2009 22:19:00 -0500

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. [More]

Phone Plan Matchup: SQL Brute Force Method

Mon, 07 Sep 2009 10:54:00 -0500

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. [More]

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

Fri, 21 Aug 2009 01:37:00 -0500

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. [More]

Bit By ANSI_NULLS- Are Your Nulls Antsy Too?

Wed, 05 Aug 2009 00:39:00 -0500

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 [More]

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

Wed, 13 May 2009 00:31:00 -0500

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. [More]

SQL Temp Tables

Sun, 03 May 2009 00:05:00 -0500

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: [More]

SQL Server: Extracting JUST Time From Datetime Value

Thu, 26 Mar 2009 01:18:00 -0500

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. [More]

SQL Server DTS: I Love It When A Plan Comes Together... Then Falls Apart Again.

Sun, 22 Mar 2009 21:52:00 -0500

At work, we are building some content management utilities to keep track of our training materials and document all of our content in a database. To get a head start, our marketing team started a large Excel spreadsheet to list, categorize, label, and tag our hundreds and hundreds of resources. We decided to attempt to automatically import some of the content directly from Excel to keep them from having to hand-enter it again. Seeing as how we are wandering around in the cold, dark, stone ages of SQL Server 2000 I thought I would throw the .XLS file at a DTS package and see what happened. [More]

SQL Server Performance: How Are My Indexes Being Using?

Sat, 14 Feb 2009 12:42:00 -0500

You may have a full-time DBA where you work, but a lot of us share the CF developer and DB developer hats. Your SQL Server's performance can be an easy thing to overlook if your database is very small, or your website gets very little traffic. The easy (but not always correct) answer to most performance problems is "add an index". Some indexes are an obvious help, but how do you tell if the less obvious ones are really being used? It is possible to have too many indexes. In addition to bloating the size of your database, they take time to update which can actually slow your application DOWN on inserts and updates. [More]

SQL Server Gotcha: Implicit Unicode Conversion

Fri, 13 Feb 2009 22:54:00 -0500

A recent thread on CF-Talk brought up this very good topic. It deals with the performance hit you can get when SQL Server implicitly converts your data to nchar and nvarchar for you when you have enabled Unicode support in your data sources. Unicode text cannot be stored in normal char or varchar fields. It must use nchar or nvarchar. These data types use two bytes per character, which means you can only store half as much text in them (limit 4,400 instead of 8,800). The problem is two fold:

SQL Server cannot directly compare a varchar and nvarchar value so it must convert one.
String manipulation or conversion on an indexed column will render the index useless

[More]

SQL Server: How Many Work/Week Days In Date Range

Wed, 15 Oct 2008 04:19:00 -0500

I had the need to calculate how many week days existed in an arbitrary range of dates today. I Googled for a while but didn't find anything I liked and I really didn't want to iterate over the entire range and count. For what it's worth, this is what I hacked out. [More]

MSSQL's openquery Saved Me

Sat, 04 Oct 2008 04:34:00 -0500

Sorry I've been quiet for the past few days. My Flex adventures took a quick detour through the massive land of our AS400 and DB2. Now that I had a prototype of my pretty line, bar, and pie charts I needed some real data. My job was to write a process to fetch our sales data from the AS400 server here at work nightly and populate some SQL Server tables with it. Easy Peasy, I thought. I didn't know what I was in for. [More]

SQL Date Parsing Error

Tue, 30 Sep 2008 16:38:00 -0500

I am writing some SQL to pull data from our AS400 here at work. I was getting the error "Arithmetic overflow error converting expression to data type datetime." from the following code where date_column contained a date formatted like 20080930: year(date_column) At first, I thought there was some bad data in the column. It turns out, the datatype was int as opposed to varchar like I had assumed. This fixed it: year(cast(date_column as varchar(8)))