Using SP_

Question

Using SP_

Viewing 15 posts - 16 through 30 (of 52 total)

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

jcrawf02 (12/14/2011)
quote]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?

select * from dbo.MSreplication_options

This statement only works in the master database.

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

Cliff Jones SSChampion Points: 10547 More actions · Answer 2

Cliff Jones

SSChampion

Points: 10547

December 14, 2011 at 7:57 am

#1421318

Interesting behavior. Thanks for the question Ron.

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 3

Good question. Got it right for the wrong reason, learned something. Thanks.

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 4

Hugo Kornelis (12/14/2011)
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.

Yep, you're right, I was confused. Thanks for setting me straight! 🙂

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

Ken SSCoach Points: 17191 More actions · Answer 5

Ken

SSCoach

Points: 17191

December 14, 2011 at 9:06 am

#1421394

Nice question and interesting behavior. Thanks!

Mark Cowne One Orange Chip Points: 26966 More actions · Answer 6

Interesting question, thanks!

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Revenant SSC-Forever Points: 42467 More actions · Answer 7

I like to ask this one when I am interviewing, so it was easy. Thanks!

OzYbOi d(-_-)b SSCarpal Tunnel Points: 4054 More actions · Answer 8

Thanks for the question and the discussion this morning.

TomThomson SSC Guru Points: 104773 More actions · Answer 9

Good question, and some interesting points in the discussion.

This behavious does seem somewhat bizarre - it would make more sense if the object_id function obeyed the same rules as drop - but it's not the only bizarre thing in T-SQL by any means.

Tom

drew.allen SSC Guru Points: 76997 More actions · Answer 10

Hugo Kornelis (12/14/2011)
* 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).

The following is from ALTER USER (Transact-SQL)

Caution:
Beginning with SQL Server 2005, the behavior of schemas changed. As a result, code that assumes that schemas are equivalent to database users may no longer return correct results.

The default DEFAULT_SCHEMA for a user is now dbo and it can be changed by specifying the DEFAULT_SCHEMA in the ALTER USER statement.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 11

Nice question Ron.

Have we made a case for sp_Tally?

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Ron McCullough SSC Guru Points: 63877 More actions · Answer 12

Nice question Ron.
Have we made a case for sp_Tally?

Put my Tally table into the model DB so it now appears in any new DB created

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Paul White SSC Guru Points: 150468 More actions · Answer 13

Hugo,

I can't find any BOL documentation, but you should find this four-part blog series on the SQL Programmability & API Development Team Blog answers most, if not all of the questions you asked:

http://blogs.msdn.com/b/sqlprogrammability/archive/2006/04/03/567643.aspx

http://blogs.msdn.com/b/sqlprogrammability/archive/2006/04/03/567648.aspx

http://blogs.msdn.com/b/sqlprogrammability/archive/2006/04/03/567660.aspx

http://blogs.msdn.com/b/sqlprogrammability/archive/2006/04/03/567663.aspx

Cheers,

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 14

BWAA-HAAA!!! Judging by the fact that more people got the wrong answer than the correct one, I'd say you just taught a whole bunch of people a lesson, Ron. Well done! 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ron McCullough SSC Guru Points: 63877 More actions · Answer 15

Jeff Moden (12/14/2011)
BWAA-HAAA!!! Judging by the fact that more people got the wrong answer than the correct one, I'd say you just taught a whole bunch of people a lesson, Ron. Well done! 🙂

Coming from some one like yourself and your anti-RBAR, quirky update and so many other articles which teach so many so much (and I am one of those you have taught) I thank you for the compliment

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]