CREATE DATABASE statement

Use this statement to create a database. The database is stored as an operating system file.

Syntax
CREATE DATABASE db-filename-string [ create-option ... ]
create-option : 
   [ ACCENT { RESPECT | IGNORE | FRENCH } ]
   [ ASE [ COMPATIBLE ] ]
   [ BLANK PADDING { ON | OFF } ]
   [ CASE { RESPECT | IGNORE } ]
   [ CHECKSUM { ON | OFF } ]
   [ COLLATION collation-label[ ( collation-tailoring-string ) ] ]
   [ DATABASE SIZE size { KB | MB | GB | PAGES | BYTES } ]
   [ DBA USER userid ]
   [ DBA PASSWORD password ]
   [ ENCODING encoding-label ]
   [ ENCRYPTED [ TABLE ] { algorithm-key-spec | OFF } ]
   [ JCONNECT { ON | OFF } ]
   [ PAGE SIZE page-size ]
   [ NCHAR COLLATION nchar-collation-label[ ( collation-tailoring-string ) ] ]
   [ [ TRANSACTION ] { LOG OFF | LOG ON [ log-filename-string ]
       [ MIRROR mirror-filename-string ] } ]
page-size :
2048 | 4096 | 8192 | 16384 | 32768
algorithm-key-spec:
ON
| [ ON ] KEY key [ ALGORITHM AES-algorithm ]
| [ ON ] ALGORITHM AES-algorithm KEY key
| [ ON ] ALGORITHM 'SIMPLE'
AES-algorithm : 
'AES' | 'AES256' | 'AES_FIPS' | 'AES256_FIPS'
key : quoted string
Parameters

The file names (db-filename-string, log-filename-string, and mirror-filename-string) are strings containing operating system file names. As literal strings, they must be enclosed in single quotes.

  • If you specify a path, any backslash characters (\) must be doubled if they are followed by an n or an x. Escaping them prevents them from being interpreted as new line characters (\n) or as hexadecimal numbers (\x), according to the rules for strings in SQL.

    It is always safer to escape the backslash character. For example:

    CREATE DATABASE 'c:\\databases\\my_db.db'
    LOG ON 'e:\\logdrive\\my_db.log';
  • If you do not specify a path, or a relative path, the database file is created relative to the working directory of the database server. If you specify no path for a transaction log file, the file is created in the same directory as the database file. It is recommended that you store the database files and the transaction log on separate disks on the computer.

  • If you provide no file extension, a file is created with extension .db for databases, .log for the transaction log, and .mlg for the transaction log mirror.

