How to alter the size/type of an existing column

Versions 12.0 and Above
Sybase didn't provide a one-step way to do this until version 12.0. As of 12.0 or later, you can use the MODIFY option with ALTER TABLE to change a column's datatype or nullibility. To do this you must enable the SELECT INTO/BULK COPY DB option (since Sybase actually recopies the data in the process of modifying the data type). If you change a column which allows NULLs so that it doesn't, you'll need to specify a default too. The part of the Reference Manual's discussion of this that's important is the part that talks about what happens when ALTER TABLE causes a "data copy". I recommend you find these portions and read them carefully. (The reason for the warning is that the SELECT INTO/BULK COPY DB option allows non-logged operations. Non-logged operations invalidate the current transaction log until you do a new full DB dump. The documentation, unfortunately, is not clear on whether ALTER TABLE MODIFY performs a non-logged copy and therefore invalidates the transaction log, but it appears that it may. You may wish to test this in a non-production DB.)

Some additional information and restrictions are given in the section of the Transact-SQL User's Guide in the chapter on Creating Databases and Tables; section on Altering Existing Columns; Modifying Columns. See particularly the notes in the section called Data copying.

Earlier Versions or More Control
For ealier versions or those who would like to have complete control of what's happening, here are some other options.

One way to do it is to re-create the table as you'd like to have it and then copy the data over (this is great if the table's not too big). In outline form, that would look something like this:

create table new_old_table
/* unchanged column definitions here */
, old_column new_type
go
insert new_old_table
(/* column list here */)
select /* column list here */
from old_table
go
/* now that the data's there, create any other indexes you want (e.g. ones on the old column with the new data type)
create index extra_index /* blah blah */
go
drop table old_table
go
sp_rename new_old_table, old_table
go

If the table in question is large, this can be expensive in terms of disk space so you can BCP out the data in character form with fixed field widths or whatever and then create the new table and put the data back (this way you don't have to have two copies of the data loaded up at the same time).

Finally, in many cases, I've just used ALTER TABLE to add the new column with the appropriate data type. Populate it via an UPDATE statement, then rename the old column to have a name indicating that it's no longer used (e.g. "obsolete_data1"). This won't recover the storage used by the old version of the column, which isn't a big deal for some data types (including varchar or varbinary), but it's pretty simple.

Good luck!

J M Craig
nsjmcraig@netscape.net