Add a NOT NULL column to table using Alter Table command in Sybase ASE

While working with database development using Sybase ASE every now and then we tend to hit on a problem due to change in requirements to add new columns to the table definition.
Alter Table command is one such command that helps us add/remove/modify columns from the table efinition. Now you must be thinking that this sounds pretty easy so where is the catch? Well, the biggest catch while using alter table command is that the alter table command does not allow addition of a NOT NULL column to a Sybase table directly.

Wondering why I imphasized on the use of word directly? Well, because just try to use this simple command to add a NOT NULL column to the table -

alter table <<table name>>
add <<column name>> <<data type>>  NOT NULL

and you will be greeted with a nice error message suggesting that this is not allowed in Sybase ASE without specifying a default value for this column. Now, the reason behind this error message is that once the table has been created and a number of rows exist in this table then the addition of a new column should provide some default values for the rows already existing in the table as we want this column to be a NOT NULL column.

But, sometimes we may require that we add a new NOT NULL column to our Sybase ASE table without the use of default. Why? Because we dont want that once this new field has been added to our table then any developer or a user application can insert the data in the table without explicitly specifying some value for this column either purposely our by mistake.

So, now that our requirement has been justified and the error message hitting us what should we do to add a NOT NULL column to our existing table without specifying the default for this column?

Well, enough of the problem definition its time to figure out a solution. So here it is-

What is DSEdit utility in Sybase ASE and how to use it?

DSEdit or dsedit.exe - It is a program provided by Sybase Inc (now a SAP company) by which we can add new server entries. The dsedit program with the help of a configuration file also called the interface file makes entries in a 'sql.ini' file located in the "Sybase ASE" install directory.

Here is the screenshot of the dsedit utility on Windows machine -

Please click on the image to enlarge.

Sybase install directory can be found by $SYBASE in unix enviornment else it is usually present in C drive of your computer in case of Windows enviornment. The path for sql.ini in case of Windows is -

C:\Sybase\ini

The 'sql.ini' file is the main file that the Sybase client/server looks into for different server's socket information. A socket is basically a combination of ip address and a port number. A Sybase server would be listening for connections over a specified port number.

A sample sql.ini will look like this -

Find all the triggers on a table?

We all know that there are three types of triggers that can be created on a table, namely the insert, update and delete trigger. What if someone asks us to find out trigger associated to a table. Well the first answer would be very much easy - Simply type -

sp_depends <<Table Name>>

and we will get all the objects dependent on the table. The only catch here is that sysdepends table is the prime table that is being used by sp_depends stored procedure to extract the required information and as per the history related to this table - This isn't very reliable because Sybase SQL Server and Microsoft SQL Server both are very bad in maintaining entries in this table. So whats the answer to our problems.

Well, the answer to this is pretty much easy. Simply query the sysobjects table for the table under consideration and check for the following column entries -

1. instrig - For insert trigger object id.
2. updtrig - For update trigger object id and
3. deltrig - For delete trigger object id

Well, apart from these three trigger entries there is one more reserved trigger type which is select trigger represented by seltrig column in sysobjects, however this isn't used in Sybase ASE.

What is the maximum nesting level of a stored procedure in Sybase ASE?

Its a very obvious question for a Sybase developer to be faced with this kind of a question - What is the maximum level to which a stored procedures can be nested. Well the answer to somebody could be as short and specific as just run the following config system sp - sp_configure and search for "nested level" and you should see the right answer. This option is usually reflected as 16 however could be changed to some other value < 16.

But what if someone simply says - Well, I dont just believe figures, so can you just tell me a quick implementation of how exactly this works, to which simply say - Yes, Ofcourse!

So, in this article we will try creating two short and specific examples to demonstrate the maximum nesting levels of a stored procedure.

How to get stored procedure resultset into a table? - Method 2 - Component Integration Service

In my last article I tolld you how we can get the resultset from a stored procedure back into the calling stored procedure or a batch, however the previous method wanted us to do lots of modifications to the existing stored procedure. What if, we are asked to get the resultset of the stored procedure without doing any modifications to the existing called stored procedure? For example - what if someone asks us to use the output from sp_spaceused system stored procedure to get the total number of rows in the table under question?

Well in that case CIS or Component Integration Service in Sybase ASE comes to the rescue. Component Integration Service via the use of proxy tables will help us achieve our desired result. How? Well I will list all the steps required while using CIS and finally we will be able to get what we want. The steps which we must follow to use the CIS are as follows -

