CREATE DATABASE (SQL Server Compact Edition)

Creates a new database and the file used to store the database.

Syntax

CREATE DATABASE database_name 
   [DATABASEPASSWORD 'database_password' 
      [ENCRYPTION {ON|OFF}] 
   ]
   [COLLATE collation_name comparison_style] 
database password ::= identifier

Arguments

  • database_name
    The name of the new database. The database_name argument is a file name and is restricted by the naming and size limitations of the operating system. Any valid character that can be used in a file name can be used for the database name. If a path name is not specified, the database is created in the current directory. By default, the file name extension for a database name in Microsoft SQL Server 2005 Compact Edition (SQL Server Compact Edition) is .sdf.
  • 'database_password'
    SQL Server Compact Edition returns an error if a user connects to a password-protected database without a password. The database_password must be enclosed in single quotation marks.
  • ENCRYPTION
    If the optional ENCRYPTION ON keyword is used, the database is encrypted with 128-bit encryption. You must specify a password to use encryption.
  • collation_name
    Specifies the default collation for the database. The collation_name argument can only be a Windows collation. If collation_name is not specified, the database is assigned the default collation of the device. For a list of collation names, see COLLATE (SQL Server Compact Edition).
  • comparison_style
    Specifies the comparison style of characters. SQL Server Compact Edition supports only CI_AS (case insensitive and accent sensitive). The collation_name and comparison_style parameters must not be enclosed in single or double quotation marks.

Remarks

To execute the CREATE DATABASE statement, you must be connected to a database. For information about connecting to a database, see SQL Server Compact Edition Query Analyzer.

Code Example

A. Creating a password-protected database

The following example creates a database with a password.

CREATE DATABASE "\test1.SDF" DATABASEPASSWORD '<tst1_pwd>'

When a user tries to connect to a password-protected database without supplying the correct password, SQL Server Compact Edition returns this error:

Error: 0x80040e4d DB_SEC_E_AUTH_FAILED
Minor Error: (25028)
Description: Authentication failed.
Interface defining error: IID_IDBInitialize
Param. 0:0
Param. 1:0
Param. 2:0
Param. 3:
Param. 4:
Param. 5:

For information about errors, see SQL Server Compact Edition Errors.

B. Creating a database with a collation specified

The following example specifies a specific collation for the database being created.

CREATE DATABASE "SpanishDB.sdf" DATABASEPASSWORD '<MyPwd>' COLLATE Traditional_Spanish_CI_AS