sql数据库可以创建同义词_如何使用同义词简化SQL Server数据库对象的使用

sql数据库可以创建同义词

The concept of SQL Server Synonyms was introduced the first time in SQL Server 2005 as an alias name that references an existing database object, replacing its fully qualified name. In this way, it makes the database object more portable and provides more flexibility for the clients to reach and maintain it. You can imagine Synonyms as a layer of abstraction that provides us with an easy way to connect to and manage the database objects without the need to identify the real name and location for these objects.

SQL Server同义词的概念是在SQL Server 2005中首次引入的，别名是引用现有数据库对象的别名，以代替其完全限定的名称。 这样，它使数据库对象更具可移植性，并为客户端提供了更大的灵活性来访问和维护它。 您可以将同义词想象为一个抽象层，它为我们提供了一种简单的方法来连接和管理数据库对象，而无需标识这些对象的真实名称和位置。

Synonyms are useful in simplifying complicated and lengthy object names by providing short and friendly alternative names for these database objects. You can benefit from Synonyms by providing backward compatibility for the database objects that are used by legacy systems in case you drop or rename that objects. It may be found to be very simple from the definition, but for database administrators and developers, it would be very useful and simplify their jobs if it is used in a correct way.

通过为这些数据库对象提供简短友好的替代名称，同义词可以简化复杂而冗长的对象名称。 通过为遗留系统使用的数据库对象提供向后兼容性，您可以从同义词中受益，以防您删除或重命名这些对象。 从定义中可以发现它非常简单，但是对于数据库管理员和开发人员来说，如果使用正确的方式，它将非常有用并简化他们的工作。

Synonym changes are also transparent from the client application perspective, as no change required from the client side if the Synonym is changed to reference a different database object, as long as the column names are not changed.

从客户端应用程序的角度看，同义词更改也是透明的，因为只要不更改列名，如果将同义词更改为引用其他数据库对象，则无需从客户端进行更改。

Assume that you plan to change the name of a database object that is used heavily in your queries. It would seem like a very difficult task, as you need to go through all places in which this object is used. An easy way to perform that is to create a Synonym that references the database object and use that Synonym in your queries. If you need to change the database object name, you need only to change the referenced object only in the Synonym definition, by dropping and recreating the Synonym, without the need to visit all places in which object is mentioned. You can also move the base object easily to another database on the same server or to another SQL server without performing any change from the application side. Just you need to drop the Synonym and recreate a new one that points to the new location of that object.

假设您打算更改在查询中大量使用的数据库对象的名称。 这似乎是一项非常困难的任务，因为您需要遍历使用此对象的所有地方。 一种简单的执行方法是创建一个引用数据库对象的同义词，然后在查询中使用该同义词。 如果需要更改数据库对象名称，则只需删除并重新创建同义词即可仅更改同义词定义中的引用对象，而无需访问提及该对象的所有位置。 您还可以轻松地将基础对象移动到同一服务器上的另一个数据库或另一个SQL服务器，而无需在应用程序端进行任何更改。 只是您需要删除同义词并重新创建一个指向该对象新位置的新名称。

Synonyms also help also in obscuring the name of the database objects, for security purposes, by creating a Synonym that references the database object and allows the users to query the Synonym rather than querying the base table directly.

为了安全起见，同义词还通过创建引用数据库对象并允许用户查询同义词而不是直接查询基表的同义词来帮助掩盖数据库对象的名称。

The Synonym, like any other database object, should belong to a database schema and should be provided a unique name that follows the T-SQL identifiers rules. The naming convention rules can be also applied to the Synonym, such as using a prefix or suffix with the Synonym name to make it easy to recognize that the database object is a Synonym. A Synonym can be used to reference the following database objects types:

与其他任何数据库对象一样，同义词应属于数据库架构，并应提供遵循T-SQL标识符规则的唯一名称。 命名约定规则也可以应用到同义词，例如使用带有同义词名称的前缀或后缀，以轻松识别数据库对象是同义词。 同义词可用于引用以下数据库对象类型：

• Assembly (CLR) stored procedure
  程序集（CLR）存储过程
• Assembly (CLR) table-valued function
  程序集（CLR）表值函数
• Assembly (CLR) scalar function
  装配（CLR）标量功能
