T-SQL

In most instances you will be using a User Interface of one sort or another to execute Transact-SQL commands against your Sybase Database Servers. In rare occasions however I find that I execute commands through the dos prompt via the Interactive SQL Utility or iSQL as it is more commonly referred. Opening a Connection BCP…

When performing database maintenance you will occasionally find the need to export data out of your database tables to an operating system for storage, or conversely import data to a table from a file. You may find yourself needing to do these tasks for data backup or for inserting data that comes from a 3rd…

Today I was running some tests on my T-SQL Procedures and noticed that for some reason my Select statement was performing in seconds compared to my Procedure which would take minutes to complete. After a few hours I stumbled upon my mistake I had used the date datatype in the procedure parameters when my database…

Database maintenance can be a full -time job when working within a large corporation. Cleanup or deletion of records can especially be time-consuming and error prone when dealing with large data tables with millions of records. Often developers will try to run a basic delete statement without knowing about batching or truncating the activity log.…

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…

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…

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…

When writing SQL queries we often measure the performance of the query or procedure by the amount of time it takes to complete. Often I found myself saying that if it completes in a couple seconds then it is good. Lately however I have found myself more concerned with the IO count then with the…

After inserting, modifying, or deleting a large amount of records in a table that contains indexes performance of queries can be increased by use of the update statistics query. The update statistics query creates or replaces statistics on a column and stores them in the system tables systabstats and sysstatistics. Sybase utilizes these statistics when…