Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Reference » SQL Statements

CONTINUE statement [T-SQL] Next Page

CREATE DATABASE statement


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

Syntax

CREATE DATABASE db-file-name-string
[ 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-file-name-string ]
[ MIRROR mirror-file-name-string ] ] }

page-size :
2048 | 4096 | 8192 | 16384 | 32768

algorithm-key-spec:
ON
| [ ON ] KEY key [ ALGORITHM { 'AES' | 'AES_FIPS' } ]
| [ ON ] ALGORITHM { 'AES' | 'AES_FIPS' } KEY key
| [ ON ] ALGORITHM 'SIMPLE'

Parameters

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

ACCENT clause    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 é. This is the default behavior.

For more information, 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. Hence 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 or not 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    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.

By default, comparisons are case insensitive. 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 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 calculates checksums for critical pages.

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

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)'. If you specify the ACCENT or CASE clause as well as a collation tailoring string that contains settings for case and accent, the values of the ACCENT and CASE clauses are used as defaults only. Following is a table of the supported keywords, including their allowed alternate forms, and their allowed values.

If UCA is specified by itself, the default tailoring applied is equivalent to 'UCA(case=UpperFirst;accent=Respect;punct=Primary)'.

Note

All of the collation tailoring options below are supported when specifying the UCA collation. For all other collations, only case sensitivity tailoring is supported.

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

Collation tailoring options
KeywordCollationAlternate formsAllowed values
LocaleUCA(none)Any valid locale code. For example, en.
CaseSensitivityAll supported collationsCaseSensitive, Case
  • respect  Respect case differences between letters. For the UCA collation, this is equivalent to UpperFirst. For other collations, it depends on the collation itself.

  • ignore  Ignore case differences between letters.

  • UpperFirst   Always sort upper case first (Aa).

  • LowerFirst  Always sort lowercase first (aA).

AccentSensitivityUCAAccentSensitive, Accent
  • respect  Respect accent differences between letters.

  • ignore  Ignore accent differences between letters.

  • French  Respect accent sensitivity with French rules.

PunctuationSensitivityUCAPunctuationSensitive, Punct
  • ignore  Ignore differences in punctuation.

  • primary  Use first level sorting (consider letter, only). For example, a > b.

  • quaternary  Use fourth level sorting: consider letter first, then case, then accent, and then punctuation. For example, multiByte, multibyte, multi-byte, and multi-Byte, are sorted as:

    • multiByte
    • multibyte
    • multi-Byte
    • multi-byte
    You cannot specify quaternary with a case or accent insensitive database.

SortTypeUCA(none)

The type of sort to use. Possible values:

  • phonebook
  • traditional
  • standard
  • pinyin
  • stroke
  • direct
  • posix
  • big5han
  • gb2312han

For more information about these sort types, see Unicode Technical Standard #35, at [external link] http://www.unicode.org/reports/tr35/.

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. By default, the size is in bytes (this can also be specified with BYTES). You can use KB, MB, or GB to specify units of kilobytes, megabytes, or gigabytes, respectively. If you use P, the argument is a percentage of the total amount of space available. You can use PAGES to specify the size as the number of pages.

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 COLLATION is set to UCA and ENCODING is not specified, then SQL Anywhere uses UTF-8. For more information on the recommended encodings and collations, see Recommended character sets and collations.

For more information on 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 Encrypting tables.

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. For simple encryption, specify ENCRYPTED ON ALGORITHM SIMPLE, or ENCRYPTED ALGORITHM SIMPLE, or specify the ENCRYPTED ON clause without specifying an algorithm or key.

With strong encryption, the data is unreadable, and virtually undecipherable. 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. For strong encryption, you use the ALGORITHM clause to specify a 128-bit AES algorithm (either AES or AES_FIPS) and the KEY clause to specify an encryption key.

On Windows CE, the AES_FIPS algorithm is supported with ARM processors.

Caution    

Protect your key! Be sure to store a copy of your key in a safe location. A lost key will result in a completely inaccessible database, from which there is no recovery.

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

JCONNECT clause    To allow the Sybase 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.

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 as well as a collation tailoring string that contains settings for case and accent, the values of the ACCENT and CASE clauses are used as defaults only. The syntax for specifying these options is identical to the syntax defined for the COLLATION clause, above. See Collation tailoring options.

Note

All of the collation tailoring options are supported when specifying the UCA collation. 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.

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 (see The transaction log), 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 mirrored transaction log.

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
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 2048
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)'