• Assembly (CLR) aggregate functions
  程序集（CLR）集合函数
• Replication-filter-procedure
  复制过滤程序
• Extended stored procedure
  扩展存储过程
• SQL scalar function
  SQL标量函数
• SQL table-valued function
  SQL表值函数
• SQL inline-tabled-valued function
  SQL内联表值函数
• SQL stored procedure
  SQL存储过程
• View
  视图
• User-defined table
  用户定义表

On the other hand, you cannot use a Synonym to reference other Synonyms or to reference a user-defined aggregate function. The object that is referenced by a Synonym will be checked at runtime, which means that the Synonym can be created with spelling or referencing errors, but you will get these errors while using that Synonym.

另一方面，您不能使用同义词来引用其他同义词或引用用户定义的聚合函数。 同义词引用的对象将在运行时检查，这意味着可以使用拼写或引用错误创建同义词，但是使用该同义词时会出现这些错误。

You can easily drop a Synonym without getting any error or warning messages that it is being referenced by any database object, or you can modify the base object without affecting the Synonym, due to the fact that the Synonyms and the base objects are loosely bonded. Synonyms cannot be referenced in a DDL T-SQL statement, such as modifying using an ALTER statement. Having the fact that the Synonyms are not schema-bound database objects, it cannot be referenced by schema-bound expressions such as:

您可以轻松删除同义词，而不会收到任何数据库对象正在引用的任何错误或警告消息，或者您可以修改基础对象而不影响同义词，因为同义词和基础对象是松散结合的。 不能在DDL T-SQL语句中引用同义词，例如使用ALTER语句进行修改。 由于同义词不是架构绑定的数据库对象，因此不能由架构绑定的表达式引用它，例如：

• CHECK constraints
  检查约束
• Computed columns
  计算列
• Default expressions
  默认表达式
• Rule expressions
  规则表达式
• Schema-bound views
  架构绑定视图
• Schema-bound functions
  架构绑定功能

To create a Synonym that references objects across schemas, databases and servers, you need to specify the schema and the name of the Synonym and the schema and the name of the database object that the synonym references. The syntax that is used to create a new Synonym is as shown below:

要创建引用跨架构，数据库和服务器的对象的同义词，您需要指定架构和同义词的名称以及架构和同义词引用的数据库对象的名称。 创建新同义词的语法如下所示：

CREATE SYNONYM schema_name_1. synonym_name FOR server_name. database_name. schema_name_2. object_name

创建SYNONYM schema_name_1。 synonym_name FOR服务器名。 数据库名称。 schema_name_2。 object_name

For a new Synonym, you need to provide the schema_name_1 that specifies the schema in which the synonym will be created, with the default schema of the current user will be used if the schema is not specified in the CREATE SYNONYM statement and the synonym_name that specifies the name of the new synonym.

对于新的同义词，您需要提供用于指定将在其中创建同义词的模式的schema_name_1，如果未在CREATE SYNONYM语句中指定该模式，并且指定了synonym_name，则将使用当前用户的默认模式新同义词的名称。

For the referenced object, you can provide the server_name that specifies the name of the server on which base object is located, the database_name that is the name of the database in which the base object is located, the schema_name_2 that is the name of the schema of the base object, with the default schema of the current user will be used if it is not provided, and the object_name that specifies the name of the base object that will be referenced by the Synonym.

对于引用的对象，您可以提供server_name，该server_name指定基础对象所在的服务器的名称，database_name是基础对象所在的数据库的名称，schema_name_2是该架构的名称。基础对象的名称，如果未提供，则使用当前用户的默认架构，而object_name指定将由同义词引用的基础对象的名称。

To be able to create a Synonym in the provided schema, you should have CREATE SYNONYM permissions with db_owner permissions in that schema or, at least at a minimum, ALTER SCHEMA permissions. Synonyms can be also created with the New Synonym window using SQL Server Management Studio, by right-clicking on the Synonyms node under the current database as shown below:

为了能够在提供的架构中创建同义词，您应该具有在该架构中具有db_owner权限的CREATE SYNONYM权限，或者至少具有ALTER SCHEMA权限。 也可以使用SQL Server Management Studio在“新建同义词”窗口中创建同义词，方法是右键单击当前数据库下的“同义词”节点，如下所示：

Where you can provide the previously described parameters, such as the Synonym schema and name and the server name, database name, schema name and the name of the referenced object, in order to create the Synonym as follows:

您可以在其中提供先前描述的参数，例如“同义词”模式和名称以及服务器名称，数据库名称，模式名称和被引用对象的名称，以便按以下方式创建同义词：

Once created, you can perform SELECT, INSERT, UPDATE, DELETE or EXECUTE operations on that Synonym. To be able to perform these operations, a number of permissions should be granted on the Synonym, such as:

创建完成后，您可以对该同义词执行SELECT，INSERT，UPDATE，DELETE或EXECUTE操作。 为了能够执行这些操作，应在同义词上授予许多权限，例如：

• CONTROL
  控制
• DELETE
  删除
• EXECUTE
  执行
• INSERT
  插
• SELECT
  选择
• TAKE OWNERSHIP
  取得所有权
• UPDATE
  更新
• VIEW DEFINITION
  查看定义

Synonym owners or users with db_owner or db_ddladmin permissions can GRANT, DENY or REVOKE permission at the Synonym level, with no effect at the base table level. The below script is used to create a new database user, and grant it SELECT permission at the Synonym level:

同义词所有者或具有db_owner或db_ddladmin权限的用户可以在同义词级别上授予GRANT，DENY或REVOKE权限，而在基表级别上则无效。 以下脚本用于创建新的数据库用户，并在“同义词”级别授予其SELECT权限：

 
USE [SQLShackDemo]
GO
CREATE USER [suheir] FOR LOGIN [suheir]
GO
GRANT SELECT ON [dbo].[MySynonym] TO [suheir]
 

创建一个同义词来引用本地对象 (Creating a synonym to reference a local object)

The below T-SQL statement is used to create a Synonym to reference a local table in the same SQL Server instance, where the base database name, the schema and the table name are provided in the CREATE SYNONYM statement:

下面的T-SQL语句用于创建同义词以引用同一SQL Server实例中的本地表，其中在CREATE SYNONYM语句中提供了基础数据库名称，架构和表名称：

 
USE SQLShackDemo
GO
CREATE SYNONYM dbo.MySynonym
FOR SQLShackDemo.dbo.SynTestNew;
GO
 

After creating the Synonym, you can easily retrieve data from it directly using the SELECT statement shown below:

创建同义词后，您可以使用下面所示的SELECT语句轻松地直接从中检索数据：

 
  SELECT TOP 10 * FROM MySynonym
 

The SELECT statement result will retrieve the data from the base table directly as follows:

SELECT语句的结果将直接从基表中检索数据，如下所示：

创建同义词来引用远程对象 (Creating a synonym to reference a remote object)

As mentioned previously within this article, Synonyms can be used to simplify the name of the base object by using a short alias instead of using the full object name. The below T-SQL statement is used to create a Synonym to reference a database table hosted in a remote SQL Server instance, where the server name, the base database name, the schema and the table name are provided in the CREATE SYNONYM statement:

如本文前面所述，可以通过使用短别名而不是完整的对象名称来使用同义词来简化基础对象的名称。 下面的T-SQL语句用于创建同义词，以引用远程SQL Server实例中托管的数据库表，其中CREATE SYNONYM语句中提供了服务器名称，基本数据库名称，架构和表名称：

 
USE SQLShackDemo
GO
CREATE SYNONYM DevSyn
FOR DEV.Testdb.dbo.InvoiceTest;
GO
 

Once created, you can easily retrieve the data by querying the Synonym simple name directly as in the SELECT statement shown below:

创建完成后，您可以直接查询“同义词”简单名称来轻松检索数据，如下面的SELECT语句所示：

 
SELECT TOP 10 * FROM DevSyn
 

Instead of using the full four-part name:

而不使用完整的四部分名称：

 
SELECT TOP 10 * FROM DEV.Testdb.dbo.InvoiceTest;
 

Both queries will return the same result as shown below:

这两个查询将返回相同的结果，如下所示：

If you check the execution plan of the previous SELECT statement using ApexSQL Plan, the execution plan viewer and query optimization tool, you will see that the Synonym name will be translated to the base table name and location when the query is executed as clear from the execution plan below:

