FirebirdSQL logo

Character Indexes

The maximum length for an index key equals one quarter of the page size, i.e. from 1,024 — for page size 4,096 — to 8,192 bytes — for page size 32,768.The maximum length of an indexed string is 9 bytes less than that quarter-page limit.

Calculating Maximum Length of an Indexed String Field

The following formula calculates the maximum length of an indexed string (in characters):

max_char_length = FLOOR((page_size / 4 - 9) / N)

where N is the number of bytes per character in the character set.

The table below shows the maximum length of an indexed string (in characters), according to page size and character set, calculated using this formula.

Table 1. Maximum Index Lengths by Page Size and Character Size

Page Size

Bytes per character

1

2

3

4

6

4,096

1,015

507

338

253

169

8,192

2,039

1,019

679

509

339

16,384

4,087

2,043

1,362

1,021

681

32,768

8,183

4,091

2,727

2,045

1,363

Note

With case-insensitive collations (“_CI”), one character in the index key will occupy not 4, but 6 (six) bytes, so the maximum key length for a page of — for example — 4,096 bytes, will be 169 characters.

BINARY

Data Type Declaration Format
BINARY [(length)]
Table 1. BINARY Type Parameters
Parameter Description

length

Length in bytes between 1 and 32,767;defaults to 1.

BINARY is a fixed-length binary data type, and is an SQL standard-compliant alias for CHAR(length) CHARACTER SET OCTETS.Values shorter than the declared length are padded with NUL (0x00) up to the declared length.

Note

Some tools may report the type as CHAR CHARACTER SET OCTETS instead of BINARY.

CHAR

Data Type Declaration Format
{CHAR | CHARACTER} [(length)]
  [CHARACTER SET <set>] [COLLATE <name>]
Table 1. CHAR Type Parameters
Parameter Description

length

Length in characters, defaults to 1.A valid length is from 1 to the maximum number of characters that can be accommodated within 32,767 bytes.

set

Character set name

name

Collation name

CHAR is a fixed-length character data type.Values shorter than the declared length are padded with spaces up to the declared length.The pad character does not have to be a space (0x20): it depends on the character set.For example, the pad character for the OCTETS character set is NUL (0x00).

Fixed-length character data can be used to store codes whose length is standard and has a definite “width”.An example of such a code is an EAN13 barcode — 13 characters, all filled.

Note
  • CHAR CHARACTER SET OCTETS has the alias BINARY

  • Formally, the COLLATE clause is not part of the data type declaration, and its position depends on the syntax of the statement.