tsql - What characters are valid in an SQL Server database name?

Question

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?

Answer 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.