Skip to content

transact-sql-syntax-conventions-transact-sql.md

Latest commit

 

History

History
122 lines (96 loc) · 8.14 KB

transact-sql-syntax-conventions-transact-sql.md

File metadata and controls

122 lines (96 loc) · 8.14 KB
title Transact-SQL Syntax Conventions (Transact-SQL) | Microsoft Docs
ms.custom
ms.date 03/09/2018
ms.prod sql
ms.prod_service database-engine, sql-database, sql-data-warehouse, pdw
ms.reviewer
ms.technology t-sql
ms.topic language-reference
f1_keywords
sql13.TSQLExpandPortal.f1
dev_langs
TSQL
helpviewer_keywords
conventions [SQL Server]
Applies to section in Transact-SQL topics
code example conventions [SQL Server]
objects [SQL Server], names
code [SQL Server], conventions
multipart names [SQL Server]
Transact-SQL syntax conventions
syntax conventions [SQL Server]
code [SQL Server]
Transact-SQL
naming conventions [SQL Server]
syntax [SQL Server], Transact-SQL
ms.assetid 35fbcf7f-8b55-46cd-a957-9b8c7b311241
author douglaslMS
ms.author douglasl
manager craigg
monikerRange >=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||=sqlallproducts-allversions||>=sql-server-linux-2017||=azuresqldb-mi-current

Transact-SQL Syntax Conventions (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-all_md]

The following table lists and describes conventions that are used in the syntax diagrams in the [!INCLUDEtsql] Reference.

Convention Used for
UPPERCASE [!INCLUDEtsql] keywords.
italic User-supplied parameters of [!INCLUDEtsql] syntax.
bold Type database names, table names, column names, index names, stored procedures, utilities, data type names, and text exactly as shown.
underline Indicates the default value applied when the clause that contains the underlined value is omitted from the statement.
| (vertical bar) Separates syntax items enclosed in brackets or braces. You can use only one of the items.
[ ] (brackets) Optional syntax items. Don't type the brackets.
{ } (braces) Required syntax items. Don't type the braces.
[,...n] Indicates the preceding item can be repeated n number of times. The occurrences are separated by commas.
[...n] Indicates the preceding item can be repeated n number of times. The occurrences are separated by blanks.
; [!INCLUDEtsql] statement terminator. Although the semicolon isn't required for most statements in this version of [!INCLUDEssNoVersion], it will be required in a future version.
<label> ::= The name for a block of syntax. Use this convention to group and label sections of lengthy syntax or a unit of syntax that you can use in more than one location within a statement. Each location in which the block of syntax could be used is indicated with the label enclosed in chevrons: <label>.

A set is a collection of expressions, for example <grouping set>; and a list is a collection of sets, for example <composite element list>.

Multipart Names

Unless specified otherwise, all [!INCLUDEtsql] references to the name of a database object can be a four-part name in the following form:

server_name.[database_name].[schema_name].object_name

| database_name.[schema_name].object_name

| schema_name.object_name

| object_name

server_name
Specifies a linked server name or remote server name.

database_name
Specifies the name of a [!INCLUDEssNoVersion] database when the object resides in a local instance of [!INCLUDEssNoVersion]. When the object is in a linked server, database_name specifies an OLE DB catalog.

schema_name
Specifies the name of the schema that contains the object if the object is in a [!INCLUDEssNoVersion] database. When the object is in a linked server, schema_name specifies an OLE DB schema name.

object_name
Refers to the name of the object.

When referencing a specific object, you don't always have to specify the server, database, and schema for the [!INCLUDEssDEnoversion] to identify the object. However, if the object can't be found, an error is returned.

Note

To avoid name resolution errors, we recommend specifying the schema name whenever you specify a schema-scoped object.

To omit intermediate nodes, use periods to indicate these positions. The following table shows the valid formats of object names.

Object reference format Description
server.database.schema.object Four-part name.
server.database..object Schema name is omitted.
server..schema.object Database name is omitted.
server...object Database and schema name are omitted.
database.schema.object Server name is omitted.
database..object Server and schema name are omitted.
schema.object Server and database name are omitted.
object Server, database, and schema name are omitted.

Code Example Conventions

Unless stated otherwise, the examples provided in the [!INCLUDEtsql] Reference were tested by using [!INCLUDEssManStudioFull] and its default settings for the following options:

  • ANSI_NULLS
  • ANSI_NULL_DFLT_ON
  • ANSI_PADDING
  • ANSI_WARNINGS
  • CONCAT_NULL_YIELDS_NULL
  • QUOTED_IDENTIFIER

Most code examples in the [!INCLUDEtsql] Reference have been tested on servers that are running a case-sensitive sort order. The test servers were typically running the ANSI/ISO 1252 code page.

Many code examples prefix Unicode character string constants with the letter N. Without the N prefix, the string is converted to the default code page of the database. This default code page may not recognize certain characters.

"Applies to" References

The [!INCLUDEtsql] reference includes articles related to [!INCLUDEssNoVersion] ([!INCLUDEssKatmai] through [!INCLUDEssCurrent]), [!INCLUDEssSDSfull], and [!INCLUDEssSDWfull].

There's a section near the top of each article indicating which products support the article's subject. If a product is omitted, then the feature described by the article isn't available in that product. For example, availability groups were introduced in [!INCLUDEssSQL11]. The CREATE AVAILABILITY GROUP article indicates it applies to [!INCLUDEssNoVersion] ([!INCLUDEssSQL11] through [!INCLUDEssCurrent]) because it doesn't apply to [!INCLUDEssKatmai], [!INCLUDEssKilimanjaro], or [!INCLUDEssSDSfull].

The general subject of the article might be used in a product, but all of the arguments aren't supported in some cases. For example, contained database users were introduced in [!INCLUDEssSQL11]. Use the CREATE USER statement in any [!INCLUDEssNoVersion] product, however the WITH PASSWORD syntax can't be used with older versions. Additional Applies to sections are inserted into the appropriate argument descriptions in the body of the article.

See Also

Transact-SQL Reference (Database Engine)
Reserved Keywords (Transact SQL)
Transact-SQL Design Issues
Transact-SQL Naming Issues
Transact-SQL Performance Issues