MDBitz Development Resources

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 determining how to process a query.

Examples:

Updating all indexes of a Table

UPDATE STATISTICS T_USERS -- T_USERS = table name

Updating a single index of a Table

UPDATE STATISTICS T_USERS user_pk_idx -- user_pk_idx = index name

Resources:

• Sybase T-SQL : Online Manual

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

You may find that when developing Select queries for an Application that you may want to dynamically limit the results returned from a Stored Procedure. Upon first inspection you may think oh lets use a TOP clause with a limit variable passed in when the stored procedure is called.

CREATE PROCEDURE getROWS
    @LIMIT INT
AS
BEGIN
    SELECT TOP @LIMIT *
    FROM TABLE
    ORDER BY modTS DESC
END
GO

Unfortunately Sybase T-SQL does not allow variables in the limit clause. Don’t worry though the ROWCOUNT variable can be paramaterized. This gives us the following procedure that limits the results returned to our desired amount.

CREATE PROCEDURE getROWS
    @LIMIT INT
AS
BEGIN
    SET ROWCOUNT @LIMIT
    SELECT *
    FROM TABLE
    ORDER BY modTS DESC
    SET ROWCOUNT 0
END
GO

That is all there is to it, simply remember to use a ROWCOUNT statement instead of a TOP clause in your stored procedures.

When querying or working with data based on datetime, date, or time values Sybase has a group of Date functions available to help perform arithmetic operations.  These functions include GETDATE, MONTH, YEAR, DATEADD, and DATEDIFF.

To help illustrate some of these functions lets assume we have the following ITEM, ORDERS, and ORDERS_ITEM tables that contains the following columns and data.

How to obtain the current date and or time

The GETDATE function is used to get the current system date and time. Lets assume that we want to obtain a report of the orders that were made today, to do so we would want to compare the ORDERS.BUY_DATE column with the current date. As our table is not storing the time and only the date we would make use of the current_date function to only get the date (likewise the current_time function returns the time).

SELECT * FROM ORDERS WHERE BUY_DATE = CURRENT_DATE()

Assuming today’s date was February 3rd, 2009 we would have the following results:

Modifying the time using DATEADD

Now that we know how to get the current date and time in a Sybase T-SQL, lets assume we have a report that runs every day in the morning that reports the orders that were made the previous day. To do this we can modify our previous example to compare the date against the current date – 1 day. Sybase provides the DATEADD function to modify the date.

SELECT * FROM ORDERS
WHERE BUY_DATE = DATEADD( dd, -1, CURRENT_DATE() )

The DATEADD function takes 3 parameters the part of date to be modified, value to modify by, and the date to be modified.
Assuming today’s date was February 4th, 2009 we would have the same results as the previous example

Utilizing the month and year parts of a Date

Sybase provides two convenient methods for obtaining the month or year part of a Date. These functions can be used to query data for a particular period of time.  Both the MONTH, and YEAR functions take a single parameter and that is the date for which the month or year should be returned.

For our first example lets assume we want to know which items and how many were sold for the current month. To do this we will need to join the ORDERS and ITEM tables based on the ORDERS_ITEM linking table and return the sum of the quantity that were sold for each order.

SELECT i.NAME, SUM(oi.QUANTITY) AS NUM_SOLD
FROM ORDERS o
  JOIN ORDER_ITEM oi ON o.ID = oi.ORDER_ID
  JOIN ITEM i ON i.ID = oi.ITEM_ID
WHERE MONTH (o.BUY_DATE) = MONTH( CURRENT_DATE() )
GROUP BY i.NAME

Running this query assuming that the current date is the last day of December, 2008 would return the following results.

Sybase provides additional Date functions besides those mentioned above to familiarize yourself with them on I suggest accessing their online manual which can be accessed under References.

References

• Sybase – Manual : Date functions

Over the lifespan of a database driven application you may find that your existing database schema is not sufficient for new functionality. To maintain the integrity of your existing data Sybase and other SQL Servers enable administrators to modify existing tables by adding columns and constraints.

SQL Format

The basic format for the add column script is as follows

ALTER TABLE <em>table_name</em>
     ADD <em>column_name</em> <em>datatype DEFAULT null | not null
</em>

How the script works is that you specify the name of the table you want to modify then use the add clause to define the column you want to add to the table. In the add clause you specify the column name, type, null or not null, and if not null the default value to insert into existing data rows in the table.

Example

Lets assume we are starting with the following database table USERS

Assuming the above table and data lets add a column called ACTIVE which will tell us if that user is active or inactive.

ALTER TABLE USERS
    ADD ACTIVE TINYINT 1 not null

After running this script we will have the following table with the ACTIVE column for all existing records having a value of 1

Resources

• Sybase – Transact-SQL Add Column Manual