Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

usp stored procedure prefix Expand / Collapse
Author
Message
Posted Tuesday, February 21, 2006 1:53 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 25, 2014 8:34 AM
Points: 255, Visits: 74

If I was trying to learn the system at the database level with the intent of making changes to the tables, I would look at an ER diagram and see the physical tables and their relationships to other tables, their cardinality and indexes.

If, on the other hand, I was learning the system as a programmer who was going to be using the database as the backend of an application I wouldn't care what the implementation of 'Customer' was, is or will be in the future.

I have found that linking the names of objects to some naming convention has actually been counterproductive in large team environments.  Any changes made require knowledge of what other members of the team are doing and require changes to their code.  Either that or you end up not changing the name to reflect the new definition of the object and this is even more confusing.

Post #260391
Posted Tuesday, February 21, 2006 2:37 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 28, 2012 6:13 AM
Points: 380, Visits: 52
If you decide to use a prefix, then yes, usp versus sp makes a difference. SQL Server automatically searches the master database when sp_ is called. That can hurt performance.

Take a look at this article:
http://www.sqlservercentral.com/columnists/jtravis/sp_performance.asp




Michelle
Post #260406
Posted Wednesday, February 22, 2006 10:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 17, 2014 11:51 AM
Points: 226, Visits: 119
good information to know, Michelle. thank you.
Post #260699
Posted Wednesday, February 22, 2006 12:43 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, April 28, 2016 2:26 PM
Points: 478, Visits: 1,647

If I really had to know whether Customer was a table or a view - and I usually wouldn't - it would not take very long to find out, in any of the development or admin tools I have used.  Expand the tables list.. nope.  Expand Views... OK, it's a view.

For most development using that object, it makes no difference if it is a table or a view.  You select, insert, update, delete, build indexes, and write triggers on tables and views the same way.  While some views are not legal targets for inserts, updates and deletes, this is not an issue: if you don't know what's inside the thing, why are you trying to delete data from it? 

When tuning, the query plan names the underlying objects being referenced and any indexes in them being used.  If I'm querying a view, and look at the plan, the view isn't even mentioned.  I still don't need to know if my target object is a view or a table; I'm only interested in what's actually happening, and that is explicitly displayed in the plan.

Going back to one of my earlier points, if Customer was a table when the database was built, that doesn't mean it always will be.  If a database was not designed to get huge, and it gets huge, then all sorts of changes are usually necessary to keep it from suffocating under its own weight.  Just one example is turning large tables into views and partitioning the data horizontally and/or vertically into multiple tables, potentially over multiple servers.  Unless the original table had been cursed with an identity column, then clients would treat the view the same way they treated the table. 

Requirements change.  Objects change.  Some project formats, such as 'extreme' or 'agile' programming, are built on this premise.  Rigidity = death.  By treating your SQL Server objects similar to other programming objects - the implementation is hidden from the caller, they just need the interface - your designs are ready for these changes.  The larger the project, the more changes that will come.  Adaptable code is just another trick in the bag that makes life easier.



Eddie Wuerch
MCM: SQL
Post #260736
Posted Thursday, February 23, 2006 9:39 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 26, 2010 11:48 PM
Points: 52, Visits: 25

This is an interesting discussion and thank you for your feedback. Although I have let go of the hungarian notation in my code a very long time a ago, the sql prefixes stayed for a while longer   Although I still think that the prefixes are useful in making the code more readable and eliminate the guesswork, I will make the change.

Regarding the table vs view debate, I have been in a situation previously, where the database schema changed and what was a table now needed to be implemented as a view. I ended up adding the tbl prefix to a view in order not to have to cascade the change throughout the system (probably someone out there has a global search and replace script, but I didn't have the option at the time).




Post #261053
Posted Thursday, February 23, 2006 7:04 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 11, 2016 1:08 PM
Points: 123, Visits: 113
After railing against Hungarian notation in my earlier post, I must say that the one such convention I do find less offensive in sql is the prefixing of views with vw_, or something similar. They seldom turn into tables, in my experience, and on occasion it has taken me awhile to realize their true nature
Post #261187
Posted Friday, May 10, 2013 10:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 15, 2015 12:42 PM
Points: 12, Visits: 16
Eddie - For what it's worth, I endorse everything you said. My 3 decades of software development experience and 2 decades of Sybase/Oracle/SQL Server developments, administration, and architecting are consistent with your views.

Well written and thought out... good advice
Post #1451690
Posted Friday, January 22, 2016 1:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 22, 2016 1:10 PM
Points: 1, Visits: 2
Wakey wakey, Forum thread. :)

I found this during a search and felt compelled to submit my two cents.

