December 13, 2011 at 9:06 pm
Comments posted to this topic are about the item Using SP_
December 14, 2011 at 12:35 am
Great question!
I have a question:
why doesn't the second 'drop table' statement drop the table in the master database?
Won't the statement look for the table in the master database, just as the select statement?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 14, 2011 at 12:36 am
An interesting find, Ron.
What's even more interesting is that you get data from the table in master even if the current database has a different table with the same name. Not really surprising (as this is the intended behaviour for stored procedures), but disturbing nonetheless.
EDIT: I have to retract the above. I don't know what went wrong with my first test, but I'm not able to reproduce it. Instead, I now see consistently that SQL Server will first search the current database for a table of the given name, and then search master. This actually makes the behaviour even more weird, since (according to the documentation for SQL Server 2005 - I see the relevant comments have been removed from the SQL 2008 docs, which is even more disturbing) stored procedures with a name starting with sp_ are searched in master first, user DB second.
I noticed that there are no documentation references in the explanation. I know that the special behaviour of names starting with sp_ has been well documented for stored procedures. But have you found any documentation that supports your claim that tables and views act the same way?
December 14, 2011 at 12:43 am
Koen Verbeeck (12/14/2011)
Great question!I have a question:
why doesn't the second 'drop table' statement drop the table in the master database?
Won't the statement look for the table in the master database, just as the select statement?
Because the drop table statement is never executed. 🙂 Remember the IF statement?
Edit: Try this to see that DROP TABLE actually works:
USE SomeDB
GO
CREATE TABLE master.dbo.sp_mytable (a INT)
GO
DROP TABLE sp_mytable
This time it should work.
Best Regards,
Chris Büttner
December 14, 2011 at 12:49 am
Christian Buettner-167247 (12/14/2011)
Koen Verbeeck (12/14/2011)
Great question!I have a question:
why doesn't the second 'drop table' statement drop the table in the master database?
Won't the statement look for the table in the master database, just as the select statement?
Because the drop table statement is never executed. 🙂 Remember the IF statement?
Exactly - you beat me to replying to Koen, because I had to edit out the inaccuracy from my previous post first.
DROP TABLE sp_whatever; will drop the table from the current DB first, and from master if there's no such table in the current DB. Here is a fun repro to try, where you can drop the same table twice without getting an error:
USE master;
CREATE TABLE sp_x (Col1 int);
INSERT INTO sp_x VALUES (1),(2);
go
USE tempdb;
CREATE TABLE sp_x (Col2 int, Col3 int);
INSERT INTO sp_x VALUES (1,2),(2,3);
SELECT * FROM sp_x;
DROP TABLE sp_x;
SELECT * FROM sp_x;
DROP TABLE sp_x;
go
December 14, 2011 at 1:01 am
Great question. 🙂
M&M
December 14, 2011 at 1:09 am
Christian Buettner-167247 (12/14/2011)
Koen Verbeeck (12/14/2011)
I have a question:why doesn't the second 'drop table' statement drop the table in the master database?
Won't the statement look for the table in the master database, just as the select statement?
Because the drop table statement is never executed. 🙂 Remember the IF statement?
Ah yes. D'uh. More coffee. Now. 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 14, 2011 at 1:54 am
Nice question Ron.
Got it wrong though - will have to try and remember that one.
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
December 14, 2011 at 1:55 am
I suppose the clue was in the heading 'Using SP_', but as usual I didn't read the question closely enough.
Nice one bitbucket!
December 14, 2011 at 2:14 am
Nice one, Thank you
Iulian
December 14, 2011 at 2:26 am
Nice question, but not sure about the explanation. Surely the crucial point is that object_id('sp_mytable') is null, even though a select against that table recognises it (and a drop would drop it)?
December 14, 2011 at 3:55 am
Koen Verbeeck (12/14/2011)
Christian Buettner-167247 (12/14/2011)
Koen Verbeeck (12/14/2011)
I have a question:why doesn't the second 'drop table' statement drop the table in the master database?
Won't the statement look for the table in the master database, just as the select statement?
Because the drop table statement is never executed. 🙂 Remember the IF statement?
Ah yes. D'uh. More coffee. Now. 😀
In that case, while you drink your coffee I would rephrase the question:
Why doesn't the OBJECT_ID function return the ID of the table in the master database? It seems a bit inconsistent...
Also, thanks for a very interesting question.
December 14, 2011 at 6:43 am
Because, by default, OBJECT_ID() assumes current database.
December 14, 2011 at 7:01 am
Hugo Kornelis (12/14/2011)
An interesting find, Ron.What's even more interesting is that you get data from the table in master even if the current database has a different table with the same name. Not really surprising (as this is the intended behaviour for stored procedures), but disturbing nonetheless.
EDIT: I have to retract the above. I don't know what went wrong with my first test, but I'm not able to reproduce it. Instead, I now see consistently that SQL Server will first search the current database for a table of the given name, and then search master. This actually makes the behaviour even more weird, since (according to the documentation for SQL Server 2005 - I see the relevant comments have been removed from the SQL 2008 docs, which is even more disturbing) stored procedures with a name starting with sp_ are searched in master first, user DB second.
I noticed that there are no documentation references in the explanation. I know that the special behaviour of names starting with sp_ has been well documented for stored procedures. But have you found any documentation that supports your claim that tables and views act the same way?
I don't have rights to create in master here, so I'll have to play when I get home, but I was under the impression that SQL always looked in master first for sp's and tables if you do not specify a schema, is that only true for sp_ prefixes?
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
December 14, 2011 at 7:26 am
jcrawf02 (12/14/2011)
I was under the impression that SQL always looked in master first for sp's and tables if you do not specify a schema, is that only true for sp_ prefixes?
I tihnk you are confusing two things:
* Schema - when you don't specify a schema when referencing an object, SQL Server will, if I recall correctly, check your default schema first (which by default is equal to your username), then the generic default schema (dbo by default - and I'm not sure if this can be changed).
* Database - when referencing an object without qualifying it with a database name, SQL Server will normally look for it in the current database only. The only exception in this case is for objects with a name starting with "sp_". For tables, my test shows that these are searched in the current database first, then (if not found) in master. For stored procedures, the documentation and the code snippet in Books Online say that these are searched in master first, and only in the current database if they don't exist in master.
Viewing 15 posts - 1 through 15 (of 52 total)
You must be logged in to reply to this topic. Login to reply