Blog

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.

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.

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:

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

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.

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.

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)))

If you are using MS SQL Server and ever want to just obliterate your transaction log, you can use the following SQL (where your database name is "foo"): BACKUP LOG foo WITH TRUNCATE_ONLY DBCC SHRINKFILE(foo_log,2) Don't ever do this to a database you care about like, say, production. I wanted this because I am screwing around creating rainbow tables of SHA-1 hashes. The Cartesian product of joining a table to itself on 1=1 is very handy for producing all possible combinations of a set of characters. Inserting a few million records can put a lot of crap in your transaction log though.

A while back while building a monitoring tool for running processes on SQL Server 2005 I encountered the desire to color code SQL in the same manner of MS Query Analyzer (Or Management Studio) for HTML output. I hit up the CF-Talk list and Google for an existing ColdFusion implementation but got crickets. Not being one to give up, I created my own.

I installed SQL Server 2005 Express today on my home PC. For the most part it was painless, but there were a couple snags. First, it took me a couple tries to find the right download page. A bunch of the links on Microsoft's site kept redirecting me to the 2008 page. Still other pages wouldn't even mention 2005.

It has been mentioned in several places that MySQL 5.0.1 has a NO_BACKSLASH_ESCAPES mode it can be run in to prevent backslashes from being an escape character. Thanks to Azadi Saryev for pointing it out on my blog and Mark Krugers as well. Jake Munson even posted instructions for applying the setting to your SQL server at startup. For the record, you can also use this setting on a specific data source.