Using CREATE DATABASE

Go Up to Creating a Database (Data Definition Guide)

CREATE DATABASE establishes a new database and populates its system tables, which are the tables that describe the internal structure of the database. CREATE DATABASE must occur before creating database tables, views, and indexes.

CREATE DATABASE optionally allows you to do the following:

• Specify a user name and a password
• Change the default page size of the new database
• Specify a default character set for the database
• Add secondary files to expand the database

CREATE DATABASE must be the first statement in the data definition file.

Important:
In DSQL, CREATE DATABASE can be executed only with EXECUTE IMMEDIATE. The database handle and transaction name, if present, must be initialized to zero prior to use.

The syntax for CREATE DATABASE is:

CREATE {DATABASE | SCHEMA} 'filespec'
 [USER 'username' [PASSWORD 'password']]
 [PAGE_SIZE [=] int]
 [LENGTH [=] int [PAGE[S]]]
 [DEFAULT CHARACTER SET charset]
 [<secondary_file>]
[WITH ADMIN OPTION];
<secondary_file> = FILE 'filespec' [<fileinfo>] [<secondary_file>]
<fileinfo> = LENGTH [=] int [PAGE[S]] | STARTING [AT [PAGE]] int
 [<fileinfo>]

Important:
Use single quotes to delimit strings such as file names, user names, and passwords.

Creating a Single-file Database

Although there are many optional parameters, CREATE DATABASE requires only one parameter, <filespec>, which is the new database file specification. The file specification contains the device name, path name, and database name.

By default, a database is created as a single file, called the primary file. The following example creates a single-file database, named employee.ib, in the current directory.

CREATE DATABASE 'employee.ib';

For more information about file naming conventions, see the Operations Guide.

Specifying file size for a single-file database

You can optionally specify a file length, in pages, for the primary file. For example, the following statement creates a database that is stored in one 10,000-page- long file:

CREATE DATABASE 'employee.ib' LENGTH 10000;

If the database grows larger than the specified file length, InterBase extends the primary file beyond the LENGTH limit until the disk space runs out. To avoid this, you can store a database in more than one file, called a secondary file.

Note:
Use LENGTH for the primary file only if defining a secondary file in the same statement.

Creating a Multifile Database

A multifile database consists of a primary file and one or more secondary files. You cannot specify what information goes into each secondary file because InterBase handles this automatically. Each secondary file is typically assigned to a different disk than that of the main database. In a multifile database, InterBase writes to the primary file until it has filled the specified number of pages, then proceeds to fill the next specified secondary file.

When you define a secondary file, you can choose to specify its size in database pages (LENGTH), or you can specify the initial page number of the following file (STARTING AT). InterBase always treats the final file of a multifile database as dynamically sizeable: it grows the last file as needed. Although specifying a LENGTH for the final file does not return an error, a LENGTH specification for the last–or only–file of a database is meaningless.

Important:
Whenever possible, create the database locally. If the database is created locally, secondary file names can include a full file specification, including a host or node names as well as a path and database file name. If you create the database on a remote server, secondary file specifications cannot include a node name, and all secondary files must reside on the same node.

Using LENGTH to Specify a Secondary File

The LENGTH parameter specifies the number of database pages for the file. The eventual maximum file size is then the number of pages times the page size for the database. (See Specifying Database Page Size.) The following example creates a database with a primary file and three secondary files. The primary file and the first two secondary files are each 10,000 pages long.

CREATE DATABASE 'employee.ib'
FILE 'employee2.ib' STARTING AT PAGE 10001 LENGTH 10000 PAGES
FILE 'employee3.ib' LENGTH 10000 PAGES
FILE 'employee4.ib';

Note:
Because file-naming conventions are platform-specific, for the sake of simplicity, none of the examples provided include the device and path name portions of the file specification.

Specifying the Starting Page Number of a Secondary File

If you do not declare a length for a secondary file, then you must specify a starting page number. STARTING AT specifies the beginning page number for a secondary file. The PAGE keyword is optional. You can specify a combination of length and starting page numbers for secondary files.

If you specify a STARTING AT parameter that is inconsistent with a LENGTH parameter for the previous file, the LENGTH specification takes precedence:

CREATE DATABASE 'employee.ib' LENGTH 10000
FILE 'employee2.ib' LENGTH 10000 PAGES
FILE 'employee3.ib' LENGTH 10000 PAGES
FILE 'employee4.ib';

The following example produces exactly the same results as the previous one, but uses a mixture of LENGTH and STARTING AT:

CREATE DATABASE 'employee.ib'
FILE 'employee2.ib' STARTING AT 10001 LENGTH 10000 PAGES
FILE 'employee3.ib' LENGTH 10000 PAGES
FILE 'employee4.ib';

Specifying User Name and Password (Using CREATE DATABASE)

If provided, the user name and password are checked against valid user name and password combinations in the security database on the server where the database will reside. Only the first eight characters of the password are significant.

Important:
Windows client applications must create their databases on a remote server. For these remote connections, the user name and password are not optional. Windows clients must provide the USER and PASSWORD options with CREATE DATABASE before connecting to a remote server.

The following statement creates a database with a user name and password:

CREATE DATABASE 'employee.ib' USER 'SALES' PASSWORD 'mycode';

Specifying Database Page Size

You can override the default page size of 4,096 bytes for database pages by specifying a different PAGE_SIZE. PAGE_SIZE can be 1024, 2048, 4096, 8192, or 16384. The next statement creates a single-file database with a page size of 2048 bytes:

CREATE DATABASE 'employee.ib' PAGE_SIZE 2048;

When to increase page size

Increasing page size can improve performance for several 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. (A row that is too large to fit on a single page requires more than one page fetch to read or write to it.)

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.

Changing page size for an existing database

To change a page size of an existing database, follow these steps:

1. Back up the database.

2. Restore the database using the PAGE_SIZE option to specify a new page size.

For more detailed information on backing up the database, see the Operations Guide.

Specifying the Default Character Set

DEFAULT CHARACTER SET allows you to optionally set the default character set for the database. The character set determines:

• What characters can be used in CHAR, VARCHAR, and BLOB text columns.
• The default collation order that is used in sorting a column.

Choosing a default character set is useful for all databases, even those where international use is not an issue. Choice of character set determines if transliteration among character sets is possible. For example, the following statement creates a database that uses the ISO8859_1 character set, typically used in Europe to support European languages:

CREATE DATABASE 'employee.ib'
 DEFAULT CHARACTER SET 'ISO8859_1';

For a list of the international character sets and collation orders that InterBase supports, see Character Sets and Collation Orders.

When there is No Default Character Set

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. No transliteration will be performed between the source and destination character sets, so in most cases, errors will occur during the attempted assignment.

For example:

CREATE TABLE MYDATA (PART_NUMBER CHARACTER(30) CHARACTER SET NONE);
SET NAMES LATIN1;
INSERT INTO MYDATA (PART_NUMBER) VALUES ('à');
SET NAMES DOS437;
SELECT * FROM MYDATA;

The data (“à”) is returned just as it was entered, without the à being transliterated from the input character (LATIN1) to the output character (DOS437). If the column had been set to anything other than NONE, the transliteration would have occurred.

Advance To:

• Creating Read-only Databases