What characters are valid in an SQL Server databas

问题:

We're going to provide our clients with a tool that (among other things) creates a new SQL Server database, and I want to be able to do basic validation on the database name they provide. SQL Server's documentation explains what characters are valid in a database name. However, the documentation is apparently incorrect, because I can successfully create databases whose names violate the documented rules.

According to SQL Server's documentation for CREATE DATABASE, database names must comply with the rules for identifiers; and the rules for identifiers depend on the database compatibility level. When the compatibility level is 100 (which, according to SQL Server Management Studio, means "SQL Server 2008"), the name must start with a Unicode letter, _, @, or #; followed by one or more letters, numbers, @, $, #, or _. The documentation clearly states that embedded spaces or special characters are not allowed.

This flies in the face of the available evidence, because I can use SQL Server Management Studio to create a database whose name is This & That | "Other" -- which not only contains embedded spaces (explicitly forbidden), but contains special characters (|, ") that aren't even valid in a filename. I checked, and the database's compatibility level is indeed "SQL Server 2008 (100)", even though its name is documented to be invalid at that compatibility level.

Heck, I can even do CREATE DATABASE " " (yes, that's a single space), which proves that the first character does not have to be a letter, underscore, at sign, or pound sign.

So I guess my question is, what characters are valid in an SQL Server database name? Are there any documented rules that are consistent with SQL Server's actual behavior?

回答1:

The rules for identifiers state at the end:

When identifiers are used in Transact-SQL statements, the identifiers that do not comply with these rules must be delimited by double quotation marks or brackets.

By choosing a database name which does not conform to those rules, you have to enclose it always with double quotation marks or brackets.

If the rules for regular identifiers are respected, you may use your database name without quotes/brackets.

The following instructions are ok

CREATE DATABASE [conformingName]
CREATE DATABASE conformingName
CREATE DATABASE [This & That | "Other"]

but not

CREATE DATABASE This & That | "Other"

EDIT:

I agree that this is not how one would understand the linked documentation: What does must comply with the rules for identifiers mean if the rules do not apply anymore as soon as the identifier is enclosed? The point about enclosing non conforming identifiers should be part of the rules.

回答2:

There is a difference between regular identifiers and delimited identifiers. A regular identifier is bound by the limitations that you mention, while a delimited identifier can contain any characters (except the delimiter).

As you are using quotation marks around the identifier, it's a delimited identifier, and you are not limited by the rules of regular identifiers.

Without the delimiters you can only create databases with identifiers that follow the rules of regular identifiers:

create database db_name

With delimiters, you can use pretty much anything:

create database "That's a funny name, isn't it?"

create database [)(/%Q)/#&%¤)Q/#)!]

回答3:

Personally I would limit them to the alphabet and numbers and nothing else (well possibly also an _). No spaces, no funny symbols, no carriage returns etc. This is the safest you can do.

回答4:

Delimited names - surrounded by square brackets or double quotes (if QUOTED_IDENTIFIER is set to ON) - can contain basically anything other than the delimiters themselves. It is even possible to use the delimiters within the name with some escape logic. Note though that it is only the closing escape character that must be escaped. In the first example below, the single instance of the opening escape character in the name does not need to be escaped whereas the closing escape character does have to be escaped (by replacing the single instance with two). I guess the logic here is that whatever code that is parsing these statements is looking for a closing escape character and has is not interested in nested opening escape characters.

• [Test[Test] -> Test[Test
• [Test]]Test] -> Test]Test

The following is a description of the rules surrounding non delimited (nonquoted) identifier names in SQL Server 2012. It is an extract from the document Guide to Migrating from MySQL to SQL Server 2012.

Schema Object Names

In SQL Server 2012, an object name can be up to 128 characters long.

Nonquoted identifier names must follow these rules:

• The first character must be alphanumeric, an underscore (_), an at sign (@), or a number sign (#).
• Subsequent characters can include alphanumeric characters, an underscore, an at (@) sign, a number sign, or a dollar sign.
• The identifier must not be a Transact-SQL reserved word. Guide to Migrating from MySQL to SQL Server 2012 8
• Embedded spaces or special characters are not allowed.

Identifiers that start with @ or a number sign have special meanings. Identifiers starting with @ are local variable names. Those that start with a number sign are temporary table names.

To quote an identifier name in Transact-SQL, you must use square brackets ([]).