What characters are valid in an SQL Server database name?

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

Solution 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/#)!]

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

Solution 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 ([]).