Hundreds of aliases/synonyms vs database tables' fully qualified names

Question

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

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 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?

Answer 1

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.

Where the no of tables are large, you really need to use prefixes. Not Sales.Customer but REF_Customer.

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.

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.