SQL Server数据库名称中哪些字符有效? [英] What characters are valid in an SQL Server database name?

问题描述

我们将为客户提供一个工具，该工具(除其他功能外)将创建一个新的SQL Server数据库，并且我希望能够对其提供的数据库名称进行基本验证. SQL Server的文档说明了数据库名称中哪些字符有效.但是，该文档显然是不正确的，因为我可以成功创建名称违反文档规则的数据库.

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.

根据SQL Server CREATE DATABASE 的文档，数据库名称必须符合以下规则身份标识; 标识符的规则取决于数据库兼容性级别.当兼容性级别为100(根据SQL Server Management Studio，其含义为"SQL Server 2008")时，名称必须以Unicode字母_，@或#开头；后跟一个或多个字母，数字，@，$，#或_.该文档明确指出不允许使用空格或特殊字符.

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.

这与现有证据不符，因为我可以使用SQL Server Management Studio创建名称为This & That | "Other"的数据库-该数据库不仅包含嵌入式空格(明确禁止)，而且包含特殊字符(|，")在文件名中甚至无效.我检查了一下，并且数据库的兼容性级别确实是"SQL Server 2008(100)"，即使该名称在该兼容性级别上被记录为无效.

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.

哎呀，我什至可以做CREATE DATABASE " "(是的，这是一个空格)，这证明第一个字符 not 不必是字母，下划线，符号或井号

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.

所以我想我的问题是，SQL Server数据库名称中哪些字符有效?是否有任何记录的规则与SQL Server的实际行为一致?

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?

推荐答案

标识符规则最后的状态:

在以下情况下使用标识符时 Transact-SQL语句 不符合的标识符 这些规则必须由 双引号或方括号.

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.

以下说明还可以

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

但不是

CREATE DATABASE This & That | "Other"

我同意这不是理解链接文档的方式:必须遵守标识符规则是什么意思，如果一旦将标识符括起来，规则就不再适用?封闭不符合标识符的要点应成为规则的一部分.

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.

这篇关于SQL Server数据库名称中哪些字符有效?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！