如果使用ApexSQL Plan（ 执行计划查看器和查询优化工具）检查上一个SELECT语句的执行计划 ，您将看到执行查询时 ，同义词名称将转换为基表名称和位置，从执行计划如下：

The screenshot is from ApexSQL Plan, a free tool to view and analyze SQL Server query execution plans

屏幕截图来自ApexSQL Plan ， ApexSQL Plan是查看和分析SQL Server查询执行计划的免费工具

创建同义词以引用用户定义的函数 (Creating a synonym to reference a user-defined function)

A Synonym can be also used to reference user-defined functions. That allows you to rename or move that function to another SQL Server instance without affecting the clients, by dropping and recreating the Synonym one time to reference the new user-function name or location.

同义词也可以用来引用用户定义的函数。 这样，您可以一次删除并重新创建“同义词”以引用新的用户函数名称或位置，从而在不影响客户端的情况下将函数重命名或移动到另一个SQL Server实例。

Let’s create a simple user-defined function that will return the absolute value of the negative integers:

让我们创建一个简单的用户定义函数，该函数将返回负整数的绝对值：

 
USE SQLShackDemo
GO
CREATE FUNCTION dbo.TestSynFunction (@InputNum int)  
RETURNS int  
AS  
BEGIN  
IF @InputNum < 0  
BEGIN  
    SET @InputNum= abs(@InputNum)
END  
RETURN(@InputNum);  
END;  
GO
 

Then we will create a Synonym that references the created function:

然后，我们将创建一个引用创建函数的同义词：

 
CREATE SYNONYM dbo.AbsVal  
FOR dbo.TestSynFunction;  
GO
 

We can easily call the function within our code by selecting from it, passing the required parameters, as shown below:

我们可以通过在代码中进行选择，并传递所需的参数来轻松调用该函数，如下所示：

 
DECLARE @Num int;  
SET @Num = -7;  
SELECT dbo.AbsVal(@Num) AS ABSValue;  
 

The absolute value of the passed negative integer will be like:

传递的负整数的绝对值将类似于：

SQL Server allows you to perform different types of operations on the Synonyms, that change the data and not the schema of the object, such as SELECT, INSERT, UPDATE, DELETE or EXECUTE operations. When you perform a data modification operation, let’s say an UPDATE statement, the base object will be affected. For example, if you update a row in a Synonym that references a database base table, you will actually update that row in the referenced table too.

SQL Server允许您对同义词执行不同类型的操作，这些操作会更改数据而不是对象的架构，例如SELECT，INSERT，UPDATE，DELETE或EXECUTE操作。 当您执行数据修改操作时，假设一条UPDATE语句，基础对象将受到影响。 例如，如果您更新引用数据库基表的同义词中的一行，则实际上您也将同时更新所引用表中的该行。

Assume that we need to update the EmpPhone of the below employee:

假设我们需要更新以下员工的EmpPhone：

The following UPDATE statement is used to update that employee information from the Synonym itself:

以下UPDATE语句用于从同义词本身更新该员工信息：

 
USE [SQLShackDemo]
GO
UPDATE [dbo].[MySynonym] SET EmpPhone='612-854-3528' WHERE ID = 2010001
 

Checking the execution plan of the previous UPDATE statement that is generated, the plan will show us that the query performed a clustered index update on the base table, SynTestNew table, as shown below:

检查生成的上一条UPDATE语句的执行计划，该计划将向我们显示该查询对基表SynTestNew表执行了聚集索引更新，如下所示：

The screenshot is from ApexSQL Plan, a free tool to view and analyze SQL Server query execution plans

屏幕截图来自ApexSQL Plan ， ApexSQL Plan是查看和分析SQL Server查询执行计划的免费工具

Which is also clear when we retrieve that employee’s information from the base table directly as shown below:

当我们直接从基表中检索该员工的信息时，这也很明显，如下所示：

The sys.synonyms DMV can be queried for the synonyms information in a specific database. Useful synonym metadata information retrieved from that DMV such as the synonym name and the name of the base object. The T-SQL statement below is used to query the sys.synonyms DMV for the metadata information of the Synonyms created under the SQLShackDemo testing database:

可以在sys.synonyms DMV中查询特定数据库中的同义词信息。 从该DMV检索到的有用的同义词元数据信息，例如同义词名称和基础对象的名称。 下面的T-SQL语句用于查询sys.synonyms DMV以获取在SQLShackDemo测试数据库下创建的同义词的元数据信息：

 
USE SQLShackDemo
GO
SELECT name, type_desc, create_date,base_object_name FROM sys.synonyms
 

The list of created Synonyms under SQLShackDemo database are shown below:

在SQLShackDemo数据库下创建的同义词列表如下所示：

The base type of the base object referenced by a Synonym can be checked using the OBJECTPROPERTYEX function. The below T-SQL statement retrieve the type of the base object for the previously created Synonyms:

可以使用OBJECTPROPERTYEX函数检查由同义词引用的基础对象的基础类型。 下面的T-SQL语句检索先前创建的同义词的基础对象的类型：

 
USE SQLShackDemo
GO
SELECT OBJECTPROPERTYEX(OBJECT_ID('MySynonym'), 'BaseType') AS LocalSyn_BaseType;  
GO
SELECT OBJECTPROPERTYEX(OBJECT_ID('DevSyn'), 'BaseType') AS RemoteSyn_BaseType;  
GO
SELECT OBJECTPROPERTYEX(OBJECT_ID('AbsVal'), 'BaseType') AS FunctionSyn_BaseType;  
GO
 

The result shows that the type of the local and remote base objects that are referenced by the first two Synonyms is User Table, and the type of the base object referenced by the last Synonym is User-Defined Function as below:

结果显示，前两个同义词所引用的本地和远程基础对象的类型为“用户表”，最后一个同义词所引用的基础对象的类型为“用户定义的函数”，如下所示：

You can also browse all Synonyms created under a specific database from the Synonyms node under the current database using SQL Server Management Studio as follows:

您还可以使用SQL Server Management Studio从当前数据库下的“同义词”节点浏览在特定数据库下创建的所有同义词，如下所示：

Where you can check the properties of each Synonym from the Synonym Properties window, including the referenced base object server, database, schema and name as clear from the image below:

在“同义词属性”窗口中，您可以在其中检查每个同义词的属性，包括从下图清晰可见的引用的基础对象服务器，数据库，架构和名称：

A Synonym can be dropped at any time, without affecting the base object. Dropping the Synonym is required when you need to rename or move the Synonym referenced object in order to create a new Synonym with the new change, as you cannot use the ALTER statement to modify that Synonym. The Synonym can be simply dropped by passing the Synonym name to the DROP SYNONYM T-SQL statement as in the script below:

可以随时删除同义词，而不会影响基础对象。 当您需要重命名或移动同义词引用的对象以使用新更改创建新的同义词时，需要删除同义词，因为您不能使用ALTER语句来修改该同义词。 可以通过将同义词名称传递给DROP SYNONYM T-SQL语句来简单地删除同义词，如以下脚本所示：

 
USE [SQLShackDemo]
GO
DROP SYNONYM [dbo].[AbsVal]
GO
 

结论 (Conclusion)

SQL Server Synonyms are very useful in simplifying and shortening the database objects names, in a way it makes these objects usage easier and more flexible.

SQL Server同义词在简化和缩短数据库对象名称方面非常有用，它可以使这些对象的使用更轻松，更灵活。

Synonyms also provide us with a layer of abstraction that helps us to use and manage these database objects without the need to identify the real name and location of these objects.

同义词还为我们提供了一层抽象，可以帮助我们使用和管理这些数据库对象，而无需标识这些对象的真实名称和位置。

Within this article, we described how to use the Synonym to reference local and remote database objects, in addition, to reference a user-defined function. Enjoy using the Synonyms in your environment, in the correct way, with the best methods to get metadata about these Synonyms as mentioned within this article.

在本文中，我们描述了如何使用同义词来引用本地和远程数据库对象，以及引用用户定义的函数。 享受以正确的方式在您的环境中使用同义词的方法，以及获得本文中提到的有关这些同义词的元数据的最佳方法。

翻译自: https://www.sqlshack.com/simplify-sql-server-database-object-usage-synonyms/

sql数据库可以创建同义词