How to get stored procedure result into a table? - Method 1

One of the basic requirements while developing in Sybase ASE or any other database is to get the resultset from a stored procedure into another stored procedure or a batch file. While it is pretty simple in Microsoft SQL Server 2005 and later but its much difficult and ofcourse a grey area in Sybase ASE. In this first method we will try to show you the indirect way of getting the result set from one stored procedure back into some other stored procedure. Now, coming back to work..

To get the resultset from one stored procedure say - proc1 into proc2 we will create a temporary table in proc1 with the same structure as the resultset that we expect from proc2 and then we will insert resultset in this temporary table already created in proc1. This will help us to get the resultset back from the proc2 procedure.

To demonstrate this we will first create a stored procedure proc2 which will give us the results from one of our table Employee. The procedure is as follows -

create procedure proc2

as

begin

  select

     EmployeeId,

     EmployeeName,

     EmployeeSalary

  from

     Employee

  where

     EmployeeName = 'TOM'

end

go

The Employee table is is defined as follows -

create table Employee

(

  EmployeeId int,

  EmployeeName varchar(100),

  EmployeeAge int,

  EmployeeSalary float

)

go

Now as we can see that the proc2 stored procedure currently selects a certain subset of the table Employee where the EmployeeName = 'TOM' but the problem here is that we wont be able to use this resultset directly within another stored procedure or a batch script. So what should we do now?

What does set nocount on does in Sybase ASE or What is the use of nocount set option in Sybase ASE?

Sometimes when we check the system stored procedures we usually find something like this written -

set nocount on

among the first few lines of the stored procedure and you might be wondering what this set option is trying to achieve because when we try to write stored procedures we don't seem to use this set option.

To give the answer to this simple question we will first take your attention towards a very simple query -

select * from MyTAB

where MyTAB is a dummy table with only two fields EmpId and EmpAge, both being integers. This table has currently 100 rows. Now

Find total number of rows in a table without using count(*) or which system stored procedure can be used to find total number of records in a table?

Preferrably when you tend to ask someone - "How to find total number of records in a table?"; then you will receive a very generic answer -

"Simply try select count(*) from TableName and you will get the total number of records.. Simple isn't it?".

However, sometimes when the table sizes we are dealing with at the moment are very large - like in the databases of large financial firms who tend to have large transactional tables to store the data related to each transaction then in such cases try not to use the count(*) method as you might end up getting your session in hung state with no output for long.

The reason why you might end up in such a state is that count(*) tries to check the whole table to find the exact number of records in the table which makes it undergo a table scan and hence takes lot of time, while if what you really care about is the approx number of records in the table then try the following system stored procedure -

sp_spaceused <<table name>>

The system stored procedure sp_spaceused checks the OAM (Object Allocation Map) entry in the tables data page and computes the total number of records stored in the data table.

Simple isn't it?

What is the difference between Delete and Truncate command?

Everytime I go for an Interview it is one of the questions that has the highest probability of hitting u - "What is the difference between the Delete and the truncate command?"

Well there are lots of differences between the two commands after all if they would have been similar then the essence of the other would have not been there? So, simply just before giving the answer here are the two commands -

Delete Command -

delete from <table name> <optional where clause>

Truncate Command -

truncate table <table name>

So if we have a look at these two basic commands we find the following differences -

1. Delete command can have an optional where clause which can help delete only a subset of the rows present in the table. So, we can have only 5 rows deleted meeting our condition from a table of say around 1000 rows.
2. Truncate command does not have a where clause associated with it and hence can be used to remove all the entries in a table. Delete command in absence of a where clause behaves just like a truncate command with some exception in the internal working. What this difference is - we will come to know a little later in the article.
3. Delete command when used within transaction can be rollbacked wherease the same is not applicable with the truncate command. When a truncate command was initially designed it was not able to rollback the truncation of the table however with newer versions of ASE we can rollback the truncate command.
4. Delete command is a DML operation however the truncate command is a DDL operation.
5. As delete command makes entries in the log for each row deleted from the table hence it also fires the delete trigger. However, as truncate table operation makes entries for only particular data pages deallocation hence the triggers dont fire at the time of truncate command.

Now coming to the internal working of delete and truncate command -