SQL

It is often the case that developers new to databases will try to perform large data modifications without batching. When this happens in most cases the modification will fail with the error of the transaction log being full after you are presented with the message The transaction log in the database is almost full. Your…

Transferring data between tables is a common task of database maintenance. It is used when updating database schemas or when you need to migrate data to a log or history table. There are 2 common methods for performing the transfer the first is done by use of a WHERE NOT EXISTS sub query, while the…

PHP Database Objects PDOs for short is a light-weight data abstraction layer for PHP. What this means is that it allows you to use an interface for performing data manipulations instead of using db specify functions such as mysql_query. So that if you needed to migrate to a different database you would not have to…

When developing a new system or software you will often find yourself unsure on the proper naming schema to utilize, or you may find out you need to comply with a company’s standard nomenclature. When this happens you may want to take note of the sp_rename stored procedure available in Sybase. This procedure allows you…

How many times have you found yourself defining multiple find or search methods for obtaining data in your Web Applications. For example for a Users table you may have a findByName, findById, FindByEmail, and etc. Utilizing the iBATIS framework and SQL Maps we can have all these sql statements condensed into a single dynamic select…

As a certain project at work evolves I find that more and more of my time is spent on optimizing the T-SQL queries that are utilized. Recently I have come across the issue that an index was not getting utilized in a sql query when I was using the UPPER or LOWER function to do…

The NOEXEC option of SQL is used to specify that the SQL statements are not to be executed. Utilizing this command with the SHOWPLAN option enables us to quickly see the query plans for a SQL statement without having the query actually execute. This gives us a quick output of the steps the DB Server…

Those familiar with SQL will have most likely used the UNION command when they want to combine select statements into 1 result set. Most often individuals will use UNION without understanding that additional work is done behind the scenes to remove duplicate entries. That is why it is important to understand your database and to…

Recently at work I have been working with Database Tables that have been denormalized to “enhance” their performance. As part of the denormilization process table columns where converted from int to the varchar data type. This change lead me to wonder if there is a performance gain from using varchar over int. After doing some…

A Trigger is a Stored Procedure executed upon the modification to a database table. Triggers can be set to occur on INSERT, DELETE, and UPDATE of a table row, and can even be set to happen before or after the change occurs. Upon a change to a table that has a trigger the body of…