Poecilonym Madness!

  • The final select returns a row containing Steve is correct because the synonym is pointed at the view called Test that points to the Test2 table. Depending on your outlook that may or may not be intuitive!

    A simple way to look at it is to think of a synonym as a token replacement on future SQL statements. This both explains why a synonym doesn't need to be bound to an existing object, and why changing the schema can change what it points at without breaking anything.

  • Mighty (6/20/2014)


    Raghavendra Mudugal (6/20/2014)


    Well... the exchange of thoughts on collation is been going on from very long back, but at the end we all kind of nodded that if the mention of collation is not there then go for default "Latin1_General_CI_AI" (this is just an example and not for debate) - Or in other words drop the collation from the criteria. Sorry for your loss (i mean for points..) Now you know. 🙂

    Issue with this is that there is no "default collation". Depends on the language/local of the system on which SQL Server has been installed and on the version of SQL Server.

    The default collation is based on the Windows system locale but none of the defaults are case sensitive. Find the paragraph [Default Collations in SQL Server Setup] in the following MSDN article.

    http://msdn.microsoft.com/en-us/library/ms143508(v=sql.105).aspx

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • Sean Pearce (6/20/2014)

    The default collation is based on the Windows system locale but none of the defaults are case sensitive. Find the paragraph [Default Collations in SQL Server Setup] in the following MSDN article.

    http://msdn.microsoft.com/en-us/library/ms143508(v=sql.105).aspx

    Good point! Missed that nugget of info.

  • Too easy, but it's Friday 🙂

    Igor Micev,My blog: www.igormicev.com

  • Nice question.

    Would it have been a better question or worse one if the create synonym statement had been the first in the batch, ie before thecorresponding table was created, so that there was the issue of whether creating a synonym checked that the target was valid? Without that there's really nothing (given we assume one of the default collations for SQL 2008R2, SQL 2012, or SQL 2014) that might make the create synonym statement fail - but of course it's pretty obvious that if that check isn't made the synonym won't be doing any schema binding as opposed to lexical binding, and then the drop table would have nothing to suggest it might fail. So changing the position of create synonym in the order of statements would just change which of the two potentially interesting pairs of questions isn't a free ride.

    Tom

  • Trick question as there was no trick in the question:-D

    Thanks Andy!

    😎

  • Ed Wagner (6/20/2014)


    This is a nice, straightforward question. A good way to end the week. Thanks, Andy.

    + 1, agree.

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • Eirikur Eiriksson (6/21/2014)


    Trick question as there was no trick in the question:-D

    Thanks Andy!

    😎

    🙂

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • david.gugg (6/20/2014)


    Tricky, I failed to realize the synonym would be pointing to the view instead of the table that had been dropped.

    Great question!

    Nice, hah 🙂

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • Great question, thanks!

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Thank you Andy.

    Teaching point taken.

  • nice and easy one..

    thanks andy.

  • Hi

    'The drop table statement fails is incorrect. This one probably has you thinking about whether the synonym prevents the drop from occurring (similar to schema binding) but it does not, so The drop table statement succeeds is correct. '

    this is not true - as the synonym has first preference - unless we drop synonym first.

    secondly, the table created is Test and not test - so in case sensitive SQL server set up - it will not work.

    Regards

  • pchirags (6/23/2014)


    nice and easy one..

    thanks andy.

    +1

    Thanks

  • Tricky Q?. I failed for sixth option where I missed the view is created with same name as table and synonym is now referencing view..

    😉

Viewing 15 posts - 16 through 29 (of 29 total)

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