Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase ««12

usp stored procedure prefix Expand / Collapse
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:

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



Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 462, Visits: 1,344

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


Group: General Forum Members
Last Login: Thursday, October 8, 2015 12:29 AM
Points: 123, Visits: 110
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


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
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse