SQL

A VIEW is a virtual table in a database whose contents is defined by a query. To the end user the view functions and seems like an actual table with defined columns and rows of data. However these rows and columns are visible by the query results of the view. For most practical purposes views…

When designing the Database Tables for an application there are many factors that come into play. Often one issue that seems to crop up is the use of denormalized vs normalized tables for representing data. Below is a brief overview of what normalized and denormalized tables are. As for which one to use in your…

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…

When utilizing the iBATIS Framework as the persistence layer of your applications, you may get stumped as to why your LIKE statements are not being handled correctly and throws errors. When at first glance the errors have to do with other parameters in the query. The solution to this problem is to program the %…

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…

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…

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…

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: 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…

SQL Queries can often be written in multiple ways to obtain the same end result set. Tables for instance can be joined in the FROM clause or the WHERE clause of a SQL Statement. In the FROM Clause we can utilize LEFT, RIGHT, INNER keywords to denote if both sides of the statement needs to…