October 22, 2009 at 5:11 pm
hello. i've scripted a db and have been updating field names, etc.
first thing is, at the top of the file, two schemas and two users were created. each schema has a corresponding user (or vice-versa) with the same name. i'll come back to this.
secondly, i'm looking at the stored procedures and they all seem to start with 'sp_'. i say "seem to" because they actually look like one or the other of these:
CREATE PROCEDURE [SchemaOrUser].[sp_blah_blah]
or
CREATE PROCEDURE [dbo].[sp_blah_blah]
'SchemaOrUser' matches the schema name and user created at the top of the file.
so, a few questions:
- schema and user are the same. which one does the [SchemaOrUser] refer to?
- what does that 'prefix' do? is it required? (same thing with [dbo]).
- where does the stored procedure's name really start? with sp_ or [SchemaOrUser] ? i've read several times that sps really shouldn't start with sp_ (reads the system tables first or something) so i'd like to change them, if i should.
October 22, 2009 at 10:23 pm
Hi there
- schema and user are the same. which one does the [SchemaOrUser] refer to?
It refers to the schema
- what does that 'prefix' do? is it required? (same thing with [dbo]).
It enables you to partition (bad word when it comes to SQL related stuff) your DB to few logical 'zones' where each zone can refer to objects in another if the user has the right to do that. It is just like having few different databases in one
- where does the stored procedure's name really start? with sp_ or [SchemaOrUser] ?
the name starts with 'sp_'. the prefix (before the last dot) is the schema name
i've read several times that sps really shouldn't start with sp_ (reads the system tables first or something) so i'd like to change them, if i should.
True... Prefixing your SPs with 'sp_' causes SQL to lookup in its internal list of procedures first and only then in the complete list. If you name it like this, you lose some CPU cycles and ms looking up in the wrong place...
October 23, 2009 at 1:05 am
great. thanks for the help.
- w.
edit:
ah, what do you use, if anything, to prefix sps? i think i've seen sproc_ before. any suggestions? (i'll do some searching too.) tnx again.
October 23, 2009 at 2:09 am
My DBs usually contain 2 kinds of objects:
1. Product objects - they are copied from one project to another - are prefixed according to their logical job: pACL_ for permissions, pCNT_ for content, etc.
2. Custom/ Project specific objects - are prefixed with p_ and acronym of project name. For example, a candidate search procedure will ben named: p_CND_SEARCH_CANDIDATE
This way I can "sort" them visually, and the ones you need pop easily. Also, It's a great way to be able to enum all your objects, as in queries over system object views.
October 23, 2009 at 2:53 am
wazz (10/23/2009)
ah, what do you use, if anything, to prefix sps? i think i've seen sproc_ before. any suggestions? (i'll do some searching too.) tnx again.
In most of cases that I've seen and worked, it would be starting with USP... (user stored procedure)
---------------------------------------------------------------------------------
October 23, 2009 at 4:06 am
thanks.
i've done some searching and see there's quite a discussion going on with this and great info. one link (started in 2002!): http://www.sqlservercentral.com/Forums/Topic8041-65-1.aspx
i think usp might do for my needs. i also like this one (found somewhere in the link above):
I use spA_ append, spD_ delete, spU_ update. I have required that submittal forms be sent in when changes are made or objects created.
Also at the top of every SP i have started comments blocks:
CREATE PROCEDURE spA_tblTest
/*
Created: 11/10/2002
Created By: AJ
Purpose(s): Append data into tblTest
--
Modified: 11/12/2002
Modified By: AJ
Purpose(s): Added additional functionality....
*/
AS
this way I can see whodunit and why.
sorry, don't remember off-hand who posted that. i'll do some more digging and get some ideas to talk over with someone here who is giving me a hand with this. i'm (we're) updating a web app pretty much from scratch that hasn't been updated very much for 5 years. :blink:
October 23, 2009 at 4:54 am
Good luck there!
Just don't forget that besides good naming convention for your procedures, T-SQL itself has undergone a long way and is very different than 5 years before, so have a look at its content, too.
October 23, 2009 at 5:27 am
dbo.benyos (10/23/2009)
Good luck there!Just don't forget that besides good naming convention for your procedures, T-SQL itself has undergone a long way and is very different than 5 years before, so have a look at its content, too.
True, and if you have any plans of upgrading your database to sql 2005 then its a must!
---------------------------------------------------------------------------------
October 23, 2009 at 8:04 am
we'll be sure to go through all the tsql. and the .net needs upgrading too; it was built on v1! it doesn't even have a master page. user controls on top of user controls on top of... everywhere. it's seems to be quite well made considering.
October 23, 2009 at 8:23 am
Personally, I've never seen a value to naming procs with prefixes like that.
I prefer to start their names with the primary table they deal with.
If, for example, I have a Customers table, then every proc that primarily deals with that table would be named like "CustomersInsert", "CustomersDelete", etc. That way, when I look in the object explorer, they're all in the same place. Makes it much easier when I need to find them.
Prefixing by function (insert, update, delete, select) generally ends up with all the insert procs sorted together. I've never had that be helpful, more often it's annoying.
Nor have I ever been in a situation where I saw a piece of code like this:
exec dbo.CustomersInsert @params, @MoreParams;
and couldn't figure out what type of object CustomersInsert was. Don't have to think to myself, "well now, is that a proc or a table?" Adding something like "proc_" or "usp_" to the beginning doesn't help. Doesn't hurt much, just makes them a bit more confusing to read till you know what the house rules on naming are, but if it doesn't help, I don't see a value to it.
The only place I've ever seen any value to that is differentiating views from tables. I can see a slight value there. Might save a couple of seconds somewhere along the way.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply