資料庫識別碼Database Identifiers

適用於: 是SQL Server 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel 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的資料表,以及具有識別碼 KeyColDescription的兩個資料行: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.

SELECT *  
FROM TableX  
WHERE KeyCol = 124  

分隔識別碼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:

SELECT *  
FROM [TableX]         --Delimiter is optional.  
WHERE [KeyCol] = 124  --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:

SELECT *  
FROM [My 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.

  1. 第一個字元必須是以下任一項:The first character must be one of the following:

    • Unicode Standard 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.

    • 底線 ()、@ 符號或數字符號 (#)。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. 以 @ 符號開頭的一般識別碼代表本機變數或參數,而且不能做為任何其他類型之物件的名稱。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 功能的名稱會以兩個 @@ 符號為開頭。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 @@.

  2. 可包含的後續字元如下:Subsequent characters can include the following:

    • Unicode Standard 3.2 所定義的字母。Letters as defined in the Unicode Standard 3.2.

    • 其他基本拉丁文或其他國家 (地區) 字集中的十進位數字。Decimal numbers from either Basic Latin or other national scripts.

    • @ 符號、錢幣符號 ($)、數字符號或底線。The at sign, dollar sign ($), number sign, or underscore.

  3. 識別碼不得為 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. 這個層級可以使用 ALTER DATABASE 陳述式加以設定。This level can be set by using the ALTER DATABASE statement.

  4. 不允許內嵌的空格或特殊字元。Embedded spaces or special characters are not allowed.

  5. 不允許補充字元。Supplementary 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. 您可以使用 ALTER DATABASE來設定這個層級。This level can be set by using ALTER DATABASE.

另請參閱See Also

ALTER TABLE (Transact-SQL) ALTER TABLE (Transact-SQL)
CREATE DATABASE (SQL Server Transact-SQL) CREATE DATABASE (SQL Server Transact-SQL)
CREATE DEFAULT (Transact-SQL) CREATE DEFAULT (Transact-SQL)
CREATE PROCEDURE (Transact-SQL) CREATE PROCEDURE (Transact-SQL)
CREATE RULE (Transact-SQL) CREATE RULE (Transact-SQL)
CREATE TABLE (Transact-SQL) CREATE TABLE (Transact-SQL)
CREATE TRIGGER (Transact-SQL) CREATE TRIGGER (Transact-SQL)
CREATE VIEW (Transact-SQL) CREATE VIEW (Transact-SQL)
DECLARE @local_variable (Transact-SQL) DECLARE @local_variable (Transact-SQL)
DELETE (Transact-SQL) DELETE (Transact-SQL)
INSERT (Transact-SQL) INSERT (Transact-SQL)
保留關鍵字 (Transact-SQL) Reserved Keywords (Transact-SQL)
SELECT (Transact-SQL) SELECT (Transact-SQL)
UPDATE (Transact-SQL)UPDATE (Transact-SQL)