usp stored procedure prefix
-
I've been using the usp prefix for years and have been challenged by a collegue about its usage who prefers no prefixes. The only reasons that I can think of, to continue using it, are to distinguish it from the system or global procedures (sp). I am not sure how strong these reasons are. Are there other compelling arguments to do so?
Thanks,
Olja
-
Well, this one often falls into the same religious-war category as allowing NULLs in columns; many people don't like to change their ways. This thread should get rather heated. Let's see if I can do this without stepping on a lot of toes...
That being said, I'll side-step putting 'usp' in front of procedures and address object prefixes in general. In general, IMHO, they are rigid and unnecessary. This affects tables, views, and table-valued functions more than procedures, but once you eliminate prefixes from many of your objects, you should get rid of prefixes for the rest of them.
The problem with table, view, and function prefixes is that tuning strategies and evolving application specs can force an object change. For example, if I have a table called dbo.tblCustomer, and later turn it into a partitioned view over several tables, then either I leave the 'tbl' prefix on the new view - defeating the purpose of prefixes - or I rename the object to dbo.viwCustomer and go and update every piece of code that references it, a very expensive process to support using prefixes.
This thinking extends to column prefixes as well... ever had to change a column from an int to a decimal, etc.?
In a more esoteric viewpoint, does the entity 'tblCustomer' hold rows of 'tblCustomers' or rows of 'Customers'.
In prefix-less systems with thousands of procedures, hundreds of tables, plus lots and lots of views and functions, I've found it quite easy to tell the difference in code between tables, procedures and functions. Adding the prefix to every object just adds noise to the system.
-Eddie
Eddie Wuerch
MCM: SQL -
It's the same reason I indent and use extra sapces when I code. It pops out at you and says, "HEY! I'm a stored procedure!" There's no extra thought process. Also, when referencing a sp or vw to a colleague... I find myself saying, "hey check out s-p... " You visualize it alot faster. Maybe I'm just used to it, but people seem to react alot quicker, and it's easier to organize and identify. I think anyone who doesn't do it is just being lazy.
-
I feel that using forms of hungarian notation is generally a mistake. As has been previously stated, when changes occur the prefixes are generally not changed or require mass search and replaces.
Secondly, using any type of SQL tool, all the type of objects are grouped together by type anyway, all the stored procedures are together, the views etc. By prefacing all your stored procedures with sp it just add noise to the left when looking for a procedure in these lists.
Thirdly, based on the context of an object it is usually obvious what it is:
exec Customer --a sproc
select * from Customer -- a view or table
select * from dbo.Customer() -- a function
-
I agree with Eddie and Jeff 10000 percent, and will go further (not minding stepping on toes) to say that I believe "Hungarian notation" to be a curse on the entire software industry. It is uncalculable how much this gibberish has detracted from code clarity over the years.
That said, it seems less offensive to me in SQL than in many other languages, which are often rendered damn near unreadable.
-
hey Jeff...
Is Customer a view or table?
-
Does it matter when selecting data from it? I does matter when doing inserts but the advantages of the ability to refactor parts of the database and not affect existing selects and reports usually wins. In most of the databases that I develop there is one common procedure to do the insert of data but the table may be used in many other selects and joins. The ability to refactor code such as:
Customer(CustomerName, Address1, Address2....)
into
tables CustomerName(CustomerName, AddressID) and CustomerAddress(AddressID, Address1, Address2...) and a view Customer(CustomerName, Address1, Address2...) and have no impact on any code that references Customer is immeasurable. The only place that has to be modified is the procedure AddCustomer(@CustomerName, @Address1, @Address2...)
-
it matters when you're in a team environment. are you selecting from a view or from a table?
-
Why does it matter in any environment? If I "Select CustomerName, City From Customer Where CustomerID = 1", when does it matter what the implementation of Customer is? Do I care if it is one table or many?
-
If I wrote the application and created a view called "customers", and then you came in and you were trying to learn the system.... You would think Customers was a table, and you would waste time trying to figure it out. The prefixes have worked well in every team environment I've been in. If you're working alone, it doesn't really matter does it?
-
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.
-
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 -
good information to know, Michelle. thank you.
-
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 -
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).
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply