June 20, 2014 at 7:55 am
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.
June 20, 2014 at 8:27 am
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
June 20, 2014 at 8:42 am
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.
June 20, 2014 at 11:05 am
Too easy, but it's Friday 🙂
Igor Micev,My blog: www.igormicev.com
June 20, 2014 at 1:17 pm
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
June 21, 2014 at 9:37 am
Trick question as there was no trick in the question:-D
Thanks Andy!
😎
June 22, 2014 at 3:32 am
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
June 22, 2014 at 3:32 am
Eirikur Eiriksson (6/21/2014)
Trick question as there was no trick in the question:-DThanks Andy!
😎
🙂
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
June 22, 2014 at 3:36 am
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
June 23, 2014 at 1:06 am
Great question, thanks!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 23, 2014 at 1:50 am
Thank you Andy.
Teaching point taken.
June 23, 2014 at 2:35 pm
nice and easy one..
thanks andy.
June 25, 2014 at 12:00 am
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
July 28, 2014 at 6:54 am
pchirags (6/23/2014)
nice and easy one..thanks andy.
+1
Thanks
August 20, 2014 at 8:41 am
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