Demystifying Square Brackets: Their Role in SQL Server

1. Delimiting Identifiers: Identifiers are names given to objects in a database, such as tables, columns, or functions. By default, certain characters and keywords are reserved by T-SQL and cannot be used directly in these names. Square brackets allow you to enclose an identifier that would otherwise cause an error.

   For example, if you want to name a table "New Table", you need to use brackets because "Table" is a reserved keyword in T-SQL. You would write it as CREATE TABLE [New Table] (...).

2. Optional Elements: In some SQL Server documentation or tools, square brackets might be used to indicate optional elements within a statement's syntax. These brackets don't denote code you need to include literally, but rather show that the enclosed part is not mandatory.

   For instance, SELECT * FROM [schema_name.]table_name might show that the schema_name part is optional. You can write the query as SELECT * FROM table_name if the table is in the default schema.

Here are some additional benefits of using square brackets around identifiers, even when not strictly necessary:

• Improved Code Readability: It can make your code clearer, especially when dealing with complex names or identifiers from external sources.
• Consistency: Enforcing a habit of using brackets everywhere helps avoid errors if you encounter a reserved keyword or special character later.

• Reserved Keyword:

-- This would cause an error because "Order" is a reserved keyword
CREATE TABLE Order (ID int, Name varchar(50));

-- Using brackets allows the table name "Order"
CREATE TABLE [Order] (ID int, Name varchar(50));

• Identifier with Space:

-- Table name with a space needs brackets
CREATE TABLE My New Table (ID int, Data varchar(100));

Optional Elements:

-- Schema name is optional here (assuming default schema)
SELECT * FROM table_name;

-- You can use brackets for clarity even when optional
SELECT * FROM [dbo].[table_name];  -- `dbo` is the default schema

-- This shows column aliases as optional
SELECT customer_id AS ID, customer_name AS Name FROM Customers;

Character Set within LIKE Operator:

Square brackets are also used to define a character set within the LIKE operator for pattern matching:

-- Find names starting with 'A' or 'B'
SELECT * FROM Customers WHERE Name LIKE '[AB]%';

-- Match names containing the literal '[' character
SELECT * FROM Customers WHERE Name LIKE 'fore [[]TAG] aft'; 

For example, instead of [Order], you could rename the table to OrderDetails.

Backticks (Some Databases):

While not standard in T-SQL, some database systems like MySQL allow using backticks () to enclose identifiers containing reserved words or special characters. However, this approach is not portable across different SQL dialects.

Double Quotes (Some Databases):

Similar to backticks, some databases might allow using double quotes (" ") for identifiers. Again, this is not standard in T-SQL and portability is limited.