Hundreds of alias / synonym names and database table tables

Question

Given hundreds of database tables in multiple schemas, would you recommend using aliases / synonyms or a full name when creating stored procedures and views? Given multiple schema.table for example

Orders.OrderHeader, Production.LineThroughput, Sales.Opportunities 

I expect a small performance gain using qualified names, but if a table such as Orders.Customers needs to be transferred to Sales.Customers, I will either have to modify the existing views / procedures related to Orders.Customers, or use a synonym before the time when such a move is expected. I see value when moving code into testing using synonyms, but at the same time, I could also create a replica of my production environment for testing / dev and not require synonyms.

SQL Server Books Online always recommends that you use a fully qualified name. Some friends suggest creating one synonym for each of the hundreds of tables and use only synonyms. Although I prefer to use a fully qualified name (more readable and understandable code, knowing which reference object belongs to which scheme and the habit of typing schema.table), what significant performance, operational or readability (dis) did you observe using synonyms against full table names ?

Answer 1

Code (SQL or a stored process) must remain in the source control system outside the database. If you cannot perform the search and replace accurately, you have serious problems, so this is actually the first thing you need to solve.

If there are no large tables, you really need to use prefixes. Not Sales.Customer, but REF_Customer.

The dot indicates that it is in a separate database (MS and Sybase) or schema (DB2 and Oracle). This is a separate recovery unit, so they are supported separately, and the server must switch contexts every time you cross the border. Therefore, you need to properly assemble your tables with this in mind and use several databases / schemas, not so much. For instance. Separate referencing tables that are often not updated and usually refer to other dbs / schemas.

Always use fully qualified names in SQL code. Not as a prefix for column names, but in every WHERE and FROM clause. This will greatly help when moving a database / schema or environment, from DEV to UAT or PROD.

Answer 2

I always use aliases in queries, one of the main reasons is that the query becomes easier to read. Repeating the full table name again and again simply clutters the code.

In most cases, the table name is superfertile, it is obvious only from the name of the field from which they originate. However, you should always indicate which table you get the field from, which simplifies the query process and becomes less sensitive to changes in the database.

Only a few tables are usually used in a query, and since they are related, it is easy enough to keep track of the tables used. Otherwise, this is another step to determine what the alias means and the information is available directly in the request.

If you need to move the table to another database, aliases are also useful. Since the full name of the table is indicated only once in the query, it is easier to change.

If there is a performance difference between different ways of specifying the same field, it will only be in the process of parsing the request, so that will be minimal. When the request is launched, there is no performance difference at all.

Answer 3

This situation is "dependent on."

Personally, in an application with tables distributed across several schemas, I would prefer not to hardcode the schema names anywhere in my code; for this purpose, I would create private synonyms for each circuit with code.

Thus, if a table moves from one schema to another or (even worse), the schema is renamed), I only need to update the synonyms pointing to it, instead of going through all the code and views.

On the other hand, in some cases, I prefer to write code that explicitly refers to a schema — it was code for a data migration project that had to reference the same table in several schemas, and we “locked” the schema names to make them work in DEV / TEST / etc, without requiring a lot of synonyms.

Answer 4

My two cents ...

Personally, the companies I worked with used fully qualified object names, even if they refer to objects in the same scheme. On rare occasions, when we moved the table to another schema, we usually set up the view (rather than a synonym) in the same place for backward compatibility, mainly because in the past we were burned by re-validation problems.

In terms of performance, at least in Oracle there are reports of a slight decrease in performance with synonyms, since the database must first resolve the name of the synonym, and then resolve the name of the target (table / view / package / etc.). Public synonyms have a slightly larger performance gain (2 times from non-public synonyms). However, this is somewhat controversial; see this Ask Tom article for more information. However, if you did not push your database to the limit, I would not worry about that.

Answer 5

if you expect the tables to actually move, be safe, use fully qualified names.

Answer 6

There is always more than the answer to any question. That is why “it depends” is always the correct answer. I find that when you stay true to your intentions and develop, you will eventually come forward.

Schemas are designed to isolate the namespace. For example, when developing a SaaS system, schemes can be used very effectively to share problems for multiple tenants. Schemes can also be used to separate a production test or data from one application from another. And this is not only for tables, but also for stored procedures and all other database objects.

DB2 provides a special register called CURRENT SCHEMA, which allows you to encode the names of unqualified objects, but switch to the correct scheme by simply setting the special CURRENT SCHEMA register as follows:

 SET CURRENT SCHEMA = 'Production' 

This, in my opinion, is much better than using synonyms.