SQL Overview (SQL Server Compact)

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

Syntax Conventions

The syntax diagrams in this reference use the following conventions.

Convention

Used for

UPPERCASE

SQL Server Compact 4.0 keywords.

Italic

User-supplied parameters of SQL Server Compact 4.0 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 4.0.

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 4.0, 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 4.0.

Delimiter

Usage in SQL Server Compact 4.0

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 4.0 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