Working with LOBs in ASE has always been kind of a pain. There are not only multiple restrictions:

• They cannot be passed as parameters to stored procedures.
• You cannot define a local variable of type image or text, so you could only work with them through temporary tables.
• They cannot be used in group by, order by and union (this is not a problem for image columns but is for text columns).
• You cannot use isnull with them.
• They cannot be used in an index (although I doubt there are real scenarios where it would be needed).
• They cannot be used in subqueries and joins (although I doubt there are real scenarios where it would be needed).
• You can only use them in a where clause using like. This is usually not a problem for image columns but is definitely for text columns. And even for image columns being able to check whether they are null
• You cannot append data to it, so there if you gather data and want to create a long string out of it, your only solutions are to use a varchar(16384) or to do it outside of SQL e.g. in a perl script.
• You have to use special commands to write to them.

but it was also very inefficient from a storage point of view:

If any LOB (text/image) column on a row doesn’t contain NULL, at least one data page will be allocated per row for the LOBs. This means that if you have an empty string in in a text column for all rows, it will eat up one logical page (2K, 4K… whatever you have configured) per row although it actually doesn’t store any real data there. So with a page size of 4K and 1 million such entries, you’re using up 4 Gigabytes of disk space for nothing.

Now, we have a good and a bad news.

Let’s start with the bad news: I haven’t seen any changes to the restrictions listed above.

The good news: ASE 15.7 brings in two major improvements regarding the storage of LOBs:

1. Compression: ASE 15.7 supports in-database LOB compression. It supports FastLZ and ZLib compression. The LOB compression can be configured at database, table or column level.
2. In-Row LOBs: LOBs can be inlined i.e. when possible they are stored in the parent row. This is especially very useful in such scenarios where the LOBs mostly contain less than a logical page worth of data. A nice side effect is also that accessing this small LOBs is also faster since you have less I/O overhead when retrieving them. And the great part is that in-rowing and out-rowing is done seamlessly !

These two changes will not turn me into a fan of LOBs but it will at least make storing LOBs come closer to storing on disk from a storage space point of view.