Create Table With 255 Columns or More
Sybase ASE  

Create Table With 255 Columns or More

In real world scenario, we have come across many situations wherein table will have more than 254 columns and when you try to create the same in Sybase ASE, you would face an error as below:

Number of variable length columns exceeds limit of 254 for allpage locked tables. CREATE TABLE for ‘table_name’ failed.

By default, locking mechanism is chosen as allpages. Lets see below table creation DDL:

create table test_254fields(
a1 varchar(50),
a2 varchar(50),
a3 varchar(50),
a4 varchar(50),
.
.
.
a252 varchar(50),
a253 varchar(50),
a254 varchar(50),
a255 varchar(50)
)

Here we are not defining any locking mechanism and hence, it would opt for allpages locking by default. And this table creation statement will throw an error. Now, there are many considerations to be taken:

  1. What if these data types were int(all of them, impractical in real-life scenario)
  2. What if these data types were tinyint(all of them, impractical again)
  3. What if data types are mixed, some of them int, some are char, some are varchar(500) or if it was double, nvarchar etc.

Please try yourself and send me error messages, if you get them.

Sybase ASE restricts number of columns in a table to 1024 however there is another restriction which comes into play for variable length columns.

Lets see how to solve this issue and create mentioned table:

create table test_254fields(
a1 varchar(50),
a2 varchar(50),
.
.
.
a254 varchar(50),
a255 varchar(50))
lock datarows
go

You will be able to solve this by using locking mechanism other than allpages, it could be datarows or datapages based on your requirement. Here I have chosen datarows, you may take datapages also.

Please leave your comment if you liked this post or have any feedback.

Leave A Comment