What are the possible restrictions with using synonyms on sql server (2005/2008) please?

Dear Experts,

What are the possible restrictions when using synonyms on sql server (2005/2008) please?

In particular:
1) Can you have referential integrity between a 'real' table and a synonym table?
2) Are you likely to have any issues if you use a synonym instead of a 'real' table as a dimention in reporting services or analysis services (cube)?
3) If you update your source table (just the type on a field for instance) is that reflected straight away, or does the synonym require refreshing (same problem as with views)?
4) Anything else I haven't thought of here?

One thing I am aware of - is that you cannot chain them.

Your experience is probably more valuable than general links, and I would be grateful if you could answer at least some of these questions.

Thank you.

CallConnectionAsked:
Who is Participating?
 
Megan BrooksConnect With a Mentor SQL Server ConsultantCommented:
1. I don't *think* you can declare referential integrity between different databases.

2. Not that I am aware of. I use views to feed my dimensions, and some of those views reference synonyms.

3. There is nothing to refresh with a synonym.

4. For the most part, using a synonym is logically equivalent to referencing a database directly from another database using the dbname.schema.object syntax. The only differences I have encountered have been with the way synonym security works.
0
 
CallConnectionAuthor Commented:
Thank you for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.