Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Usage » Designing Your Database » Designing the database table properties » Choosing data types for columns

Choosing data types for columns Next Page

Storing BLOBs in the database

A BLOB is an uninterpreted string of bytes or characters, stored as a value in a column. Common examples of a BLOB are picture or sound files. While BLOBs are typically large, you can store them in any character string or binary string data type such as CHAR, VARCHAR, NCHAR, BINARY, VARBINARY, and so on. Choose your data type and length depending on the content and length of BLOBs you expect to store.


While a character large object is commonly called a CLOB, a binary large object is called a BLOB, and the combination of both is called a LOB, the documentation uses only the acronym BLOB.

BLOB storage

When you create a column for storing BLOB values, you can control aspects of their storage. For example, you can specify that BLOBs up to a specified size be stored in the row (inline), while larger BLOBs are stored outside of the row in table extension pages. Additionally, you can specify that for BLOBs stored outside of the row, the first n bytes of the BLOB, also referred to as the prefix, are duplicated in the row. These storage aspects are controlled by the INLINE and PREFIX settings specified in the CREATE TABLE and ALTER TABLE statements. The values you specify for these settings can have unanticipated impacts on performance or disk storage requirements.

If neither INLINE nor PREFIX is specified, or if USE DEFAULT is specified, default values are applied as follows:

It is strongly recommended that you not set INLINE and PREFIX values unless there are specific requirements for which the defaults are not sufficient. The default values have been chosen to balance performance and disk space requirements. For example, if you set INLINE to a large value, and all the BLOBs are stored inline, row processing performance may degrade. If you set PREFIX too high, you increase the amount of disk space required to store BLOBs since the prefix data duplicates a portion of the BLOB.

If you do decide to set INLINE or PREFIX values, the INLINE length must not exceed the length of the column. Likewise, the PREFIX length, must not exceed the INLINE length.

For information about the defaults for the INLINE and PREFIX clauses, see CREATE TABLE statement.

In the case of compressed columns, regardless of the settings of INLINE and PREFIX, the behavior is as though INLINE and PREFIX were set to 0. That is, no prefix is stored, and the BLOB is stored in table extension pages, and if INDEX was specified (the default), BLOB indexing is still performed. If, at a later time, the column is uncompressed, whatever settings were previously in effect for INLINE and PREFIX are restored.

BLOB sharing

If a BLOB exceeds the inline size, and requires more than one database page for storage, the database server stores it so that it can be referenced by other rows in the same table, when possible. This is known as BLOB sharing. BLOB sharing is handled internally and is intended to reduce unnecessary duplication of BLOBs in the database.

BLOB sharing only occurs when you set values of one column to be equal to those of another column. For example, UPDATE t column1=column2;. In this example, if column2 contains BLOBs, instead of duplicating them in column1, pointers to the values in column2 are used instead.

When a BLOB is shared, the database server keeps track of how many other references there are to the BLOB. Once the database server determines that a BLOB is no longer referenced within a table, the BLOB is removed.

If a BLOB is shared between two uncompressed columns and one of those columns is then compressed, the BLOB will no longer be shared.