SQL Syntax Separator Understanding summary _mssql

The main difference is that delimited identifiers are enclosed in double quotes (Transact-SQL also supports the use of square brackets: [identifiers]) and is case-sensitive. Single quotes are used only for the delimitation of strings. In general, delimited identifiers are introduced to standardize identifiers, otherwise they will be the same as reserved words. Specifically, a delimited identifier allows you to avoid using reserved words that may appear in future SQL standards when naming (identifiers or variable names). In addition, delimited identifiers can contain characters that are considered illegal in the usual identifier names, such as spaces.
In SQL Server, the use of double quotes is defined by the QUOTED_IDENTIFIER option in the SET statement. If this option is set to ON, the identifiers in double quotes will be defined as a delimited identifier. In this case, double quotes cannot be used for bounding strings. ”
Remember the following words to understand the separator
This passage is the exact words in the book, and my understanding of the delimited identifiers is: "Hu", "H u", [Hu],
[h u] These identifiers are different and are used in the same way as using identifiers like A,b, except that they must be enclosed in double quotes or brackets.
My own understanding of the separator: separator is to play the role of separation. SELECT * from ' my Table '
WHERE "last Name" = ' O ' Brien '
Self-analysis: Select,from,where is the key word. "My Table" is a marker, and "" is the delimiter
Please advise the prawns.
Well, you know, you're pretty right.
But don't forget its role is to define identifiers.
Identifiers that conform to all identifier format rules can use delimiters or do not use delimiters.
Identifiers that do not conform to the identifier format rule must use delimiters.
Delimited identifiers are used in the following situations:
When a reserved word is used in an object name or part of an object name.
It is recommended that you do not use reserved keywords as object names. Databases upgraded from earlier versions of Microsoft®sql Server™ may contain identifiers that include words that were not retained in earlier versions and reserved in SQL Server 2000. A delimited identifier can be used to refer to an object until its name is changed.
When using a word characters that is not listed as a legal identifier.
SQL Server allows any character in the current code page to be used in a delimited identifier. However, the indiscriminate use of special characters in object names makes SQL statements and scripts difficult to read and maintain.
Type of delimiter used by Transact-SQL:
The description separator is used only for identifiers. Delimiters cannot be used for keywords, regardless of whether they are marked as reserved words in SQL Server.
Quoted identifiers are separated by double quotation marks ("):
SELECT * from ' blanks in Table Name '
Identifiers enclosed in parentheses are delimited by square brackets ([]):
SELECT * FROM [blanks in Table Name]
The referenced identifier is valid only if the QUOTED_IDENTIFIER option is set to ON. By default, QUOTED_IDENTIFIER is set to ON when the Microsoft OLE DB provider for SQL Server connects to the SQL Server ODBC driver. By default, Db-library does not set QUOTED_IDENTIFIER to ON. Regardless of the interface you use, individual applications or users can change settings at any time. SQL Server provides several ways to specify this option. For example, in SQL Server Enterprise Manager and SQL Query Analyzer, this option can be set in the dialog. In Transact-SQL, this option can be set to multiple levels using the QUOTED IDENTIFIER option of SET QUOTED_IDENTIFIER, sp_dboption, or the user options option for sp_configure.
When QUOTED_IDENTIFIER is on, SQL Server follows the SQL-92 rule for the use of double quotes and single quotes (') in SQL statements:
Double quotes can only be used for delimited identifiers and cannot be used to delimit strings.
To maintain compatibility with existing applications, SQL Server does not fully enforce the rule. If the string does not exceed the length of the identifier, the string can be enclosed in double quotes. But this is not recommended.
Single quotes must be used to contain strings and cannot be used to delimit identifiers.
If the string contains single quotes, you need to add a single quote before the single quotation mark:
SELECT * from ' my Table '
WHERE "last Name" = ' O ' Brien '
When QUOTED_IDENTIFIER is off, SQL Server follows the following rules for the use of double quotes and single quotes:
Quotation marks cannot be used to delimit identifiers, but are parentheses as delimiters.
Single or double quotes can be used to contain strings.
If you use double quotes, embedded single quotes do not need to be represented by two single quotes:
SELECT * FROM [my Table]
WHERE [last Name] = "O ' Brien"
You can use delimiters in parentheses, regardless of the QUOTED_IDENTIFIER settings.
Delimited identifier rule
The formatting rules for delimited identifiers are:
Delimited identifiers can contain the same number of characters as regular identifiers (1 to 128, excluding separator characters). A local temporary table identifier can contain up to 116 characters.
The body of an identifier can contain any combination of letters within the current code page (except the separator itself). For example, a separator identifier can contain spaces, any character that is valid for a regular identifier, and any of the following characters: tilde (~) hyphen (-)
Exclamation point (!) opening parenthesis ({)
Percent semicolon (%) Right bracket (})
Insert Number (^) apostrophe (')
and sign (&) period (.)
Left parenthesis (() backslash (\)
Right parenthesis ()) Accent sign (')