September 12, 2014 at 9:14 am
.
September 12, 2014 at 11:28 am
mister_zed (9/12/2014)
kenambrose (9/11/2014)
I can't recall a single environment in the past 10 years I have worked in that allowed OPENQUERY. It was always considered a no no due to security risk....Unfortunately, I happen to work in such no no No NO environment. There is no need - users say - to develop a proper GUI, as we can use DBA tools and write SQL queries against the database. Madness...
I've seen scenarios where building and maintaining a GUI for updates was also overkill. For example, when we use our own custom configuration tables and we need to change a value.
For that we've provided excel spreadsheets to provide an interface to specify the changed values, and a simple excel macro to generate insert/update statements to perform the change in the DB.
Users had no problem with it and as a bonus it saved a whole history of the data values in the excel rows...
September 12, 2014 at 1:48 pm
Just my 2 cents, and it has been a while since I attempted to use a synonym. Thought it would be useful, created some for test, then tried to use them in 3rd party BI tools, report writers, etc. Turned out, I could get a list of tables and views, but not synonyms, so couldn't use them for that purposes. At that point, they became less useful. Now, this was at the early stages of them being introduced, and maybe newer versions of these type of tools can use them. YMMV.
Leonard
September 12, 2014 at 9:26 pm
Lawrence Moore (9/12/2014)
So can someone explain how a synonym is superior to a view?
A SYNONYM can be to any object, including stored procedures and functions. I don't think you can create a view on a stored procedure. 🙂
Sincerely,
Daniel
September 12, 2014 at 9:38 pm
Books Online 2012:
The base object need not exist at synonym create time. SQL Server checks for the existence of the base object at run time.
Synonyms can be created for the following types of objects:
Assembly (CLR) Stored Procedure
Assembly (CLR) Table-valued Function
Assembly (CLR) Scalar Function
Assembly Aggregate (CLR) Aggregate Functions
Replication-filter-procedure
Extended Stored Procedure
SQL Scalar Function
SQL Table-valued Function
SQL Inline-table-valued Function
SQL Stored Procedure
View
Table(1) (User-defined)
(1) Includes local and global temporary tables
Four-part names for function base objects are not supported.
Synonyms can be created, dropped and referenced in dynamic SQL.
Sincerely,
Daniel
September 13, 2014 at 4:06 am
At my shop we have a great use case for synonyms.
It's actually an Oracle database but the principle is identical.
We have a line of business enterprise-y app which has a database. It is quite good at some stuff, awful at others (it's a corporate treasury management system). We need to extend the system in a way which is largely transparent to the user and hides the awfulness. We are not willing (and the vendor does not support) adding to or modifying the production schema.
Our DBAs created a 'parallel' schema, which contained synonyms referencing every object in the app's schema, and we went to town. We have access via the synonym to every object in the application schema. We just need to avoid naming any of our objects the same as an object in the app schema, which is easy enough because we have a strict naming convention and they don't.
September 15, 2014 at 2:24 am
JediSQL (9/12/2014)
Lawrence Moore (9/12/2014)
So can someone explain how a synonym is superior to a view?A SYNONYM can be to any object, including stored procedures and functions. I don't think you can create a view on a stored procedure. 🙂
That's true. However, I can create a stored proc that just includes a sub-call to the original.
Anyway - you have convinced me to take another look at synonyms...:-P
September 22, 2014 at 7:29 am
For synonyms constructed on tables, no changes are required to the synonym to reflect changes in columns on the underlying table. The synonyms adjust automatically when adding or removing columns on the table upon which it is based.
For views, changes are often required to reflect changes in columns on the underlying table.
If you remove columns on the table, then you have to alter your view to remove the columns in the view as well. If you add a column on the table, then you have alter your view to see the new column.
However on the "downside" of synonyms on tables, I have not been able to make them linkable from MS Access which would be great if I could. They just do not show up at all in MS Access. I can link to views from MS Access.
June 27, 2015 at 4:41 pm
mister_zed (9/11/2014)
And the third: can you update views in SQL Server?
Interesting question, because this is one downfall of synonyms, you have to DROP and recreate synonyms, but a view you can alter in place (or refresh). If you meant update data through a view, you certainly can depending on the underlying query.
I'm curious of the true performance overhead of a simple view (select allcolumns from table) vs a synonym redirect?
To me, one of the BIG values of views (and not allowing direct table access) is refactoring in the future. How many times has a table gotten too deep or wide and you want to refactor, but the debt of changing EVERY reference to the table is too high. If a view had been used from the start, it becomes trivial. And in extreme cases, an instead of trigger solves complex insert problems.
June 28, 2015 at 2:31 pm
We just implemented a new scenario for SYNONYM use (at our location). We use the Ektron content management system. Ektron has a product, eSync, that copies Ektron content from one server to another. We have our content in SQL Server databases. To eSync between servers, the Ektron database for each server has to have a different name (totally annoying). We have processes that need to peek into the Ektron databases. Thus, a stored procedure on one server that references the Ektron database will need to use a different database name than the same stored procedure on a different server, unless, of course, one uses a SYNONYM. So, in each of our databases that reference the Ektron database we create a SYNONYM to each referenced Ektron object, with the server-specific database name, and then we can deploy the identical stored procedures to each our database on each server. Problem solved!
Sincerely,
Daniel
June 24, 2016 at 12:28 am
Synonyms are useful for emulating operating system environment variables.
Just as you might assign 'sales' to be 'sales2016_q3.dat' at the OS level, you can do the equivalent in SQL by synonyms.
They also come into their own when you have different names in production and development.
June 24, 2016 at 1:21 am
I'm with 'Mighty'. Friends don't let friends use synonyms.
June 24, 2016 at 1:37 am
It's a pity that EF does not support synonyms
June 24, 2016 at 3:56 am
Nice article. When I used synonyms for tables,scalar functions and table functions. It was important to me to include the schema name as part of the definition. It worked across environments as well as federated database model in developing queries.
CREATE SYNONYM [highlight="#ffff00"]<schema_name>.<object_name>[/highlight]
FOR <server_name>.<database_name>.[highlight="#ffff00"]<schema_name>.<object_name>[/highlight]
June 24, 2016 at 6:26 am
If you live in a universe where you control database and scheming naming conventions, then I imagine you don't need to synonyms.
However, I don't.
For example:
We have written multiple interfaces between different Packaged Software applications.
Each one sits in its own database.
Because multiple environments sit on the same server, I have examples like this:
Server1/VendorADatabase
Server1/VendorBDatabase
Server2/VendorATestDatabase
Server2/VendorADevDatabase
Server2/VendorBTestdatabase
Server2/VendorBDevDatabase
To both create interfaces and reports, I need to reference multiple databases. I don't want to hardcode database names in my code. Synonyms (and views) solve the issue.
To make life easier, we have one stored procedure that automatically generates the appropriate synoyms and view each time a database gets refreshed. I posted on this a while ago:
For those who say there's no need for this, please explain how you'd solve this problem.
Viewing 15 posts - 46 through 60 (of 80 total)
You must be logged in to reply to this topic. Login to reply