Transact-SQL Syntax Conventions

Transact-SQL Reference

Transact-SQL Reference

Transact-SQL Syntax Conventions

The syntax diagrams in the Transact-SQL Reference use these conventions.

Convention Used for
UPPERCASE Transact-SQL keywords.
italic User-supplied parameters of Transact-SQL 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 portions 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>.

Unless specified otherwise, all Transact-SQL references to the name of a database object can be a four-part name in the form:

 [
    server_name.[database_name].[owner_name].
    
| database_name.[owner_name].
    | owner_name.
    ]
]
object_name

  • server_name specifies a linked server name or remote server name.

  • database_name specifies the name of a Microsoft® SQL Server™ database when the object resides in a SQL Server database. It specifies an OLE DB catalog when the object is in a linked server.

  • owner_name specifies the user that owns the object if the object is in a SQL Server database. It specifies an OLE DB schema name when the object is in a linked server.

  • object_name refers to the name of the object.

When referencing a specific object, you do not always have to specify the server, database, and owner for SQL Server to identify the object. Intermediate nodes can be omitted; use periods to indicate these positions. The valid formats of object names are:

server.database.owner.object

server.database..object

server..owner.object

server...object

database.owner.object

database..object

owner.object

object

Code Example Conventions

Unless stated otherwise, the examples were tested using SQL Query Analyzer and its default settings for these options:

  • QUOTED_IDENTIFIER

  • ANSI_NULLS

  • ANSI_WARNINGS

  • ANSI_PADDING

  • ANSI_NULL_DFLT_ON

  • CONCAT_NULL_YIELDS_NULL

Most code examples in the Transact-SQL Reference have been tested on servers running a case-sensitive sort order. The test servers were usually running the ANSI/ISO 1252 code page.

Transact-SQL Data Type Categories

Data types with similar characteristics are classified into categories. Categories that contain two or three data types generally have a category name derived from the data types in that category. For example, the money and smallmoney category contains the money data type and the smallmoney data type. Data type names always appear in bold, even when used as part of a category name.

Transact-SQL Data Type Hierarchy

The following data type hierarchy shows the SQL Server data type categories, subcategories, and data types used in the SQL Server documentation. For example, the exact numeric category contains three subcategories: integers, decimal, and money and smallmoney.

The exact numeric category also contains all of the data types in these three subcategories: bigint, int, smallint, tinyint, bit, decimal, money, and smallmoney. Any reference to exact numeric in the Transact-SQL Reference refers to these eight data types.

In this hierarchy the category names built from two or more data types use the conjunction "and." The conjunction "or" may be used in the Transact-SQL Reference if it is more appropriate for the context in which the name is used.

The data types specified in this hierarchy also pertain to synonyms. For example, int refers to both int and its synonym integer. For more information, see Data Types.

numeric
        exact numeric
                integer
                        bigint
                        int
                        smallint
                        tinyint
                bit
                decimal and numeric
                        decimal
                        numeric
                money and smallmoney
                        money
                        smallmoney
        approximate numeric
                float
                real
        datetime and smalldatetime
                datetime
                smalldatetime

character and binary string
        character string
                char, varchar, and text
                        char and varchar
                                char
                                varchar
                        text
                Unicode character string
                        nchar and nvarchar
                                nchar
                                nvarchar
                        ntext
        binary strings
                binary and varbinary
                        binary
                        varbinary
                image

cursor

sql_variant

table

timestamp

uniqueidentifier

Additional data type categories used in the Transact-SQL Reference are described in these two hierarchies:

text, ntext, and image
        text and ntext
                text
                ntext
        image

short string
        short character
                char and varchar
                        char
                        varchar
                nchar and nvarchar
                        nchar
                        nvarchar
        binary and varbinary
                binary
                varbinary