CREATE DATABASE statement

• CREATE DATABASE

  Each of db-filename-string, log-filename-string, and mirror-filename-string consists of an optional path followed by the name of a file. 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.

    Here are some examples where this is important.

    CREATE DATABASE 'c:\\temp\\\x41\x42\x43xyz.db' 
    DBA USER 'DBA' DBA PASSWORD 'passwd';

    The initial \\ sequence represents a backslash. The \x sequences represent the characters A, B, and C, respectively. The file name here is ABCxyz.db.

    CREATE DATABASE 'c:\temp\\nest.db' 
    DBA USER 'DBA' DBA PASSWORD 'passwd';

    To avoid having the \n sequence interpreted as a newline character, the backslash is doubled.

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

    CREATE DATABASE 'c:\\my_db.db' 
    DBA USER 'DBA' DBA PASSWORD 'passwd'
    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. 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.

  • The directory path is relative to the database server.

  You cannot specify utility_db for db-filename-string. This name is reserved for 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, the default accent sensitivity for comparisons and sorting is insensitive, with one exception; for Japanese databases created with a UCA collation, the default accent sensitivity is sensitive.

• 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 Adaptive Server Enterprise compatibility views dbo.syscolumns and dbo.sysindexes.

• BLANK PADDING clause

  The database server 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, the database server never trims or pads values with trailing blanks when the values are stored in the database.

  By default, the database server 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, the database server 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.

• 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.

• CHECKSUM clause

  Checksums are used to determine whether a database page has been modified on disk. When you create a database with global 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 global checksums enabled can also be validated using checksums. You can check whether a database was created with global checksums enabled by executing the following statement:

  SELECT DB_PROPERTY ( 'Checksum' );

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

  Regardless of the setting of this clause, the database server always enables write checksums for databases running on storage devices such as removable drives, to help provide early detection if the database file becomes corrupt. The database server also calculates checksums for critical pages during validation activities.

  For databases that do not have global checksums enabled, you can enable write checksums by using the -wc options.

• 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, the database server 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 (SACA), or it can be the Unicode Collation Algorithm (UCA). If UCA is specified, also specify the ENCODING clause.

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

  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

  Use this optional clause to set the initial size of the database file. You can use KB, MB, GB, or PAGES to specify units of kilobytes, megabytes, gigabytes, or pages respectively.

  Specifying the file size at creation time is a way of preallocating space for the file. This 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.

• DBA USER and DBA PASSWORD clauses

  Use these clauses to specify a DBA user ID and password for the database.

  By default, passwords must be a minimum length of 6 characters unless the MINIMUM PASSWORD LENGTH clause is specified and set to a different value. Passwords should be composed of 7-bit ASCII characters. Other characters may not work correctly if the server cannot convert from the client character set to UTF-8.

• 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.

  If COLLATION is set to UCA and ENCODING is not specified, then the database server uses UTF-8.

• ENCRYPTED or ENCRYPTED TABLE clause

  Encryption makes stored data undecipherable. 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.

  There are two levels of database and table encoding: simple obfuscation and strong encryption. Obfuscation is not encryption, and someone with cryptographic expertise could decipher the data. Strong encryption ensures that the data is unreadable and virtually undecipherable.

  For simple obfuscation, 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. 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. A key can be specified as either a string or a variable name.

  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.

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

• JCONNECT clause

  To allow the jConnect JDBC driver access to system catalog information, specify JCONNECT ON. This clause installs the system objects that provide jConnect support. Specify JCONNECT OFF 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.

• MINIMUM PASSWORD LENGTH clause

  Use this clause to set the minimum password length. If this clause is not specified, then the default minimum password length for a new database is 6.

• 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. The 2048 page size is deprecated. Large databases generally obtain performance benefits from a larger page size, but there can be additional overhead associated with the large page sizes.

  For example:

  CREATE DATABASE 'c:\\temp\\my_db.db' 
  DBA USER 'DBA' DBA PASSWORD 'passwd'
  PAGE SIZE 4096;

  Note 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.
• NCHAR COLLATION clause

  The collation specified by the NCHAR COLLATION clause is used for sorting and comparing 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, the database server 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.

  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.

  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.

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

• SYSTEM PROCEDURE AS DEFINER { ON | OFF } clause

  The SYSTEM PROCEDURE AS DEFINER clause specifies whether to execute pre-16.0 system procedures that perform privileged tasks with the privileges of the invoker or the definer (owner). ON means that these system procedures are executed with the privileges of the definer (owner). OFF means these system procedures are executed with the privileges of the invoker.

  If this clause is not specified, the default is to run these procedures with the privileges of the invoker.

  This setting does not impact user-defined procedures, or any system procedures introduced in version 16.0 or later.

• [ 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 default is LOG ON.

  The MIRROR option of the LOG clause allows you to provide a file name if you are using 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, the database server does not use a transaction log mirror.

Creates a database file with the supplied name and attributes. The database is stored as an operating system file. This statement is not supported in procedures, triggers, events, or batches.

You must be connected to a database to create another database. For example, connect to the utility database.

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

Messages sent to the client indicate what type of database encryption is used for the database. If encryption is used, the algorithm being used is also displayed.

Your ability to execute this statement depends on the setting for the -gu database option, and whether you have the SERVER OPERATOR system privilege.

An operating system file is created.

• ANSI/ISO SQL Standard

  Not in the standard.

• Transact-SQL

  The CREATE DATABASE statement is supported by Adaptive Server Enterprise, though with different clauses.