T-SQL

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…

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…

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…

What is the GROUP BY Clause? The GROUP BY clause of T-SQL is used to divide the results returned into groups.  This technique is often utilized to obtain aggregate information from the table as in the number of users you have from each state or number of books an author has written. In addition you…

The DISTINCT command: The DISTINCT command is used for retrieving unique data entries dependent on the columns specified. The opposite way of thinking about it is that it eliminates duplicate result rows. For a basic example lets assume we have the following person table and data First Name Last Name City State Matthew Denton Fayetteville…

What is Pagination Pagination is the process of displaying a subset of the data on a web page at one time. The simplest example is a google search result in that after a search is performed you are displayed with the first x results as page 1. Then utilizing the page handlers you are able…