My friend working on Java asked me - whether I can create a column of size varchar(8000). So I asked him to check the page size of the ASE Adaptive server (or simply Sybase server). He said its 2048. So, I confidently told him - NO, you cannot create it since page size is 2k and you are trying to create a column more than allowed size. But, to my surprise, he said there is already a table in the database having column as varchar(8000).
So, I started searching in Sybase manuals and other informative Sybase blogs and found out this - On version 12.5 and onwards, @@pagesize always returns the number - 2048. Kind of default page size. You can find actual page size of the server by checking the value of global variable @@maxpagesize.
Back to my friend's problem. So I asked him to tell me the value of global variable @@maxpagesize and he said its 8192. So, clearly its a 8k page server. He can still create a table having column size more than 8192 bytes or total number of bytes of all columns exceeding 8192 limit. But while creating such table, it will throw a warning. While populating such table, if value(s) exceeds 8192 limit, Adaptive server will throw an error and results in failure of insert/update command.
Morale of the story is: always check value of the global variable @@maxpagesize instead of relying on @@pagesize global variable.
P.S.: If your database design wants you to have a column to store value up to approx. 2047 bytes per row/record, you might want to think of TEXT datatype. For instance: you need to have a column in your table to store user comments or something similar which would occupy up to 2k size think of declaring it as TEXT datatype. Adaptive server allocates entire 2k page to store value for text datatype column. Always declare this column as null. Otherwise, even if you don't put any value while inserting/updating the TEXT column,Adaptive server will assign a text pointer and allocate entire 2k page to store the value (meaning sheer wastage of the space).
Also, consider following limitations of TEXT datatype before going for it:
So, I started searching in Sybase manuals and other informative Sybase blogs and found out this - On version 12.5 and onwards, @@pagesize always returns the number - 2048. Kind of default page size. You can find actual page size of the server by checking the value of global variable @@maxpagesize.
Back to my friend's problem. So I asked him to tell me the value of global variable @@maxpagesize and he said its 8192. So, clearly its a 8k page server. He can still create a table having column size more than 8192 bytes or total number of bytes of all columns exceeding 8192 limit. But while creating such table, it will throw a warning. While populating such table, if value(s) exceeds 8192 limit, Adaptive server will throw an error and results in failure of insert/update command.
Morale of the story is: always check value of the global variable @@maxpagesize instead of relying on @@pagesize global variable.
P.S.: If your database design wants you to have a column to store value up to approx. 2047 bytes per row/record, you might want to think of TEXT datatype. For instance: you need to have a column in your table to store user comments or something similar which would occupy up to 2k size think of declaring it as TEXT datatype. Adaptive server allocates entire 2k page to store value for text datatype column. Always declare this column as null. Otherwise, even if you don't put any value while inserting/updating the TEXT column,Adaptive server will assign a text pointer and allocate entire 2k page to store the value (meaning sheer wastage of the space).
Also, consider following limitations of TEXT datatype before going for it:
- A stored procedure can neither have a parameter as text datatype nor it can be declared as a local variable
- It cannot be indexed.
- Cannot be included in order by, group by, compute or union clauses
- Cannot be included in subqueries or joins
- Cannot be used in where clause. Exception is: you can use like operator on this column
Fabulous! Thank's!
ReplyDelete