October 3, 2005 at 10:14 am
So whats so extreme about conventions for naming of 'system created constraints'?
It's damnably hard work to issue database upgrade scripts from Dev --> QA --> Live without having standard, explicit constraint names...
October 3, 2005 at 11:02 am
Wangkhar,
With the correct scripting or migration tools, explicit constraint names aren't necessary. I don't object to constraint naming conventions - but I find that for a team of developers and dbas, they are difficult to enforce. I try to focus on tables, views, stored procedures, and other major object types instead. If you have the environment control to keep constraint-level names standardized - I envy you.
October 3, 2005 at 11:38 am
I know it's almost universal to have Verb-first, then the Object as a naming convention:
But I use the reverse -- Object-first, then Verb -- for a specific reason
The reason is that when they are listed alphabetically (as they are everywhere) I want to see all of the User-oriented sprocs together, then all of the Inventory-oriented sprocs together, etc.; rather than all of the Delete sprocs together, then all of the Insert sprocs together, etc.
October 3, 2005 at 11:43 am
As Sushila said it may be obvious to some that it is sensible to move column names into variables of the same name prefixed by @ but I have seen yards of off-shore code which does not follow this convention. Also in the interest of laziness tables like Purchase_order_header (I wonder what that might be?) are called something like Purordhd. A good example of this is Sage, a leading UK accountancy package. It has table names like stockm uses tables as if they were flat files and does not have any constraints. There is also ignorance about the use of triggers, functions and transaction processing.
October 3, 2005 at 12:17 pm
The prefix is helpful, but only in certain conditions.
If you're using the Query Analyzer object viewer or using Enterprise Manager to browse for your stored procedures, then a prefix probably won't help you much. I don't think it would make it more difficult either.
Remember that the naming convention for stored procs is for more than just the DBA though. Developers use these stored procs and in a complex script with many stored procs, tables, views, functions, and other objects, it takes out the ambiguity when looking at a script. It's especially helpful if you're looking at the script via a text editor, Visual Studio, or something else where you don't have the full support of Query Analyzer or the ability to quickly run a SQL statement every time you see an object name and can't remember what it is. It won't make or break you, it just makes development easier if you can limit the guesswork.
Good article though. I'm a big supporter of naming conventions. I have used several different ones at various client sites and the ones who have a standard and follow it are much easier to support than those who don't -- irregardless of whether they include or discard the prefix.
October 3, 2005 at 12:58 pm
Naming conventions are difficult to make useful ALL OF THE TIME. I'm currently working at a place that uses a strict naming convention. Our SPs are named by project_method_action_sp. Unfortunately when they have something common like adding a customer they do not have a good way to use the above naming convention. And my personal feeling is that the suffix _sel_sp is just rediculous as you will find an sp that the developer may have thought would be a simple select but in actuality does much more so the _sel_sp has no meaning at all. Also, why bother with the suffix _sp? I do like having the SPs grouped by project and method. But for the generic ones I would go with something along the lines of global_customer_add. That way you can find the reusable ones very easily and the project specific ones will be grouped together as well. My biggest gripe with our current method is that there is no correlation between the functional spec "method" name and the stored procedure name. This makes it very hard to validate that the functional spec has been correctly implemented.
Having a naming standard is great. But it can be taken too far. For instance we have a standard that states that all variable names, stored procedure names, function names, temp/variable tables have to be in lower case and all their field names in lower case yet the table names and field names of regular tables have to be mixed (are you confused yet? I know I get that way when I'm coding trying figure out what case I need to be using!). They also don't allow the use of aliases on the tables unless used in a subquery. All in all the coding standards they have set up make for a lot of bloated code and extra time spent coding. But since their code review process is also very strict the code is pretty and formatted the same way!
A comment on prefixes. If you use the <project>_<Method>_<action> and do your coding in Visual Studio Enterprise edition with a database project you can access the SPs in the database by hitting the first letter on your keyboard in the server explorer panes tree view. If all your SPs use usp_ as a prefix you don't get to use this handy feature.
Gary Johnson
Sr Database Engineer
October 3, 2005 at 7:30 pm
Just a personal prefix that I like to use rather than "usp_" is "up_" this way I can perform a find for every occurance of "up_" to find all user procedures in a large script, "sp_" to find all system procedures in a large script and "p_ to find all procedures, user or system, in a large script.
But my personal preference for any standard naming conventions is to simply have one and have it documented. Unless Microsoft decide to set it in stone as part of the SQL code, no-one will ever agree on a common convention across the board but as long as we have a convention and have it documented this can make it all the more easier for outsiders or fellow co-workers to navigate our work and save far more time than it costs.
Charles Wilkinson
Database Administrator
Sastek Pty Ltd
www.sastek.com
October 3, 2005 at 8:25 pm
Charles - I agree 100%. There are many workable standards out there - but the best one is having one and sticking with it.
At first I was worried about putting my pet standard out there in this article, but I'm now glad I did it, because I think it has produced an interesting discussion. Also, I'm seeing that no one standard solves every potential situation - as almost every comment on this article has a point-counter-point.
Best Regards,
Joe
October 3, 2005 at 10:04 pm
In our environment SQL development is divided between 3 groups and about 1/2 "freelancers".
Two of the groups and all of the freelancers have no convention.
In my team we following something like the function_subfunction_Description_Parameter format.
I set the function
UI - stored procs used to deliver functionality to a user interface
MAINT - used to perform application maintenance functions
REPORT - used for reports
The sub function is something like AR, ORDERS
The Parameter would CURRENT month, etc.
Its not documented well, but the three or four us work well together on it.
October 4, 2005 at 12:58 pm
I'm sure we all have our own opinions on this, but why would you ever append usp_ or sp_ in front of a stored procedure. I've not worked on most other applications than SQL Server, so my opinion is mute on all other platforms, but in regards to SQL Server, all SPs are already stored seperately from other objects, so appending usp_ or sp_ in front of the procedure name is just redundant information. Yes, we know it's an SP, not too concerned with what type, all system SPs are pretty easy to identify. For comparision reasons, here's an article I've been pointed to regarding naming practices which I think offers some very valid points.
http://weblogs.asp.net/jamauss/articles/DatabaseNamingConventions.aspx
This should definately be on any SQL developer's mind when it comes to DB creation (and especially for maintenance concerns) so I believe a lengthier article should have been written. Nice attempt, but little worth in my opinion. Sorry, I think this article is more of an opinion and less of an in-depth look of this inherant problem with DB design.
John
October 5, 2005 at 9:48 am
I think it is a decent article at least for newbies. I had no naming convention and haven't had time to develop one as i work on this project. Having SOME IDEAS verses NONE is better than none at all. And this has been a worry on my part but i had no where to begin.
This discussion has also cleared up some things that are found in the books i've read taht do suggest using another prefix other than sp_.
Since i'm programming i do feel i need to be able to tell the difference between an SP in code an other variables or constants. Knowing for sure it is an SP can be valuable.
I will give more thought to my naming convention now even if i haven't decided how to implement it.
October 19, 2005 at 1:43 pm
Say you have a database with 100 tables, with 4 procs each, 400 stored procs beginning with usp_. Your eyes quickly start to filter out the usp_. That prefix is noise. Save the characters for something useful, or just keep your proc names short.
System_Action_Object descriptor or just Action_Object_descriptor for a database used for just one module/system/subject
October 3, 2006 at 8:38 am
October 3, 2006 at 10:32 am
Like the author, I use the 'usp_' prefix on all of my stored procs. The rest of the name is descriptive text. Personally, I like to be able to take a quick look at the list of stored procedures and see which ones are mine, which ones belong to the system and which ones belong to the application. Obviously, I don't use 'usp_' for those procedures which are directly involved in the application, assuming I am actually writting it.
Unfortuntaly, I've been stuck having to muck about with boxed applications to produce results outside of what was originally intend. For example, I have a couple of minor tweaks that I have done to our MS Project database (mostly for reporting stuff), which required stored procs. The last thing I would want to do is lose my procedures inside the massive list of application procedures, so all of my procs are named 'usp_InsertdescriptiveTextHere'. When looking at the list of procs, I just scroll past the hundered or so 'MSP_ProjectProcName', to my little list at the bottom.
I had not considered adding an extra prefix to my procs to define the object(s) they touched, but I think I may steal that idea.
Good article overall, if nothing else, it has spawned a good discussion.
October 6, 2006 at 7:07 am
Prefixing SPs with "usp" seems redundant to me - u - user - it must be a user object, because last time I checked, I wasn't SQL Server - sp - well, it's "CREATE PROCEDURE.." therefore it's an SP!
The remainder of the prefixes, I dislike, as procedures will get grouped together by the type of action they perform, rather than by their region of the system, I generally use:
Customer_Get
Customer_Insert
Customer_Delete
.. etc.
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply