References for Transact-SQL Syntax
Introduction
This article aims to show that despite the flexibility in the construction of Transact-SQL statements that we have through SSMS (SQL Server Management Studio) or from the command line sqlcmd, there is a determined effort to write the syntax of each statements in the single SQL Server default.
It's a best practice to standardize the available objects in your database as indicated, aiming to facilitate the understanding of it's script SQL and subsequent maintenance, to anyone responsible for this update.
There are also terms used exclusively in the SQL Server to define, manipulate or access the data and objects in a database data, these terms are known as Reserved Keywords.
Used Convention
The Reference of statements Transact-SQL has been homologated inservers running a rating differentiation, using the ANSI/ISO 1252 code.
In cases of use of the string Unicode, T-SQL script includes a constant prefix represented by letter N. Without this prefix, the string can be converted to the default code page of the database data and some characters will not be recognized.
This convention is used initially for documentation and diagramming of objects, but it is recommended that this convention also applied, when possible, in the construction of your T-SQL statements.
The following describes some of conventions to be used in the syntax:
Convention |
Used in |
UPPERCASE |
Transact-SQL Keyword Reserved. |
[ ] (brackets) |
Item optional syntax. Don't type the brackets if not needed. |
; |
Terminator Transact-SQL. Although the signal semicolon is not mandatory in most statements in this release of SQL Server, will need it in a future release. |
<label>:: = |
The name of a syntax of block.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 location within a statement. Each location where the block of syntax can be used is indicated with the label between chevrons: <label>. A set is a collection of expressions (for example, <Set Cluster>) and a list is a collection of sets (for example, <list of elements composed>). |
Object Names
The name of an object can be composed of up to 4 parts as follows:
server_name.[database_name].[schema_name].object_name
It's not always necessary to specify all the reference of a nameobject for that to be found, but if the object is not located SQL Server returns an error indicating that the object does not was found.
To avoid syntax errors, especially when a database has more than one schema, it's recommended that it be indicated in the name of the object under the schema to which it belongs, as indicated below:
schema_name.object_name
Reserved Keywords
The Reserved Keywords are used for processing instructions of SQL Server. Consider avoiding the use of these Reserved Keywords in any object or as identifiers to optimize for your T-SQL statement performance. The development team of SQL Server always keeps these updated lists.
These words are often used as functions or stored procedures of system and extended.
There are three lists determined Reserved Keywords for SQL Server, two of these lists are already in use and another list is available to prepare your objects and their T-SQL statements for future migrations version of SQL Server. These lists of Reserved Keywords are: T-SQL, ISO/ODBC and T-SQL Future.
T-SQL Reserved Keywords
The Reserved Keywords are part of the grammar in the Transact-SQL to analyze and understand the instructions and their batches.
SQL Server allows the use of these words as identifiers and object names in T-SQL scripts, but it is necessary to make the appropriate treatment to avoid errors or delays in the processing of statement. To learn more about what may occur and how to fix these objects see the section Known Errors.
In the future, other keywords can be added to this list, foravoid processing problems or performance of your application is recommended careful to avoid the use these new words too. See these keywords in the list Future Reserved Keywords in T-SQL.
The table below shows the Reserved Keywords by Transact-SQL to the current version of SQL Server (in alphabetical order):
| ADD |
| ALL |
| ALTER |
| AND |
| ANY |
| AS |
| ASC |
| AUTHORIZATION |
| BACKUP |
| BEGIN |
| BETWEEN |
| BREAK |
| BROWSE |
| BULK |
| BY |
| CASCADE |
| CASE |
| CHECK |
| CHECKPOINT |
| CLOSE |
| CLUSTERED |
| COALESCE |
| COLLATE |
| COLUMN |
| COMMIT |
| COMPUTE |
| CONSTRAINT |
| CONTAINS |
| CONTAINSTABLE |
| CONTINUE |
| CONVERT |
| CREATE |
| CROSS |
| CURRENT |
| CURRENT_DATE |
| CURRENT_TIME |
| CURRENT_TIMESTAMP |
| CURRENT_USER |
| CURSOR |
| DATABASE |
| DBCC |
| DEALLOCATE |
| DECLARE |
| DEFAULT |
| DELETE |
| DENY |
| DESC |
| DISK |
| DISTINCT |
| DISTRIBUTED |
| DOUBLE |
| DROP |
| DUMP |
| ELSE |
| END |
| ERRLVL |
| ESCAPE |
| EXCEPT |
| EXEC |
| EXECUTE |
| EXISTS |
| EXIT |
| EXTERNAL |
| FETCH |
| FILE |
| FILLFACTOR |
| FOR |
| FOREIGN |
| FREETEXT |
| FREETEXTTABLE |
| FROM |
| FULL |
| FUNÇÃO |
| GOTO |
| GRANT |
| GROUP |
| HAVING |
| HOLDLOCK |
| IDENTITY |
| IDENTITY_INSERT |
| IDENTITYCOL |
| IF |
| IN |
| INDEX |
| INNER |
| INSERT |
| INTERSECT |
| INTO |
| IS |
| JOIN |
| KEY |
| KILL |
| LEFT |
| LIKE |
| LINENO |
| LOAD |
| MERGE |
| NATIONAL |
| NOCHECK |
| NONCLUSTERED |
| NOT |
| NULL |
| NULLIF |
| OF |
| OFF |
| OFFSETS |
| ON |
| OPEN |
| OPENDATASOURCE |
| OPENQUERY |
| OPENROWSET |
| OPENXML |
| OPTION |
| OR |
| ORDER |
| OUTER |
| OVER |
| PERCENT |
| PIVOT |
| PLAN |
| PRECISION |
| PRIMARY |
| PROC |
| PROCEDURE |
| PUBLIC |
| RAISERROR |
| READ |
| READTEXT |
| RECONFIGURE |
| REFERENCES |
| REPLICATION |
| RESTORE |
| RESTRICT |
| RETURN |
| REVERT |
| REVOKE |
| RIGHT |
| ROLLBACK |
| ROWCOUNT |
| ROWGUIDCOL |
| RULE |
| SAVE |
| SCHEMA |
| SECURITYAUDIT |
| SELECT |
| SEMANTICKEYPHRASETABLE |
| SEMANTICSIMILARITYDETAILSTABLE |
| SEMANTICSIMILARITYTABLE |
| SESSION_USER |
| SET |
| SETUSER |
| SHUTDOWN |
| SOME |
| STATISTICS |
| SYSTEM_USER |
| TABLE |
| TABLESAMPLE |
| TEXTSIZE |
| THEN |
| TO |
| TOP |
| TRAN |
| TRANSACTION |
| TRIGGER |
| TRUNCATE |
| TRY_CONVERT |
| TSEQUAL |
| UNION |
| UNIQUE |
| UNPIVOT |
| UPDATE |
| UPDATETEXT |
| USE |
| USER |
| VALUES |
| VARYING |
| VIEW |
| WAITFOR |
| WHEN |
| WHERE |
| WHILE |
| WITH |
| WITHIN GROUP |
| WRITETEXT |
ISO/ODBC Reserved Keywords
The list of ISO standard Reserved Keywords is the same as ODBC. In some cases, this list is more restrictive than the list shown in T-SQL Reserved Keywords.
The table below shows the ISO Reserved Keywords in the list (in alphabetical order) :
| ABSOLUTE |
| ACTION |
| ADA |
| ADD |
| ALL |
| ALLOCATE |
| ALTER |
| AND |
| ANY |
| ARE |
| AS |
| ASC |
| ASSERTION |
| AT |
| AUTHORIZATION |
| AVG |
| BEGIN |
| BETWEEN |
| BIT |
| BIT_LENGTH |
| BOTH |
| BY |
| CASCADE |
| CASCADED |
| CASE |
| CAST |
| CATALOG |
| CHAR |
| CHAR_LENGTH |
| CHARACTER |
| CHARACTER_LENGTH |
| CHECK |
| CLOSE |
| COALESCE |
| COLLATE |
| COLLATION |
| COLUMN |
| COMMIT |
| CONNECT |
| CONNECTION |
| CONSTRAINT |
| CONSTRAINTS |
| CONTINUE |
| CONVERT |
| CORRESPONDING |
| COUNT |
| CREATE |
| CROSS |
| CURRENT |
| CURRENT_DATE |
| CURRENT_TIME |
| CURRENT_TIMESTAMP |
| CURRENT_USER |
| CURSOR |
| DATE |
| DAY |
| DEALLOCATE |
| DEC |
| DECIMAL |
| DECLARE |
| DEFAULT |
| DEFERRABLE |
| DEFERRED |
| DELETE |
| DESC |
| DESCRIBE |
| DESCRIPTOR |
| DIAGNOSTICS |
| DISCONNECT |
| DISTINCT |
| DOMAIN |
| DOUBLE |
| DROP |
| ELSE |
| END |
| END-EXEC |
| ESCAPE |
| EXCEPT |
| EXCEPTION |
| EXEC |
| EXECUTE |
| EXISTS |
| EXTERNAL |
| EXTRACT |
| FALSE |
| FETCH |
| FIRST |
| FLOAT |
| FOR |
| FOREIGN |
| FORTRAN |
| FOUND |
| FROM |
| FULL |
| GET |
| GLOBAL |
| GO |
| GOTO |
| GRANT |
| GROUP |
| HAVING |
| HOUR |
| IDENTITY |
| IMMEDIATE |
| IN |
| INCLUDE |
| INDEX |
| INDICATOR |
| INITIALLY |
| INNER |
| INPUT |
| INSENSITIVE |
| INSERT |
| INT |
| INTEGER |
| INTERSECT |
| INTERVAL |
| INTO |
| IS |
| ISOLATION |
| JOIN |
| KEY |
| LANGUAGE |
| LAST |
| LEADING |
| LEFT |
| LEVEL |
| LIKE |
| LOCAL |
| LOWER |
| MATCH |
| MAX |
| MIN |
| MINUTE |
| MODULE |
| MONTH |
| NAMES |
| NATIONAL |
| NATURAL |
| NCHAR |
| NEXT |
| NO |
| NONE |
| NOT |
| NULL |
| NULLIF |
| NUMERIC |
| OCTET_LENGTH |
| OF |
| ON |
| ONLY |
| OPEN |
| OPTION |
| OR |
| ORDER |
| OUTER |
| OUTPUT |
| OVERLAPS |
| PAD |
| PARTIAL |
| PASCAL |
| POSITION |
| PRECISION |
| PREPARE |
| PRESERVE |
| PRIMARY |
| PRIOR |
| PRIVILEGES |
| PROCEDURE |
| PUBLIC |
| READ |
| REAL |
| REFERENCES |
| RELATIVE |
| RESTRICT |
| REVOKE |
| RIGHT |
| ROLLBACK |
| ROWS |
| SCHEMA |
| SCROLL |
| SECOND |
| SECTION |
| SELECT |
| SESSION |
| SESSION_USER |
| SET |
| SIZE |
| SMALLINT |
| SOME |
| SPACE |
| SQL |
| SQLCA |
| SQLCODE |
| SQLERROR |
| SQLSTATE |
| SQLWARNING |
| SUBSTRING |
| SUM |
| SYSTEM_USER |
| TABLE |
| TEMPORARY |
| THEN |
| TIME |
| TIMESTAMP |
| TIMEZONE_HOUR |
| TIMEZONE_MINUTE |
| TO |
| TRAILING |
| TRANSACTION |
| TRANSLATE |
| TRANSLATION |
| TRIM |
| TRUE |
| UNION |
| UNIQUE |
| UNKNOWN |
| UPDATE |
| UPPER |
| USAGE |
| USER |
| USING |
| VALUE |
| VALUES |
| VARCHAR |
| VARYING |
| VIEW |
| WHEN |
| WHENEVER |
| WHERE |
| WITH |
| WORK |
| WRITE |
| YEAR |
| ZONE |
T-SQL Future Reserved Keywords
The list of T-SQL Future Reserved Keywords seek to prepare all that uses SQL Server to adapt SQL Server objects and T-SQL statements to avoid use any of these words, keeping it's contents according to Best Practices in SQL Server.
The table below shows the reserved words that may in the future become Reserved Keywords for use of T-SQL (in alphabetical order):
| ABSOLUTE |
| ACTION |
| ADMIN |
| AFTER |
| AGGREGATE |
| ALIAS |
| ALLOCATE |
| ARE |
| ARRAY |
| ASENSITIVE |
| ASSERTION |
| ASYMMETRIC |
| AT |
| ATOMIC |
| BEFORE |
| BINARY |
| BIT |
| BLOB |
| BOOLEAN |
| BOTH |
| BREADTH |
| CALL |
| CALLED |
| CARDINALITY |
| CASCADED |
| CAST |
| CATALOG |
| CHAR |
| CHARACTER |
| CLASS |
| CLOB |
| COLLATION |
| COLLECT |
| COMPLETION |
| CONDITION |
| CONNECT |
| CONNECTION |
| CONSTRAINTS |
| CONSTRUCTOR |
| CORR |
| CORRESPONDING |
| COVAR_POP |
| COVAR_SAMP |
| CUBE |
| CUME_DIST |
| CURRENT_CATALOG |
| CURRENT_DEFAULT_TRANSFORM_GROUP |
| CURRENT_PATH |
| CURRENT_ROLE |
| CURRENT_SCHEMA |
| CURRENT_TRANSFORM_GROUP_FOR_TYPE |
| CYCLE |
| DATA |
| DATE |
| DAY |
| DEC |
| DECIMAL |
| DEFERRABLE |
| DEFERRED |
| DEPTH |
| DEREF |
| DESCRIBE |
| DESCRIPTOR |
| DESTROY |
| DESTRUCTOR |
| DETERMINISTIC |
| DIAGNOSTICS |
| DICTIONARY |
| DISCONNECT |
| DOMAIN |
| DYNAMIC |
| EACH |
| ELEMENT |
| END-EXEC |
| EQUALS |
| EVERY |
| EXCEPTION |
| FALSE |
| FILTER |
| FIRST |
| FLOAT |
| FOUND |
| FREE |
| FULLTEXTTABLE |
| FUSION |
| GENERAL |
| GET |
| GLOBAL |
| GO |
| GROUPING |
| HOLD |
| HOST |
| HOUR |
| IGNORE |
| IMMEDIATE |
| INDICATOR |
| INITIALIZE |
| INITIALLY |
| INOUT |
| INPUT |
| INT |
| INTEGER |
| INTERSECTION |
| INTERVAL |
| ISOLATION |
| ITERATE |
| LANGUAGE |
| LARGE |
| LAST |
| LATERAL |
| LEADING |
| LESS |
| LEVEL |
| LIKE_REGEX |
| LIMIT |
| LN |
| LOCAL |
| LOCALTIME |
| LOCALTIMESTAMP |
| LOCATOR |
| MAP |
| MATCH |
| MEMBER |
| METHOD |
| MINUTE |
| MOD |
| MODIFIES |
| MODIFY |
| MODULE |
| MONTH |
| MULTISET |
| NAMES |
| NATURAL |
| NCHAR |
| NCLOB |
| NEW |
| NEXT |
| NO |
| NONE |
| NORMALIZE |
| NUMERIC |
| OBJETO |
| OCCURRENCES_REGEX |
| OLD |
| ONLY |
| OPERATION |
| ORDINALITY |
| OUT |
| OUTPUT |
| OVERLAY |
| PAD |
| PARAMETER |
| PARAMETERS |
| PARTIAL |
| PARTITION |
| PATH |
| PERCENT_RANK |
| PERCENTILE_CONT |
| PERCENTILE_DISC |
| POSITION_REGEX |
| POSTFIX |
| PREFIX |
| PREORDER |
| PREPARE |
| PRESERVE |
| PRIOR |
| PRIVILEGES |
| RANGE |
| READS |
| REAL |
| RECURSIVE |
| REF |
| REFERENCING |
| REGR_AVGX |
| REGR_AVGY |
| REGR_COUNT |
| REGR_INTERCEPT |
| REGR_R2 |
| REGR_SLOPE |
| REGR_SXX |
| REGR_SXY |
| REGR_SYY |
| RELATIVE |
| RELEASE |
| RESULT |
| RETURNS |
| ROLE |
| ROLLUP |
| ROUTINE |
| ROW |
| ROWS |
| SAVEPOINT |
| SCOPE |
| SCROLL |
| SEARCH |
| SECOND |
| SECTION |
| SENSITIVE |
| SEQUENCE |
| SESSION |
| SETS |
| SIMILAR |
| SIZE |
| SMALLINT |
| SPACE |
| SPECIFIC |
| SPECIFICTYPE |
| SQL |
| SQLEXCEPTION |
| SQLSTATE |
| SQLWARNING |
| START |
| STATE |
| STATEMENT |
| STATIC |
| STDDEV_POP |
| STDDEV_SAMP |
| STRUCTURE |
| SUBMULTISET |
| SUBSTRING_REGEX |
| SYMMETRIC |
| SYSTEM |
| TEMPORARY |
| TERMINATE |
| THAN |
| TIME |
| TIMESTAMP |
| TIMEZONE_HOUR |
| TIMEZONE_MINUTE |
| TRAILING |
| TRANSLATE_REGEX |
| TRANSLATION |
| TREAT |
| TRUE |
| UESCAPE |
Known Errors
As indicated in section T-SQL Reserved Keyword, Best Practice is not to use reserved keyword for SQL Server objects, such as the names of fields in tables.
In this case, when you cannot change the structure of table, changing the name of this field for other than Reserved Keyword, it's recommended that the name of the field is between brackets, as in the example below:
schema_name.table_name.[field_name]
Additional Information
This article is constantly evolving, feel free to include additional information on new contents or other problem in relation to misuse of conventions or reserved keyword. If possible, highlight the solution used.
See Also
- Transact-SQL Syntax Conventions
- Reserved Keywords (Transact-SQL)
- Expressions (Transact-SQL)
- Built-in Functions (Transact-SQL)
Another important place to find a huge amount of articles related to Transact-SQL is TechNet Wiki itself.
The best entry point can be found in:
Other Languages
You can also find this article in the following language: