SQL Server中[]与''的区别及使用疑问（基于Northwind数据库）

Great question! Let's break down exactly when to use each, and why you're hitting those weird edge cases with the Northwind database.

Core Difference: They Serve Entirely Different Purposes #

1. Square Brackets []: Escape for Identifiers #

Square brackets are SQL Server's way of wrapping database identifiers—think table names, column names, view names, or any object that's part of your database schema. You must use them in these scenarios:

• When the identifier has spaces: Northwind's famous Order Details table is a perfect example. Try running SELECT * FROM Order Details and you'll get a syntax error—SQL thinks Order and Details are two separate things. Wrap it in [Order Details] and it works.
• When the identifier is a SQL reserved word: If you had a table named User (which is a reserved keyword), you'd need to write [User] to avoid confusing the SQL parser.
• When the identifier has special characters: Columns like Discount# or Price$ need [Discount#] or [Price$] to be recognized correctly.

2. Single Quotes '': String Constants (and Alias Compatibility) #

Single quotes are for defining string literal values—like when you filter rows with WHERE CustomerID = 'ALFKI' (here 'ALFKI' is a string constant matching a customer ID).

Now, you mentioned using AS '列别名' works for renaming columns—this is actually a SQL Server compatibility quirk. While it's not standard SQL, SQL Server lets you use single quotes for column aliases as a convenience. But keep in mind:

• This only works for aliases, not for table/column identifiers. If you try SELECT 'CustomerID' FROM Customers, you won't get the values from the CustomerID column—you'll just get the string 'CustomerID' repeated for every row.
• For aliases with spaces or reserved words, you can use single quotes, but it's more consistent to use [] (or double quotes if you have QUOTED_IDENTIFIER enabled) to match how you handle other identifiers.

Why Do Single Quotes Fail in Some Scenarios? #

Let's use your Northwind example to make this concrete. Suppose you try to query the Order Details table with:

SELECT * FROM 'Order Details'

SQL Server sees 'Order Details' as a string literal, not a table name. It has no idea you're trying to reference a database object—so it throws an error. Swap in [Order Details], and SQL immediately recognizes it as the table you want to query.

Another example: If you have a column named Product Name and write SELECT 'Product Name' FROM Products, you'll get a column full of the string 'Product Name' instead of the actual product names. You need [Product Name] to pull the column's values.

Quick Rule of Thumb #

• Use [] when you're talking about a database object (table, column, etc.)—especially if it has spaces, reserved words, or special characters.
• Use '' when you're talking about a specific string value, or as a quick (but non-standard) way to set simple column aliases.

内容的提问来源于stack exchange，提问作者QWERTY1234567890