Usage of
-
I know part of my answer is in these messages threads somewhere but lost specific thread. So here is the specific sub-question.
I know the usage of "sp_" (SP underscore) for user stored procedures causes SQLServer to first look in Master database wasteing time etc. However is this also true for just "sp" (SP no underscore)? Review of Master stored procedures only show "sp_" and "xp_" prefixes.
For user stored procedures I want to use "up" or "usp" prefix. My users are used to using the "sp" and do not want to change. They are accepting of "uf" for user functions. If "sp" works like "sp_" then this is a very good argument for my choice. Else we will be using "sp"
SmithDM
Edited by - smithdm on 10/29/2003 11:07:50 AM
SmithDM -
The answer is no it does not work like sp_. Here is a bit of info on sp_ http://www.sqlservercentral.com/columnists/jtravis/sp_performance.asp
-
SQL looks sp_ in the MasterDB cache and not actual SysObjects (Am I correct?)
SPXXX does not cause as cache miss so I assume that it does not check master first where as SP_XXX did. Everytime.
Cheers,
Crispin
ps:
Interesting article by Brian.
http://www.sqlservercentral.com/columnists/bkelley/procedurecache.asp#Recompilation">
http://www.sqlservercentral.com/columnists/bkelley/procedurecache.asp#Recompilation
Edited by - crappy on 10/28/2003 11:01:54 AM
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
Correct, the spXXX behaviour and sp_XXX behavior are different. spXXX is treated like uspXXX so long as spX isn't sp_ (just to be clear).
sp_ behavior:
It looks in syscacheobjects for a hit with the object name in the master database first.
Not finding it, it'll put a [COMPILE] lock on the object, then do a second search through syscacheobjects. The [COMPILE] lock is an exclusive lock, BTW.
This second search should find the object if it's in the local database or if you've used a three part naming convention. The [COMPILE] lock will be released and if the execution plan can be reused it will be.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley -
Thanks all. This was one of those CYA things where I was not sure how to setup the proper proof.
SmithDM
SmithDM -
Actually I don't really understand this thing using prefixes.
Neither will you get any value added from doing so nor will it become more self explaining. You know what type of object this is, because you've created it. And if you are uncertain about it, you can always take a look in EM.
Just my $0.02 cents
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
My .02 worth...I always (at my previous
employer) used a prefix. tb_ for tables,
usp_ for stored procs and vw_ for views.
Comes in handy when you do a sp_help and
list all objects, you know what is what.
I don't have this at my new employer and
bascially, the naming conventions of all
objects are inconsistent and basically a
mess! Frustrating...
Again...my .02 worth.
-
No ultimate answer to this question, I guess.I prefer the other way, but 'it depends...'.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
sorry, hit too early...
I also prefer the use of OBJECTPROPERTY(blah, blah) to determine the type of an object
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Do I detect I started something here, use of standardized naming conventions etc?
Personally I am a strong believer in standardized naming converntions with object type identifiers - either as prefix or suffix. Compliments of Microsoft and Oracle object identity prefixes are very common standards. Specific values for each object type just have a tendencey to change over time.
SmithDM
SmithDM -
Naming conventions...no thanks.
I started as a C/C++ programmer and did not use the "conventions". I switched over to Visual Basic and Access where the prefix convention is prevelent. I never understood the need to say "tblEmployees" when everyone knows it is a table, for example.
Just my 2 cents.
Dr. Peter Venkman: Generally you don't see that kind of behavior in a major appliance.
Patrick
Quand on parle du loup, on en voit la queue
-
quote:
Naming conventions...no thanks.I never understood the need to say "tblEmployees" when everyone knows it is a table, for example.
Not sure, but there *might* be a difference between a programming language and database objects.
I know, I have posted this before, but I highly recommend this reading
http://mindprod.com/unmain.html
...just before one's co-developers are about to kill you
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Just read my own link
quote:
When I use a word," Humpty Dumpty said, in a rather scornful tone, "it means just what I choose it to mean - neither more nor less."nice one!
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Going out on a limb here to contradict some people who are obviously more experienced than I...where's that damn ripcord!
The posters in this thread seem to assume a few things:
1) You will never die, or get a better job elsewhere.
2) You will never be promoted to a position where you will not have time to micromanage all the other people writing queries, etc.
Those two were a little tongue in cheek, but a more important example is a shop that is using a CRM/SFA/ERP/(ad nauseum). These tools use scripts to generate all the systems objects, and use their own prefix. When your development team customizes the app, they use a different prefix to show that their new objects are not out of the box. Likewise, prefixes for stored procedures that hint to their general use are very helpful (i.e. reporting, internal processing, app business logic, etc. ).
I think prefixes should be used as the first "Meta" comment, so when I'm looking for an object (that perhaps someone else wrote) I can tell without calling up the script whether or not this one MIGHT be what I'm after. It's true, "tbl_" doesn't say much, but it does at least it's not a "vw_" or something that Peoplesoft wrote. Your developers need to know that, and if they can know it at a glance they can continue their work.
Of course, good external documentation would alleviate this pain as well, but...'nuff said on that.
Go easy on me,
Jeff
-
tbl may not say much, if all your tables were to start with that, although it certainly distinguishes them from the system tables. But when you have tlkp, tbl, trel to indicate lookups, entities, and relation tables, you are a step further towards a uniform understanding among your team.
No ultimate answer to this question, I guess.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply