数百个别名/同义词与数据库表的完全限定名称 [英] Hundreds of aliases/synonyms vs database tables' fully qualified names
问题描述
在多个模式中有数百个数据库表，在创建存储过程和视图时，您是否建议使用别名/同义词或完全限定名？给定一些schema.table如此，
Given hundreds of database tables in multiple schemas, when creating stored procedures and views, would you recommend using aliases/synonyms or fully qualified name? Given a few schema.table like so,
Orders.OrderHeader, Production.LineThroughput, Sales.Opportunities
我希望使用限定名称的性能略有提高，但如果Orders.Customers这样的表必须移动到Sales 。客户，我将不得不改变现有的意见/程序引用Orders.Customers或提前使用同义词，如果这样的举动是预期的。我看到使用同义词将代码移入测试的价值，但同时我也可以创建我的生产环境的副本来测试/ dev，而不需要同义词。
I expect a slight gain in performance with using qualified names but if a table such as Orders.Customers would have to be moved to Sales.Customers, I would either have to alter existing views/procedures referring to Orders.Customers or use a synonym ahead of time where such a move is anticipated. I see value in moving code into testing using synonyms but at the same time I could as well create a replica of my production environment to test/dev and not require synonyms.
SQL Server图书在线建议始终使用完全限定名。一些朋友建议为数百个表中的每一个创建一个同义词，并仅使用同义词。虽然我喜欢使用完全限定的名称（更易读和自解释的代码，知道什么引用对象属于什么模式，以及键入schema.table的习惯），您观察到的性能，操作或可读性（dis）优势使用同义词vs完全限定的表名？
SQL Server Books Online recommends to always use a fully qualified name. Some friends propose creating one synonym for each of the hundreds of tables and using only synonyms. While I prefer to use fully qualified name (more readable and self-explanatory code, knowing what referred object belongs to what schema, and habit of typing schema.table), what significant performance, operational or readability (dis)advantage have you observed with using synonyms vs fully qualified table names?
推荐答案
代码（SQL或存储过程）应保留在源代码管理系统数据库。如果你不能准确搜索和替换，你有严重的问题，所以真的应该首先解决。
The code (SQL or stored proc) should remain in a source code management system outside the database. If you cannot search and replace accurately, you have serious problems, so really that should be addressed first.
如果表的数量很大，你真的需要使用前缀。不是Sales.Customer，而是REF_Customer。
Where the no of tables are large, you really need to use prefixes. Not Sales.Customer but REF_Customer.
点表示它在单独的数据库（MS和Sybase）或模式（DB2和Oracle）中。这是一个单独的恢复单元，因此它们是单独维护的，并且每当你跨越边界时，服务器必须切换上下文。因此，您需要正确地收集表，记住这一点，并使用几个数据库/模式，而不是很多。例如。分隔引用表，不经常更新，通常从其他dbs /模式引用。
The dot specifies that it is in a separate database (MS and Sybase) or schema (DB2 and Oracle). That is a separate recovery unit, so they are maintained separately, and teh server has to switch contexts every time you cross teh boundary. Therefore you need to collect your tables properly with this in mind and use a few databases/schemas, not many. Eg. separate the reference tables, that do not get updated often, and are commonly referenced from other dbs/schemas.
始终在SQL代码中使用完全限定名。不是作为列名的前缀，而是在每个WHERE和FROM子句中。这将非常有助于移动数据库/模式或环境时，DEV到UAT或PROD。
Always use fully qualified names in SQL code. Not as a prefix for column names, but in every WHERE and FROM clause. That will greatly assist when moving database/schemas or environments, DEV to UAT or PROD.
这篇关于数百个别名/同义词与数据库表的完全限定名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