You cannot specify utility_db for db-filename-string. This name is reserved for the utility database. See Using the utility database.

  • ACCENT clause   This clause is used to specify accent sensitivity for the database. Support for this clause is deprecated. Use the collation tailoring options provided for the COLLATION and NCHAR COLLATION clauses to specify accent sensitivity.

    The ACCENT clause applies only when using the UCA (Unicode Collation Algorithm) for the collation specified in the COLLATION or NCHAR COLLATION clause. ACCENT RESPECT causes the UCA string comparison to respect accent differences between letters. For example, e is less than é. ACCENT FRENCH is similar to ACCENT RESPECT, except that accents are compared from right to left, consistent with the rules of the French language. ACCENT IGNORE causes string comparisons to ignore accents. For example, e is equal to é.

    If accent sensitivity is not specified when the database is created, default accent sensitivity for comparisons and sorting is insensitive, with one exception; for Japanese databases created with a UCA collation, default accent sensitivity is sensitive.

    For more information about character sets, see International languages and character sets.

  • ASE COMPATIBLE clause   Do not create the SYS.SYSCOLUMNS and SYS.SYSINDEXES views. By default, these views are created for compatibility with system tables available in Watcom SQL (version 4 and earlier of this software). These views conflict with the Sybase Adaptive Server Enterprise compatibility views dbo.syscolumns and dbo.sysindexes.

  • BLANK PADDING clause   SQL Anywhere compares all strings as if they are varying length and stored using the VARCHAR domain. This includes string comparisons involving fixed length CHAR or NCHAR columns. In addition, SQL Anywhere never trims or pads values with trailing blanks when the values are stored in the database.

    By default, SQL Anywhere treats blanks as significant characters. For example, the value 'a ' (the character 'a' followed by a blank) is not equivalent to the single-character string 'a'. Inequality comparisons also treat a blank as any other character in the collation.

    If blank padding is enabled (specifying BLANK PADDING ON), the semantics of string comparisons more closely follow the ANSI/ISO SQL standard. With blank-padding enabled, SQL Anywhere ignores trailing blanks in any comparison.

    In the example above, an equality comparison of 'a ' to 'a' in a blank-padded database returns TRUE. With a blank-padded database, fixed-length string values are padded with blanks when they are fetched by an application. Whether the application receives a string truncation warning on such an assignment is controlled by the ansi_blanks connection option. See ansi_blanks option [compatibility].

  • CASE clause   This clause is used to specify case sensitivity for the database. Support for this clause is deprecated. Use the collation tailoring options provided for the COLLATION and NCHAR COLLATION clauses to specify case sensitivity.

    CASE RESPECT causes case-sensitive string comparisons for all CHAR and NCHAR data types. Comparisons using UCA consider the case of a letter only if the base letters and accents are all equal. For all other collations, uppercase and lowercase letters are distinct; for example, a is less than A, which is less than b, and so on. CASE IGNORE causes case-insensitive string comparisons. Uppercase and lowercase letters are considered to be exactly equal.

    If case sensitivity is not specified when the database is created, default case sensitivity for comparisons and sorting is insensitive, with one exception; for Japanese databases created with a UCA collation, default case sensitivity is sensitive.

    CASE RESPECT is provided for compatibility with the ISO/ANSI SQL standard. Identifiers in the database are always case insensitive, even in case-sensitive databases.

    For more information about character sets, see International languages and character sets.

  • CHECKSUM clause   Checksums are used to determine whether a database page has been modified on disk. When you create a database with checksums enabled, a checksum is calculated for each page just before it is written to disk. The next time the page is read from disk, the page's checksum is recalculated and compared to the checksum stored on the page. If the checksums are different, then the page has been modified on disk and an error occurs. Databases created with checksums enabled can also be validated using checksums. You can check whether a database was created with checksums enabled by executing the following statement:
    SELECT DB_PROPERTY ( 'Checksum' );

    This query returns ON if checksums are turned on, otherwise, it returns OFF. Checksums are turned off by default, so if the CHECKSUM clause is omitted, OFF is applied.

    Regardless of the setting of this clause, the database server always enables checksums for databases running on storage devices such as removable drives, and databases running on Windows Mobile to help provide early detection if the database file becomes corrupt. It also calculates checksums for critical pages during validation activities. See Using checksums to detect corruption, Validation utility (dbvalid), sa_validate system procedure, or VALIDATE statement.

  • COLLATION clause   The collation specified by the COLLATION clause is used for sorting and comparison of character data types (CHAR, VARCHAR, and LONG VARCHAR). The collation provides character comparison and ordering information for the encoding (character set) being used. If the COLLATION clause is not specified, SQL Anywhere chooses a collation based on the operating system language and encoding.

    The collation can be chosen from the list of collations that use the SQL Anywhere Collation Algorithm, or it can be the Unicode Collation Algorithm (UCA). If UCA is specified, you should also specify the ENCODING clause.

    It is important to choose your collation carefully. It cannot be changed after the database has been created. See Choosing collations.

    For a list of supported collations, see Recommended character sets and collations, and Supported and alternate collations.

    Optionally, you can specify collation tailoring options (collation-tailoring-string) for additional control over the sorting and comparing of characters. These options take the form of keyword=value pairs, assembled in parentheses, following the collation name. For example, ... CHAR COLLATION 'UCA(locale=es;case=respect;accent=respect)'.

  • DATABASE SIZE clause   Pre-allocating space for the database helps reduce the risk of running out of space on the drive the database is located on. As well, it can help improve performance by increasing the amount of data that can be stored in the database before the database server needs to grow the database, which can be a time-consuming operation. You can use KB, MB, GB, or PAGES to specify units of kilobytes, megabytes, gigabytes, or pages respectively.

  • DBA USER clause   Use this clause to specify a DBA user for the database. When you use this clause, you can no longer connect to the database as the default DBA user. If you do not specify this clause, the default DBA user ID is created.

  • DBA PASSWORD clause   You can specify a different password for the DBA database user. If you do not specify this clause, the default password (sql) is used for the DBA user.

  • ENCODING clause   Most collations specified in the COLLATION clause dictate both the encoding (character set) and ordering. For those collations, the ENCODING clause should not be specified. However, if the value specified in the COLLATION clause is UCA (Unicode Collation Algorithm), use the ENCODING clause to specify a locale-specific encoding and get the benefits of the UCA for comparison and ordering. The ENCODING clause may specify UTF-8 or any single-byte encoding for CHAR data types. ENCODING may not specify a multibyte encoding other than UTF-8.

    If you choose the UCA collation, you can optionally specify collation tailoring options. See Collation tailoring options.

    If COLLATION is set to UCA and ENCODING is not specified, then SQL Anywhere uses UTF-8. For more information about the recommended encodings and collations, see Recommended character sets and collations.

    For more information about how to obtain the list of SQL Anywhere supported encodings, see Supported character sets.

  • ENCRYPTED or ENCRYPTED TABLE clause   Encryption makes stored data unreadable. Use the ENCRYPTED keyword (without TABLE) when you want to encrypt the entire database. Use the ENCRYPTED TABLE clause when you only want to enable table encryption. Enabling table encryption means that the tables that are subsequently created or altered using the ENCRYPTED clause are encrypted using the settings you specified at database creation. See Table encryption.

    There are two levels of database and table encryption: simple and strong. Simple encryption is equivalent to obfuscation. The data is unreadable, but someone with cryptographic expertise could decipher the data. Strong encryption makes the data is unreadable and virtually undecipherable.

    For simple encryption, specify ENCRYPTED ON ALGORITHM SIMPLE, or ENCRYPTED ALGORITHM SIMPLE, or specify the ENCRYPTED ON clause without specifying an algorithm or key.

    For strong encryption, specify ENCRYPTED ON ALGORITHM with a 128-bit or 256-bit AES algorithm, and the KEY clause to specify an encryption key. It is recommended that you choose a value for your key that is at least 16 characters long, contains a mix of uppercase and lowercase, and includes numbers, letters, and special characters.

    On Windows Mobile, the AES_FIPS and AES256_FIPS algorithms are only supported with ARM processors.

    Caution

    For strongly encrypted databases, be sure to store a copy of the key in a safe location. If you lose the encryption key there is no way to access the data, even with the assistance of technical support. The database must be discarded and you must create a new database.

    For more information about strong database encryption, see Strong encryption.

    You can also create an encrypted copy of an existing database using the CREATE ENCRYPTED DATABASE statement. See CREATE ENCRYPTED DATABASE statement.

  • JCONNECT clause   To allow the jConnect JDBC driver access to system catalog information, specify JCONNECT ON. This will install the system objects that provide jConnect support. Specify JCONNECT OFF if you want to exclude the jConnect system objects. You can still use JDBC, as long as you do not access system information. JCONNECT is ON by default.

    If you are creating a database for use on Windows Mobile, see Using jConnect on Windows Mobile.

  • NCHAR COLLATION clause   The collation specified by the NCHAR COLLATION clause is used for sorting and comparison of national character data types (NCHAR, NVARCHAR, and LONG NVARCHAR). The collation provides character ordering information for the UTF-8 encoding (character set) used for national characters. If the NCHAR COLLATION clause is not specified, SQL Anywhere uses the Unicode Collation Algorithm (UCA). The only other allowed collation is UTF8BIN, which provides a binary ordering of all characters whose encoding is greater than 0x7E. See Choosing collations.

    Optionally, you can specify collation tailoring options (collation-tailoring-string) for additional control over the sorting and comparing of characters. These options take the form of keyword=value pairs, assembled in a quoted string, following the collation name. For example, ... NCHAR COLLATION 'UCA(locale=es;case=respect;accent=respect)'. If you specify the ACCENT or CASE clause and a collation tailoring string that contains settings for case and accent, the values of the ACCENT and CASE clauses are used as defaults only.

    For a list of the supported collation tailoring options, see Collation tailoring options.

    Note

    When you specify the UCA collation, all collation tailoring options are supported. For all other collations, only the case sensitivity tailoring option is supported.

    Note

    Databases created with collation tailoring options cannot be started using a pre-10.0.1 database server.

  • PAGE SIZE clause   The page size for a database can be 2048, 4096, 8192, 16384, or 32768 bytes. The default page size is 4096 bytes. Large databases generally obtain performance benefits from a larger page size, but there can be additional overhead associated with large page sizes.

    For example,

    CREATE DATABASE 'c:\\databases\\my_db.db'
    PAGE SIZE 4096;
    Page size limit

    The page size cannot be larger than the page size used by the current server. The server page size is taken from the first set of databases started or is set on the server command line using the -gp option. See -gp server option.

  • TRANSACTION LOG clause   The transaction log is a file where the database server logs all changes made to the database. The transaction log plays a key role in backup and recovery, and in data replication.

    The MIRROR clause of the TRANSACTION clause allows you to provide a file name if you want to use a transaction log mirror. A transaction log mirror is an identical copy of a transaction log, usually maintained on a separate device, for greater protection of your data. By default, SQL Anywhere does not use a transaction log mirror.

