CREATE DATABASE
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]]]
[WITH ADMIN OPTION]
[DEFAULT CHARACTER SET <charset>]
[secondary_file];
secondary_file = FILE 'filespec' [fileinfo] [secondary_file]
fileinfo = [LENGTH [=] int [PAGE[S]] | STARTING [AT [PAGE]] int }
[fileinfo]
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 |
|---|---|
|
‘<filespec>’ |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
WITH ADMIN OPTION |
|
|
|
|
|
|
|
|
|
Specifies the starting page number for a secondary file. |
|
|
|
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 theSET SQL DIALECT<n> command before issuing theCREATE DATABASEcommand. 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
gfixor 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 SETlimits 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
NONEmeans 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 withNONE, 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:
EXEC SQL
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:
EXEC SQL
CREATE DATABASE 'employee.ib'
DEFAULT CHARACTER SET ISO8859_1
FILE 'employee2.ib' STARTING AT PAGE 10001;