SQL syntax delimiter

The main difference between the two is that the separated identifiers are enclosed in double quotation marks (Transact-SQL also supports the use of square brackets: [identifiers]) and are case sensitive. Single quotes are only used for string boundary. In general, the Delimiter is introduced to regulate the identifier, otherwise it will be the same as the reserved word. In particular, separated identifiers prevent you from using reserved words that may appear in future SQL standards when naming (identifiers or variable names. In addition, the separated identifiers can contain characters, such as spaces, that are generally considered invalid in the names of identifiers.
In SQL SERVER, double quotation marks are defined by the QUOTED_IDENTIFIER option in the SET statement. If this option is set to ON, the identifier in double quotation marks will be defined as a separate identifier. In this case, double quotation marks cannot be used to define strings ."
Remember the following sentence to understand the delimiter:
These are the original words in the book. My Understanding of the separated identifiers is: for example, "hu", "h u", [hu],
[H u] These identifiers are different. When using them, they are like using identifiers such as a and B, but they must be enclosed in double quotation marks or brackets.
My own understanding of delimiters: delimiters are used for separation. SELECT * FROM "My Table"
WHERE "Last Name" = 'O' brien'
Self-analysis: SELECT, FROM, where is the keyword. "My Table" is the identifier, and "" is the Separator
Please advise me.
Actually, you are quite right.
But don't forget that it serves to define the identifier.
Identifiers that comply with the rules of all identifiers can use or do not use delimiters.
Delimiters are required for identifiers that do not conform to the rules.
The Delimiter is used in the following scenarios:
When reserved words are used in an object name or an object name component.
We recommend that you do not use reserved keywords as object names. From Microsoft®SQL Server™Databases updated in earlier versions may contain identifiers that are reserved in earlier versions but not in SQL Server 2000. Separate identifiers to reference objects until they can be changed.
When using characters not listed as legal identifiers.
SQL Server allows the use of any character in the current Code page in the separator identifier. However, it is difficult to read and maintain SQL statements and scripts without selecting special characters in the object name.
The types of delimiters used by Transact-SQL:
The description Delimiter is only used for identifiers. Separators cannot be used for keywords, whether or not they are marked as reserved words in SQL Server.
The referenced identifiers are separated by double quotation marks:
SELECT * FROM "Blanks in Table Name"
Identifiers enclosed in brackets are separated by square brackets:
SELECT * FROM [Blanks In Table Name]
The referenced identifier is valid only when the QUOTED_IDENTIFIER option is set to ON. By default, when the Microsoft ole db provider for SQL Server is connected to the SQL Server ODBC driver, set QUOTED_IDENTIFIER to ON. By default, DB-Library does not set QUOTED_IDENTIFIER to ON. Regardless of the interface used, individual applications or users can change the settings at any time. SQL Server provides multiple methods to specify this option. For example, in SQL Server Enterprise Manager and SQL query analyzer, this option can be set in the dialog box. In Transact-SQL, you can use the quoted identifier option of SET QUOTED_IDENTIFIER, sp_dboption, or the user options Option of sp_configure to SET this option to multiple levels.
When QUOTED_IDENTIFIER is ON, SQL Server follows the SQL-92 rules for the use of double quotes and single quotes (') in SQL statements:
Double quotation marks can only be used to separate identifiers. They cannot be used to separate strings.
To maintain compatibility with existing applications, SQL Server does not fully enforce this rule. If the length of a string does not exceed the length of the identifier, the string can be contained in double quotation marks. However, this is not recommended.
Single quotes must be used to contain strings and cannot be used to separate identifiers.
If the string contains single quotes, you need to add a single quotation mark before single quotes:
SELECT * FROM "My Table"
WHERE "Last Name" = 'O' brien'
When QUOTED_IDENTIFIER is OFF, SQL Server follows the following rules for double quotation marks and single quotation marks:
Quotation marks cannot be used to separate identifiers, but are used as separators.
Single or double quotation marks can be used to contain strings.
If double quotation marks are used, the embedded single quotation marks do not need to be expressed using two single quotation marks:
SELECT * FROM [My Table]
WHERE [Last Name] = "O 'Brien"
Separators can be used in brackets regardless of QUOTED_IDENTIFIER settings.
Delimiter rules
The format rules for delimiter identifiers are:
The delimiter can contain the same number of characters (1 to 128, excluding delimiter characters) as the regular identifier ). The local temporary table identifier can contain a maximum of 116 characters.
The subject of the identifier can contain any combination of letters (except separators) in the current Code Page. For example, a delimiter identifier can contain spaces, any characters that are valid for regular identifiers, and any of the following characters: font size (~) Hyphen (-)
Exclamation point (!) Left parenthesis ({)
Percent sign (%) Right parenthesis (})
Insert number (^) marker (')
And (&) Full Stop (.)
Left parentheses () backslash (\)
Right parentheses () accent (')