MDBitz Development Resources

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 to rename a table, column, or index object be forewarned though that renaming a table can break stored procedures or queries that utilize the table and they will need to be updated appropriately.

Renaming a Database Table with sp_rename

To rename a table all you need to do is execute the procedure passing the name of the table to be modified and the new name for the table.

EXEC SP_RENAME MY_TABLE_OLD, MY_TABLE_NEW

Renaming a Table Column with sp_rename

To rename a column you need to specify the table and column in the first parameter and then in the 2nd parameter only put the new name of the column.

EXEC SP_RENAME MY_TABLE_NEW.old_id, new_id

Renaming a Table Index with sp_rename

To rename an index you need to specify the table and index in the first parameter and then in the 2nd parameter only put the new name of the index.

EXEC SP_RENAME MY_TABLE_NEW.id_idx, new_id_idx

It is important to note that if you have a column and index with the same name for a table you can specify a third parameter saying that you want to rename the index or column

EXEC SP_RENAME MY_TABLE_NEW.client_id, new_client_id, "index"

Resources

• Sybase Online : sp_rename function reference

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 execution time. The IO count is the amount of reads and writes performed by the execution of a query. This value is what can really tell you if your SQL query is optimized.

To view the IO performance of the query one can use the SET STATISTICS command of SYBASE. To enable the display of the logical reads and writes you would turn on the statistics by the command:

SET STATISTICS IO ON

And would turn it off by running the command:

SET STATISTICS IO OFF

Now that you can obtain the io performance of the query you can make use of the SHOWPLAN command to fine tune your query’s performance. The SHOWPLAN command is enabled similar to the SET STATISTICS command by toggling on and off by the ON/OFF keywords:

SET SHOWPLAN ON

By setting the SHOWPLAN command to ON you will be outputted with the steps that are performed by the query or procedure. The output will display all the selects, inserts, and creations that occur when running the sql statement. Using this input one is able to fine tune the query by the reorganization of statements, addition of indexes to remove or reduce Table Scans, as well as further optimization techniques.

When writing SQL we may find that we need to write a WHERE clause that checks if a columns is not a certain value. To do this we have the <> operator that means does not equal. However you may be surprised that you are not getting all the results you expected, this is because <> does not match null values. To get all values that are not equal you need to check that a value is not equal or that it is null.

Examples SQL

SELECT * FROM CLIENT
WHERE SENSITIVE <> 'Y' or SENSITIVE IS null

Most individuals having database experience with MySQL will find shortly after using Sybase DBMS that the backslash character does not escape characters. Instead in Sybase T-SQL the single quote itself acts as the escape character. Below is a sample UPDATE statement showing the difference in escaping the single quote character in MySQL and Sybase.

MySQL

UPDATE COMPANY SET NAME = 'Joseph\'s Shoes' WHERE ID = 3

Sybase

UPDATE COMPANY SET NAME = 'Joseph''s Shoes' WHERE ID = 3

A Cursor is a database structure that allows for the traversal of the records in a result set. In Sybase T-SQL a cursor functions by the following steps:

1. Declare the Cursor:
   To use a cursor you first must declare it by providing a name and the SELECT query that will provide the result set it will be traversing through.  The Syntax for declaring the Cursor is as follows:

   DECLARE cursor_name CURSOR
   FOR  SELECT ....

2. Open the Cursor:
   Once the Cursor is declared we must open the cursor to utilize it:

   OPEN cursor_name

3. Fetch Rows:
   Now that the Cursor is ready we use the FETCH command to return a result row.  When fetching a row we need to fetch it into variables defined prior to the Fetch. To provide the user with if the query was successful the @@sqlstatus variable is available, where 0 means success, 1 = error, and 2 is no more data. We can use the @@sqlstatus variable in a WHILE condition to traverse the entire result set.

   FETCH cursor_name INTO @var1, @var2, ...
   WHILE ( @@sqlstatus = 0 )
   BEGIN
     // do something WITH the DATA
     FETCH cursor_name INTO @var1, @var2, ...
   END

4. Close the Cursor:
   After traversal of the result set we close the cursor by use of the CLOSE command:

   CLOSE cursor_name

5. Deallocate the Cursor:
   Last we deallocate the cursor so that Cursor is removed from memory and can not be opened again:

   DEALLOCATE CURSOR cursor_name

Optimization

Be advised that when using SELECT statements that JOIN multiple tables or have sub-queries that performance can be affected. To optimize it is good practice to perform such selects into a temporary table and that the cursor is set up to select data from that temp table.

Resources

• Sybase T-SQL Manual – Cursors