SQL Overview (SQL Server Compact Edition)

You can use SQL grammar to query a database and to insert, update, and delete rows in tables in a Microsoft SQL Server 2005 Compact Edition (SQL Server Compact Edition) database.

Syntax Conventions

The syntax diagrams in this reference use the following conventions.

Convention Used for

UPPERCASE

SQL Server Compact Edition keywords.

Italic

User-supplied parameters of SQL Server Compact Edition syntax.

| (vertical bar)

Separating syntax items within brackets or braces. You can choose only one of the items.

[ ] (brackets)

Optional syntax items. Do not type the brackets.

{} (braces)

Required syntax items. Do not type the braces.

[,...n]

Indicating that the preceding item can be repeated n number of times. The occurrences are separated by commas.

[ ...n]

Indicating that the preceding item can be repeated n number of times. The occurrences are separated by blanks.

bold

Database names, table names, column names, index names, stored procedures, utilities, data type names, and text that must be typed exactly as shown.

<label> ::=

The name for a block of syntax. This convention is used to group and label sections of lengthy syntax or a unit of syntax that can be used in more than one place within a statement. Each location in which the block of syntax can be used is indicated with the label enclosed in chevrons: <label>.

Query Elements

You can use identifiers, delimiters, and comments when you query a database in SQL Server Compact Edition.

Identifiers

The name of a database object is known as an identifier. The object name is created when the object is defined. The identifier is used to reference the object.

With SQL Server Compact Edition, because you must connect to a database that already exists in the device before you can run queries, you do not have to qualify object identifiers with owner, database, or server names. You can reference objects in one of these ways:

  • object_name
  • table_name
Delimiters

You must delimit identifiers when they do not comply with the rules for the format of regular identifiers or when reserved keywords are used as identifiers. The following table summarizes the usage of delimiters in SQL Server Compact Edition.

Delimiter Usage in SQL Server Compact Edition

Brackets [ ]

Not supported

Double quotation marks " "

Noncompliant identifiers:

  • Multiple-word identifiers
  • Reserved words used as identifiers

Single quotation marks ' '

String data values

Comments

You can include text strings that should not be executed in the code as comments. Comments can be used to document code or to temporarily disable certain parts of a SQL statement.

You can delineate text as comments using either of the following styles:

  • /*text_of_comment*/
  • ----text_of_comment

Default Behavior

Queries executed through SQL Server Compact Edition Query Analyzer run with the following default behavior, which cannot be changed:

  • QUOTED_IDENTIFIER_ON
  • ANSI_NULLS_ON
  • ANSI_PADDING_ON
  • ANSI_NULL_DFLT_ON_ON
  • CONCAT_NULL_YIELDS_NULL_ON

For more information, see SQL Server Compact Edition Query Analyzer.