From InterBase

Go Up to Statement and Function Reference (Language Reference Guide)

Creates a new database. Available in gpre, DSQL, and isql.

 CREATE {DATABASE | SCHEMA} '<filespec>'
 [USER '<username>' [PASSWORD '<password>']]
 [PAGE_SIZE [=] <int>]
 [LENGTH [=] <int> [PAGE[S]]]
secondary_file = FILE 'filespec' [fileinfo] [secondary_file]
fileinfo = [LENGTH [=] int [PAGE[S]] | STARTING [AT [PAGE]] int }
In SQL statements passed to DSQL, omit the terminating semicolon. In embedded applications written in C and C++, and in isql, the semicolon is a terminating symbol for the statement, so it must be included.
Argument Description


  • A new database file specification.
  • File naming conventions are platform-specific.

USER ‘<username>’

  • Checks the <username> against valid user name and password combinations in the security database on the server where the database will reside.
  • Windows client applications must provide a user name when attaching to a server.

PASSWORD ‘<password>’

  • Checks the <password> against valid user name and password combinations in the security database on the server where the database will reside; can be up to 8 characters.
  • Windows client applications must provide a password when attaching to a server.

PAGE_SIZE [=] <int>

  • Size, in bytes, for database pages.
  • int can be 1024 (default), 2048, 4096, 8129, or 16384.

PREALLOCATE [=] <number> [PAGE[S]]

  • Reserves storage space in a file system for the requested number of database pages. It guarantees that a write will not fail due to lack of storage space over this range of pages.


  • Create new database with embedded user authentication enabled.


  • Sets default character set for a database.
  • <charset> is the name of a character set; if omitted, character set defaults to NONE.

FILE ‘<filespec>’

  • Names one or more secondary files to hold database pages after the primary file is filled.
  • For databases created on remote servers, secondary file specifications cannot include a node name.


Specifies the starting page number for a secondary file.

<int> [PAGE[S]]

  • Specifies the length of a primary or secondary database file.
  • Use for primary file only if defining a secondary file in the same statement.

Description: CREATE DATABASE creates a new, empty database and establishes the following characteristics for it:

  • The name of the primary file that identifies the database for users.
By default, databases are contained in single files.
  • The name of any secondary files in which the database is stored.
A database can reside in more than one disk file if additional file names are specified as secondary files. If a database is created on a remote server, secondary file specifications cannot include a node name.
  • The size of database pages.
Increasing page size can improve performance for the following reasons:
  • Indexes work faster because the depth of the index is kept to a minimum.
  • Keeping large rows on a single page is more efficient.
  • Blob data is stored and retrieved more efficiently when it fits on a single page.
If most transactions involve only a few rows of data, a smaller page size might be appropriate, since less data needs to be passed back and forth and less memory is used by the disk cache.
  • The number of pages in each database file.
  • The dialect of the database.
The initial dialect of the database is the dialect of the client that creates it. For example, if you are using isql, either start it with the -sql_dialect <n> switch or issue the SET SQL DIALECT <n> command before issuing the CREATE DATABASE command. Typically, you would create all databases in dialect 3. Dialect 1 exists to ease the migration of legacy databases.
To change the dialect of a database, use gfix or the Properties dialog in IBConsole. See the Migration appendix in the InterBase Operations Guide for information about migrating databases.
  • The character set used by the database.
For a list of the character sets recognized by InterBase, see Character Sets and Collation Orders (Language Reference Guide).
Choice of DEFAULT CHARACTER SET limits possible collation orders to a subset of all available collation orders. Given a specific character set, a specific collation order can be specified when data is selected, inserted, or updated in a column.
If you do not specify a default character set, the character set defaults to NONE. Using character set NONE means that there is no character set assumption for columns; data is stored and retrieved just as you originally entered it. You can load any character set into a column defined with NONE, but you cannot load that same data into another column that has been defined with a different character set. In that case, no transliteration is performed between the source and destination character sets, and transliteration errors may occur during assignment.
  • System tables that describe the structure of the database.
After creating the database, you define its tables, views, indexes, and system views as well as any triggers, generators, stored procedures, and UDFs that you need.
In DSQL, you must execute CREATE DATABASE EXECUTE IMMEDIATE. The database handle and transaction name, if present, must be initialized to zero prior to use.

Read-only databases :

Databases are always created in read-write mode. You can change a table to read-only mode in one of two ways: you can specify mode -read_only when you restore a backup, or you can use gfix -mode read_only to change the mode of a table to read-only. See “Database User Management” in the Operations Guide for more information on database configuration and maintenance.

About file sizes:

InterBase dynamically expands the last file in a database as needed. The maximum file size is system-dependent. This applies to single-file databases as well as to the last file of multifile databases. You should be aware that specifying a LENGTH for such files has no effect.

The total file size is the product of the number of database pages times the page size. The default page size is 4KB and the maximum page size is 16KB. However, InterBase files are small at creation time and increase in size as needed. The product of number of pages times page size represents a potential maximum size, not the size at creation.

Examples: The following isql statement creates a database in the current directory using isql:

CREATE DATABASE 'employee.ib';

The next embedded SQL statement creates a database with a page size of 2048 bytes rather than the default of 4096:

CREATE DATABASE 'employee.ib' PAGE_SIZE 2048;

The following embedded SQL statement creates a database stored in two files and specifies its default character set:

CREATE DATABASE 'employee.ib'
FILE 'employee2.ib' STARTING AT PAGE 10001;

See Also

Advance To: