Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
shapper
Constraint Violating Yak Guru
450 Posts |
Posted - 2007-02-08 : 21:54:25
|
| Hello,Consider the following:SELECT NULL FROM [Customers] WHERE [CustomerID] LIKE @CustomerIDSometimes I see SQL code where [] is used and sometimes it isn't.What is the difference?Thanks,Miguel |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-02-08 : 22:36:21
|
[] is used when the object name is a reserved word like order, table etccreate table [table]( [from] datetime, [to] datetime) This is from BOLquote: Delimited IdentifiersAn identifier that complies with all the rules for the format of identifiers can be used with or without delimiters. An identifier that does not comply with the rules for the format of regular identifiers must always be delimited.Delimited identifiers are used in these situations: When reserved words are used for object names or portions of object names. It is recommended that reserved keywords not be used as object names. Databases upgraded from earlier versions of Microsoft® SQL Server™ may contain identifiers that include words not reserved in the earlier version, but are reserved words for SQL Server 2000. You can refer to the object using delimited identifiers until the name can be changed.When using characters not listed as qualified identifiers. SQL Server allows any character in the current code page to be used in a delimited identifier; however, indiscriminate use of special characters in an object name may make SQL statements and scripts difficult to read and maintain.Types of delimiters used in Transact-SQL:Note Delimiters are for identifiers only. Delimiters cannot be used for keywords, whether or not they are marked as reserved in SQL Server.Quoted identifiers are delimited by double quotation marks ("): SELECT * FROM "Blanks in Table Name"Bracketed identifiers are delimited by brackets ([ ]): SELECT * FROM [Blanks In Table Name]Quoted identifiers are valid only when the QUOTED_IDENTIFIER option is set to ON. By default, the Microsoft OLE DB Provider for SQL Server and SQL Server ODBC driver set QUOTED_IDENTIFIER ON when they connect. DB-Library does not set QUOTED_IDENTIFIER ON by default. Regardless of the interface used, individual applications or users may change the setting at any time. SQL Server provides a number of ways to specify this option. For example, in SQL Server Enterprise Manager and SQL Query Analyzer, the option can be set in a dialog box. In Transact-SQL, the option can be set at various levels using SET QUOTED_IDENTIFIER, the quoted identifier option of sp_dboption, or the user options option of sp_configure.When QUOTED_IDENTIFIER is ON, SQL Server follows the SQL-92 rules for the use of double quotation marks and the single quotation mark (') in SQL statements: Double quotation marks can be used only to delimit identifiers. They cannot be used to delimit character strings. To maintain compatibility with existing applications, SQL Server does not fully enforce this rule. Character strings can be enclosed in double quotation marks if the string does not exceed the length of an identifier; this practice is not recommended.Single quotation marks must be used to enclose character strings. They cannot be used to delimit identifiers. If the character string contains an embedded single quotation mark, insert an additional single quotation mark in front of the embedded mark:SELECT * FROM "My Table"WHERE "Last Name" = 'O''Brien'When QUOTED_IDENTIFIER is OFF, SQL Server follows these rules for the use of single and double quotation marks: Quotation marks cannot be used to delimit identifiers. Instead, use brackets as delimiters.Single or double quotation marks can be used to enclose character strings. If double quotation marks are used, embedded single quotation marks do not have to be denoted by two single quotation marks:SELECT * FROM [My Table]WHERE [Last Name] = "O'Brien"Delimiters in brackets can always be used, regardless of the setting of QUOTED_IDENTIFIER.
KH |
 |
|
|
|
|
|
|
|