Does empty/null long binary column take up space in table?

[Former Member]

If does, how to avoid it?

thank!

[Former Member]

You will find SQL Anywhwere tries to save you storage space whenever possible. Unlike some other database technologies, with SQL Anywhere, LOBs will not allocate extra 'blob' pages unless required. There are a few layers to this.

The simplest case is the Null value case which Mark has covered already. I.E. NO extra space.

The 2nd layer, there is a space savings for short LOBs if they are less than the INLINE size, they are store inside your row and will not use blob pages. This can save you lots of space (in comparison to some other implementations) if most of your LOBS are small. This is true of you zero-length (ie. empty) LOB case; we need to store the length byte for those and that's it.

And there are other aspects of LOB storage you might want to also be aware of.

[Former Member]

Thank you for the answer! What if alter long binary column from not null to null, Does it cost any space in database?

[Former Member]

I believe you are only concerned about the larger LOBs here ... those that need their own extra page allocations outside the row ... Such pages (as with any page inside the database) once allocated will remain a part of the database but will be freed up for reuse or other purposes as appropriate.

We will try to associate a cluster of pages together for a specific object (to help for I/O purposes) but freed pages are reused in many different ways inside the database.

Are you seeing a specific issue with blobs in your usage?

[Former Member]

It added a lot spaces to database. How to free up them?

[Breck_Carter]

It depends on what you mean by "free up". Here is a simplistic explanation:

Deleted data becomes "free space" that is available for inserts.

The database server will not shrink the database file.

A "full reorganization" can be used to effectively shrink the database: dbunload everything, delete the .db file, dbinit a new .db file, dbisql to reload the data. The dbunload utility can be configured to do all four steps automatically.

[MarkCulp]

A NULL column of any type only takes up 1 bit in the row header.