Remarks

Creates a database file with the supplied name and attributes. This statement is not supported in procedures, triggers, events, or batches.

Permissions

The permissions required to execute this statement are set on the server command line, using the -gu option. The default setting is to require DBA authority.

The account under which the database server is running must have write permissions on the directories where files are created.

Side effects

An operating system file is created.

See also
Standards and compatibility
  • SQL/2003   Vendor extension.

Examples

The following statement creates a database file named mydb.db in the C:\ directory.

CREATE DATABASE 'C:\\mydb.db'
TRANSACTION LOG ON
CASE IGNORE
PAGE SIZE 4096
ENCRYPTED OFF
BLANK PADDING OFF;

The following statement creates a database using code page 1252 and uses the UCA for both CHAR and NCHAR data types. Accents and case are respected during comparison and sorting.

CREATE DATABASE 'c:\\uca.db'
COLLATION 'UCA'
ENCODING 'CP1252'
NCHAR COLLATION 'UCA'
ACCENT RESPECT
CASE RESPECT;

The following statement creates a database, myencrypteddb.db, that is encrypted using simple encryption:

CREATE DATABASE 'myencrypteddb.db' 
ENCRYPTED ON;

The following statement creates a database, mystrongencryptdb.db, that is encrypted using the key gh67AB2 (strong encryption):

CREATE DATABASE 'mystrongencryptdb.db' 
ENCRYPTED ON KEY 'gh67AB2';

The following statement creates a database, mytableencryptdb.db, with table encryption enabled using simple encryption. Notice the keyword TABLE inserted after ENCRYPTED to indicate table encryption instead of database encryption:

CREATE DATABASE 'mytableencryptdb.db' 
ENCRYPTED TABLE ON;

The following statement creates a database, mystrongencrypttabledb.db, with table encryption enabled using the key gh67AB2 (strong encryption), and the AES_FIPS encryption algorithm:

CREATE DATABASE 'mystrongencrypttabledb.db' 
ENCRYPTED TABLE ON KEY 'gh67AB2' 
ALGORITHM 'AES_FIPS';

The following statement creates a database file named mydb.db that uses collation 1252LATIN1. The NCHAR collation is set to UCA, with the locale set to es, and has case sensitivity and accent sensitivity enabled:

CREATE DATABASE 'my2.db' 
      COLLATION '1252LATIN1(case=respect)' 
      NCHAR COLLATION 'UCA(locale=es;case=respect;accent=respect)';