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