Poecilonym Madness!

Question

Poecilonym Madness!

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

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

sknox SSChampion Points: 12388 More actions · Answer 1

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.

Sean Pearce SSCoach Points: 15999 More actions · Answer 2

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]

Mighty SSCrazy Eights Points: 9063 More actions · Answer 3

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.

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 4

Too easy, but it's Friday 🙂

Igor Micev,My blog: www.igormicev.com

TomThomson SSC Guru Points: 104773 More actions · Answer 5

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

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 6

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

Thanks Andy!

😎

Hany Helmy SSChampion Points: 13921 More actions · Answer 7

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

Hany Helmy SSChampion Points: 13921 More actions · Answer 8

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

Hany Helmy SSChampion Points: 13921 More actions · Answer 9

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 10

Great question, thanks!

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

F. van Ruyven SSCommitted Points: 1507 More actions · Answer 11

F. van Ruyven

SSCommitted

Points: 1507

June 23, 2014 at 1:50 am

#1723909

Thank you Andy.

Teaching point taken.

PChiragS SSCarpal Tunnel Points: 4965 More actions · Answer 12

PChiragS

SSCarpal Tunnel

Points: 4965

June 23, 2014 at 2:35 pm

#1724197

nice and easy one..

thanks andy.

ameshjeram SSC Veteran Points: 295 More actions · Answer 13

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

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 14

pchirags (6/23/2014)
nice and easy one..
thanks andy.

+1

Thanks

Ashok Teotia SSCrazy Points: 2407 More actions · Answer 15

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

😉