I am against ubiquitous non-descriptive naming conventions; however, I am for descriptive naming conventions because eliminating the process of [looking at an object's name and not immediately knowing what it is or does without having to go hunting and read some or all of the code which can take a really long time] is possible with a little forethought and the implementation of a descriptive naming convention.

In an environment with 1000's of database objects and several developers, this "lookup time" can amount to 1000's of wasted minutes when aggregated. Adhering to a not-really-that-hard-to-stomach naming convention can reduce or altogether prevent this waste of time.

In a situation that wouldn't be better solved using schemata, useful prefixes — like [PJSP_%] - standing for Primary Join Stored Procedure — can be used to categorize many different steps or one of several series of stored procedure executions in an environment where code standardization is required across MANY databases and when multiple people are working on the code base of over one million lines. A object browser skim can take a very long time to find one object in 500 stored procedures, or 500 tables, or 500 views when the object might be a table function. Three characters "vw_" can save the developer from having to look in multiple places for his item.

Q: But what if the table becomes a view!?
A: Not a valid excuse anymore. Do some work, and build the solution properly. In the future, build solutions with scalability in mind from the beginning instead of naming objects and coding in a manner that will allow for inefficient design to persist until a scalable solution is required to be built.

Prefixing also makes following instructions more palpable for junior developers:
Copy all of the Primary Join Procedures and all of their called functions and called procedures to Database1743 from Database_0000_CODEREPO.

Imagine, being a junior developer finding non-descriptive procedure names:
"Table Backup", "Base Table Creation", "Base Table Population", "Data Calculations", "Data Calculation", ... "Final Step"
You'd come back in a few minutes with questions and have to skim code to find out which functions and other procs are called by the procedures. This is highly inefficient.

Now imagine, being a junior developer and finding procs and functions named thusly:
"PJSP_Step001", "PJSP_Step002", "PJSP_Step003", "SJSP_Step001" ... "PJSP_Step167"
"PJSP_SubStep001Backup", "PJSP_SubStep001ARenameOldIfError", "PJSP_SubStep003Restore"
You'd probably be able to get started right away and probably be able to finish before coming back with questions.

Code example:
WHERE sys.procedures.name LIKE 'PJSP_Step%'
-- One, elegant condition.


Keep up the good work, fellas.
Post #1755056
Posted Friday, January 22, 2016 10:10 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:33 AM
Points: 40,381, Visits: 37,589
william.balogh (1/22/2016)
Wakey wakey, Forum thread. :)


Right back a'cha and welcome aboard.


Q: But what if the table becomes a view!?
A: Not a valid excuse anymore. Do some work, and build the solution properly. In the future, build solutions with scalability in mind from the beginning instead of naming objects and coding in a manner that will allow for inefficient design to persist until a scalable solution is required to be built.



First of all, if it takes a developer any time at all to figure out whether they're working with a stored procedure, table, or function just by looking at the code, they should probably learn the grammar of SQL a whole lot better or pick a different career that they're going to be good at.

Tables, views, and synonyms are about the only things that look identical in a (for example) FROM clause and if it's with Intellisense, it really doesn't matter unless they actually need to research the object, they're going to need to look for it in the Object Explorer anyway. Even if there are thousand of tables, it takes only a second to engage the filter to find the object if the developer knows how to copy and paste. In fact, that rare act takes about as long as it does to type "tbl_" or "vw_" or "syn". Even with Intellisense, that takes much more combined time than the occasional lookup that you're talking about.

As for properly planning for scalability, it's not always to support partitioning that a table (for example) might be changed to a view/synonym or vice versa. There are plenty of other reasons. For example, you might change a function from T-SQL to SQLCLR for performance because the DBA finally gave in on the use of SQLCLR. Requirements change and, unless you actually have a crystal ball or have mastered the use of DBCC TIMEWARP, you will run into other problems that may require such a change and it's usually a whole lot more effective to change an object type than it is to go through all of the T-SQL, front end code, possible batch code, and third party driven code such as that for WebMethods, Business Objects, or calling all your customers that may have access, or what have you. Leaving it improperly named should not be an option if you actually do value Hungarian Notation.

To be sure, I'm not trying to convince anyone whether or not Hungarian notation is a good idea or not. I'm saying that it's no good for me and I won't have it in my shop. If I have to work in a shop where Hungarian Notation is the rule, so be it... I'll adapt. I don't have to like it to adapt and, make no doubt about it, I don't like it so I get 5x5 in the area of adaptability.

BTW, do you have a link for the paragraph you cited? I'd like to go and help people adapt over there.


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

Helpful Links:
How to post code problems
How to post performance problems
Post #1755131
Posted Saturday, January 23, 2016 12:20 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:18 AM
Points: 44,477, Visits: 42,083
Jeff Moden (1/22/2016)
I'm saying that it's no good for me and I won't have it in my shop.


Seconded.

I did a long project at a company that mandated usp_ for all stored procs. So if I was typing into object explorer to locate the procedure that runs step 1 of process 75, I would type 9 characters before a single object filtered out of the list (dbo.usp_*). Contrast, without prefixes I could just look for Process75.Step1 (if using schema for grouping) or dbo.Process75Step1 if not.

Hungarian notation was never supposed to be object/variable types (iLoopVariable, sLastName, etc). That's useless. Any halfway-competent intellisense can get that info. Hell, even the near-useless SSMS built-in intelllisense will, if I hover over an object name, tell me the object's type.

Even without that, it's only tables and views that can get mixed up, unless the developer's completely new to T-SQL.

The original intention of Hungarian notation was to designate the kind of object, not it's data type.
http://www.joelonsoftware.com/articles/Wrong.html (about 2/3 of the way down)

Oh, and as for the example for the junior dev, if I was defining the naming standard, it would be something like

PrimaryJoin_Step001_TableBackup, PrimaryJoin_Step002_BaseTableCreation, PrimaryJoin_Step003_BaseTablePopulation, PrimaryJoin_Step004_DataCalculations, ...
Now from the name I know what process it's part of (and don't need to worry about if it's part of the PrimaryJoin process or the ProcessJournal process), what step in the process and a high-level summary of what it does.



Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass



  Post Attachments 
Intellisense.png (75 views, 1.83 KB)
Post #1755141
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse