The Future Synonym

Question

The Future Synonym

Steve Jones - SSC Editor

SSC Guru

Points: 734418
More actions
March 1, 2021 at 12:00 am

#3846847

Comments posted to this topic are about the item The Future Synonym

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Bob Razumich SSCrazy Points: 2136 More actions · Answer 1

Interesting feature that I didn't know about before. I would suppose it would make it easier to select data from a table with a long, confusing or hard to spell name - my brain came up with MyDB.dbo.supercalifragilisticexpialidocious, which I freely admit I cut and pasted after searching for that word. Happily it appears that there is a sys.synonyms view that allows finding all on the current database and what objects they are referring to. Nice bit of new knowledge for a Monday morning.

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 2

Interesting, so they're less reliable than views.....

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 3

These are good for cross database dependencies, separating out the linkage between objects. This also lets you move the other object to a new db or a new instance and only edit the synonym instead of a number of other objects.

I don't know these are less reliable, but you don't have to worry about chronology if you are moving something, or you have to recreate an environment. That's a good thing.

Of course, test them when you create them and the other object does exist to be sure you haven't typo'd something.

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 4

To me, it's a bit funny that someone would say that they're "less reliable" than views... they are one of the few things that operate precisely the way they're documented and will allow you to do complete database builds one database at a time without having to stand up all the databases and then go back to them all to build the synonyms. It's something that you can actually "rely on". 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 5

/shrug Views at least validate themselves at creation time and if you do schema binding should stay valid.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 6

An interesting use of synonyms provides flexibility for source or target table names in scripts and procedures.

DECLARE  @InputTable varchar(100) = 'ContentX' -- this would be a parameter to the procedure

-- the procedcure starts with 
DECLARE  @dynSQL varchar(1000) = '
DROP SYNONYM [dbo].[InputTable];
CREATE SYNONYM [dbo].[InputTable] FOR ' + @InputTable

exec (@DynSQL)

-- procedure code simply refers to the synonym
select top 10 * from dbo.InputTable

By creating a synonym called something like "InputTable" for the table to be read, we can (obviously) write a procedure using the name of the synonym instead of the actual table name. But by beginning the procedure with a simple "DROP/CREATE SYNONONYM" in dynamic SQL, you can pass in the name of your input table as a parameter. This is much simpler than coding/ debugging large chunks of code in dynamic SQL to replace table names with a variable.

Obviously, it is up to the developer to make sure the input tables are all identical in terms of column names and datatypes. This technique has only been applied to simple imports and merges, so the inevitable recompile time is minimal. Still it has been useful when importing tables from other systems which are identified either by dates being included in their table names, or by being stored in different databases.

This is just another layer of abstraction, the same thing could be done with a view, instead of a synonym.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills