ColdFusion Muse

Clustered indexes on a Non Primary-Key (MS SQL 2000)

Ever wonder why a table is only allowed one "clustered" index? It's because the clustered index is the actual sort order that of the table. When you implement a clustered index you are "rearranging" the rows so that they are actually ordered by that index. That makes this index faster. Choosing the right clustered index can have a major impact on the speed of searches in your table. Wait a minute - isn't the Primary Key always the clustered index? Well... yes, when you use enterprise manager to set a primary key you will find that it also designates that column or columns as the default clustered index. It doesn't have to be that way however.

MS SQL and the Tragedy of Log File Obesity

Did you ever have this annoying problem? About once every 2 months a customer will call panicky because the server is generating errors that say the "transaction log is full". Many (maybe most) customers don't know what it is or what it's for - but they suddenly have a problem with it. The transaction log is the history of all the database changes from the time of the last complete backup. It's purpose is to provide recovery for you AFTER the last complete backup. That is to say, if you have a complete backup from 1 a.m. and a transaction log, you could restore your database back to "1:00 a.m." status - and then restore it "forward" to a particular point in time.

This useful feature comes at a cost. Everything must be recorded in the transaction log. The transaction log on a database with many updates, deletes and inserts can grow ginormous (thanks Elf)! I saw one today that was 9 gigabytes for a 12 megabyte database - a logging and tracking database. Unless you've had this problem you may not know how to fix it. Here are two ways to do it.

Why You should worry about your execution plan

No I'm not talking about dead man walking. I'm talking about your database execution plan. I want to give fair warning to all of you Microsoft haters out there (and you know who you are) that I'm going to use lingo from Microsoft SQL Server 2000. It's a ubiquitous and full-featured database with good documentation regarding this subject. Much of what is said here applies to other databases as well (no doubt using different lingo). So please, feel free to post comments of how Oracle or MySQL or PostgreSQL or Interbase or your flat file - all have a great way of doing this. But please don't post about how your favorite DB is so great and Microsoft is the spawn of Satan. That's not helpful and it's makes me want to poke out my eye with an ice pick! Whew! Now that that's out of the way.

The database execution plan is the series of steps that a database takes to deliver a particular query or task. These steps are cached on the DB server. When you run a query it looks in the cache for a plan that matches. If it finds one, it uses it. If not, it creates a new one. Why is this important? Because the more often your DB Server finds a matching plan in the cache, the better it performs. In fact, it can run significantly faster when it is not tasked with constantly building execution plans from scratch. Here's the rub, much of the query code written in Coldfusion requires the RDBMS to compile a new execution plan. Here's why.

DTS Export Part 2

Well obviously I need to catch up on DTS. Dave Ross pointed out to me that there is an option for "copy objects and data" that is a part of the DTS wizard. I never tried that option. I simply didn't absorb what it said. In my head I had the 3 options separated as

• Copy Tables
• Copy using a query
• Copy the schema only (objects - tables, users etc.)

Having tried it I can tell you it does a perfectly wonderful job of copying the data, views, stored procedures etc. There is even an option to eliminate the object level permissions (like owner). And it doesn't generate constraint errors because it's smart enough to copy the objects and data in an appropriate order. Thanks Dave - you just saved me some time - even if I have to eat crow (ha).

Database Exporting Using DTS

A tedious but necessary task is moving a database. Whether it's installing a new application or migrating servers, just about all developers have had to do it. If you've ever moved DATA from 1 SQL database to another you know there are many ways make it work. You can restore from backup; detach, copy and re-attach a file; or use DTS (Data Transformation Services).

I often find myself using DTS because I do not have physical access to the DB Server, or perhaps because the new database already contains some objects or tables that I want or need. For example, if I have an application that manages inventory and an application that manages permissions and I wanted to move them into a single database for ease of management - DTS would be the way to go. Actually DTS has some powerful capabilities that most of us leave largely untouched. Most of the time we are interested in a one time migration of data from database "A" to database "B". DTS does good job of this. It's fast and handles the migration very smoothly. There are some issues however.