数据库标识符Database Identifiers
03/16/2017
本文内容
适用于:Applies to: SQL ServerSQL Server(所有支持的版本)
SQL ServerSQL Server (all supported versions)
Azure SQL 数据库Azure SQL Database
Azure SQL 数据库Azure SQL Database
Azure SQL 托管实例Azure SQL Managed Instance
Azure SQL 托管实例Azure SQL Managed Instance
Azure Synapse AnalyticsAzure Synapse Analytics
Azure Synapse AnalyticsAzure Synapse Analytics
并行数据仓库Parallel Data Warehouse
并行数据仓库Parallel Data Warehouse适用于:Applies to:
SQL ServerSQL Server(所有支持的版本)
SQL ServerSQL Server (all supported versions)
Azure SQL 数据库Azure SQL Database
Azure SQL 数据库Azure SQL Database
Azure SQL 托管实例Azure SQL Managed Instance
Azure SQL 托管实例Azure SQL Managed Instance
Azure Synapse AnalyticsAzure Synapse Analytics
Azure Synapse AnalyticsAzure Synapse Analytics
并行数据仓库Parallel Data Warehouse
并行数据仓库Parallel Data Warehouse
数据库对象的名称即为其标识符。The database object name is referred to as its identifier. MicrosoftMicrosoft SQL ServerSQL Server 中的所有内容都可以有标识符。Everything in MicrosoftMicrosoft SQL ServerSQL Server can have an identifier. 服务器、数据库和数据库对象(例如表、视图、列、索引、触发器、过程、约束及规则等)都可以有标识符。Servers, databases, and database objects, such as tables, views, columns, indexes, triggers, procedures, constraints, and rules, can have identifiers. 大多数对象要求有标识符,但对有些对象(例如约束),标识符是可选的。Identifiers are required for most objects, but are optional for some objects such as constraints.
对象标识符是在定义对象时创建的。An object identifier is created when the object is defined. 标识符随后用于引用该对象。The identifier is then used to reference the object. 例如,下列语句创建一个标识符为 TableX的表,该表中有两列的标识符分别是 KeyCol 和 Description:For example, the following statement creates a table with the identifier TableX, and two columns with the identifiers KeyCol and Description:
CREATE TABLE TableX
(KeyCol INT PRIMARY KEY, Description nvarchar(80));
此表还有一个未命名的约束。This table also has an unnamed constraint. PRIMARY KEY 约束没有标识符。The PRIMARY KEY constraint has no identifier.
标识符的排序规则取决于定义标识符的级别。The collation of an identifier depends on the level at which it is defined. 为实例级对象(如登录名和数据库名)的标识符分配实例的默认排序规则。Identifiers of instance-level objects, such as logins and database names, are assigned the default collation of the instance. 为数据库对象(如表、视图和列名)的标识符分配数据库的默认排序规则。Identifiers of objects in a database, such as tables, views, and column names, are assigned the default collation of the database. 例如,对于名称差别仅在于大小写的两个表,可在使用区分大小写排序规则的数据库中创建,但不能在使用不区分大小写排序规则的数据库中创建。For example, two tables with names that differ only in case can be created in a database that has case-sensitive collation, but cannot be created in a database that has case-insensitive collation.
备注
变量的名称或函数和存储过程的参数必须符合 Transact-SQLTransact-SQL 标识符的规则。The names of variables, or the parameters of functions and stored procedures must comply with the rules for Transact-SQLTransact-SQL identifiers.
标识符的种类Classes of Identifiers
有两类标识符:There are two classes of identifiers:
常规标识符Regular identifiers
符合标识符的格式规则。Comply with the rules for the format of identifiers. 在 Transact-SQLTransact-SQL 语句中使用常规标识符时不用将其分隔开。Regular identifiers are not delimited when they are used in Transact-SQLTransact-SQL statements.
USE AdventureWorks
GO
SELECT *
FROM HumanResources.Employee
WHERE NationalIDNumber = 153479919
分隔标识符Delimited identifiers
包含在双引号 (") 或者方括号 ([ ]) 内。Are enclosed in double quotation marks (") or brackets ([ ]). 不会分隔符合标识符格式规则的标识符。Identifiers that comply with the rules for the format of identifiers might not be delimited. 例如:For example:
USE AdventureWorks
GO
SELECT *
FROM [HumanResources].[Employee] --Delimiter is optional.
WHERE [NationalIDNumber] = 153479919 --Delimiter is optional.
在 Transact-SQLTransact-SQL 语句中,必须对不符合所有标识符规则的标识符进行分隔。Identifiers that do not comply with all the rules for identifiers must be delimited in a Transact-SQLTransact-SQL statement. 例如:For example:
USE AdventureWorks
GO
CREATE TABLE [SalesOrderDetail Table] --Identifier contains a space and uses a reserved keyword.
(
[Order] [int] NOT NULL,
[SalesOrderDetailID] [int] IDENTITY(1,1) NOT NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
[UnitPriceDiscount] [money] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_SalesOrderDetail_Order_SalesOrderDetailID] PRIMARY KEY CLUSTERED
([Order] ASC, [SalesOrderDetailID] ASC)
);
GO
SELECT *
FROM [SalesOrderDetail Table] --Identifier contains a space and uses a reserved keyword.
WHERE [Order] = 10; --Identifier is a reserved keyword.
常规标识符和分隔标识符包含的字符数都必须在 1 到 128 之间。Both regular and delimited identifiers must contain from 1 through 128 characters. 对于本地临时表,标识符最多可以有 116 个字符。For local temporary tables, the identifier can have a maximum of 116 characters.
常规标识符规则Rules for Regular Identifiers
变量、函数和存储过程的名称必须符合 Transact-SQLTransact-SQL 标识符的规则。The names of variables, functions, and stored procedures must comply with the following rules for Transact-SQLTransact-SQL identifiers.
第一个字符必须是下列字符之一:The first character must be one of the following:
Unicode 标准 3.2 定义的字母,A letter as defined by the Unicode Standard 3.2. Unicode 中定义的字母包括拉丁字符 a-z 和 A-Z,以及来自其他语言的字母字符。The Unicode definition of letters includes Latin characters from a through z, from A through Z, and also letter characters from other languages.
下划线 (_)、at 符号 (@) 或数字符号 (#)。The underscore (_), at sign (@), or number sign (#).
在 SQL ServerSQL Server中,某些位于标识符开头位置的符号具有特殊意义。Certain symbols at the beginning of an identifier have special meaning in SQL ServerSQL Server. 以 at 符号开头的常规标识符始终表示局部变量或参数,并且不能用作任何其他类型的对象的名称。A regular identifier that starts with the at sign always denotes a local variable or parameter and cannot be used as the name of any other type of object. 以一个数字符号开头的标识符表示临时表或过程。An identifier that starts with a number sign denotes a temporary table or procedure. 以两个数字符号 (##) 开头的标识符表示全局临时对象。An identifier that starts with double number signs (##) denotes a global temporary object. 虽然数字符号或两个数字符号字符可用作其他类型对象名的开头,但是我们建议不要这样做。Although the number sign or double number sign characters can be used to begin the names of other types of objects, we do not recommend this practice.
某些 Transact-SQLTransact-SQL 函数的名称以两个 at 符号 (@@) 开头。Some Transact-SQLTransact-SQL functions have names that start with double at signs (@@). 为了避免与这些函数混淆,不应使用以 @@ 开头的名称。To avoid confusion with these functions, you should not use names that start with @@.
后续字符可以包括:Subsequent characters can include the following:
Unicode 标准 3.2 定义的字母。Letters as defined in the Unicode Standard 3.2.
基本拉丁字符或其他国家/地区字符中的十进制数字。Decimal numbers from either Basic Latin or other national scripts.
at 符号、美元符号 ($)、数字符号或下划线。The at sign, dollar sign ($), number sign, or underscore.
标识符必须不能是 Transact-SQLTransact-SQL 保留字。The identifier must not be a Transact-SQLTransact-SQL reserved word. SQL ServerSQL Server 保留保留字的大写和小写版本。reserves both the uppercase and lowercase versions of reserved words. 在 Transact-SQLTransact-SQL 语句中使用标识符时,不符合这些规则的标识符必须由双引号或括号分隔。When identifiers are used in Transact-SQLTransact-SQL statements, the identifiers that do not comply with these rules must be delimited by double quotation marks or brackets. 保留字依赖于数据库兼容级别。The words that are reserved depend on the database compatibility level. This level can be set by using the ALTER DATABASE statement.
不允许嵌入空格或特殊字符。Embedded spaces or special characters are not allowed.
在 Transact-SQLTransact-SQL 语句中使用标识符时,不符合这些规则的标识符必须由双引号或括号分隔。When identifiers are used in Transact-SQLTransact-SQL statements, the identifiers that do not comply with these rules must be delimited by double quotation marks or brackets.
备注
一些常规标识符格式规则取决于数据库兼容级别。Some rules for the format of regular identifiers depend on the database compatibility level. This level can be set by using ALTER DATABASE.
另请参阅See Also