May 31, 2008 at 2:33 am
Can anyone give me an idea of a good naming convention for views and stored procs please? We seem to prefix our views with rv and and our stored procs with rp. Is it necessary to prefix these objects? Also, what is the best way of tracking what the usage of each object is? For example, we have over a hundred views but I cannot tell what some of them are used for or indeed if they are still required.
Thanks
May 31, 2008 at 10:36 am
You certainly don't need any prefixes to make them run. So far as not being able to tell what the views are used for, that's thanks to the lack of a naming convention on the part of the original creators. My general rule is a table or view should be named to describe the content or purpose of one row in the table or view.
So far as determining when a view was last used, I don't know... rumor has it that you can use a DDL trigger on a view to store the view name and current date, but I don't know how to do it. Hopefully, someone else will see this post and shed some light in that area.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 31, 2008 at 12:49 pm
Hello David,
a good naming convention is a convention that everyone dealing with your objects follows.
I dont see any reason for not using your suggested prefixes. I use vw and usp, but thats just personal preference.
With regards to identifying who is using what objects: If you have granular permissions assigned to your objects, you should be able to identify your "users" by those permissions. Unfortunately, permissions are too often applied on a broad basis (running apps under the sa account or as the db owner for example)
Although it seems to speed up development processes at the beginning, it tends to leave a "cloud" like the one you currently have where noone knows exactly what happens under the hoods of the server...
You may be able to trace the activity to find out more about your selects. But that will impose an overhead. There might also be some third party tools, but I am not sure.
Best Regards,
Chris Büttner
June 1, 2008 at 11:32 am
It is a useful to have comments in all procedures and views, describing what it is, how it is used, and who and when has changed the definition... but that's something you can start doing now for future.
Regarding old objects without proper description, this is what you can do:
- check whether these objects are referenced by other objects inside SQL server (view used in a procedure, or procedure called in a job etc. - you can query the system tables for that)
- use Profiler to find out, whether these objects are accessed, when and how often
- read the code, try to understand what it does and comment the code
- if you are pretty confident, that an object is obsolete, rename it (e.g. prefix the name with underscore). In case you were wrong, sooner or later it will pop up in some error, and all you'll have to do will be rename it back and write a comment what it is for
- if you're using some third party software, try to squeeze the authors for technical documentation, at least in the form of list of objects their program is using, or other means of identification (sometimes all objects used by the software have the same owner, or some prefix in the name, or something inside the code that could help you)
- use your test database - change the names of objects there, and then perform actions that you think may be using the object
- if it seems, that it could be something vital, but so far you weren't able to make sure, better leave it as it is, and include this in the comments
If there are hundreds of views suspected doing nothing, that's pretty bad... I don't know any tools that could help you (fortunately, I never had to cope with more than just a few suspects).
June 2, 2008 at 6:42 am
As Jeff and the others have already said, no, you don't need to put these abbreviations, or any others, in front of the views and stored procedures. However, I'll give a couple of my reasons for using that type of convention. We put 'v' in front of most of our views. Is say most because some views we intend as a mask so that the users don't know that they're accessing a view. Those views get named just like a table. As for stored procedures, we use 'spr' for read, 'spd' for delete, 'spc' for insert (create), 'spx' for multi-statements... you get the idea. This gives you some idea of what's in the procedures.
Since we store every object in the database as an individual file, having naming conventions that identify the objects (no we don't put 't' or 'tbl' in front of tables, they're just named for the thing that they represent) we name the files for the objects and then we have a very good idea what each file is.
That said, none of this is necessary. The best naming convention is one that is actually used and actually does something for you. If you can't justify it or it's so ugly that no one uses it, toss it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 2, 2008 at 8:17 am
With regard to naming convensions, I have never understood why vendors frequently call their stored procedures with a prefix such as PROC_% or SP _%.
Surely if you have a naming standard and wish to identify by inspection of a name alone, then a suffix standard of _PROC (or something of that ilk), would mean that when you look at the Management Studio tree for Stored Procedures, you can then find alphabetically the SP easier than with a Prefixed named?
e.g. What might be easier to find?
a) If you have 100 Stored Procedures all called PROC_ and need to find one called PROC_FRED%
or
b) If you have 100 Stored Procedures all called _PROC and need to find one called FRED%_PROC
Hopefully this is making my point clear enough?
June 2, 2008 at 9:03 am
I tend to name views and procs based on the table(s) they will access, and the function they perform. My naming convention looks a lot like OOP, just without the periods.
For example, if I have a Customers table, I might have objects like these:
Tables:
Customers
CustomersEmail
CustomersPhone
CustomersAddresses
Procs:
CustomersContact -- selects contact data from all four tables
CustomersEmailInsert -- adds a new e-mail to an existing customer
CustomersNew -- creates a new customer, inserts appropriate phone, e-mail and address data
This makes it very, very easy to find things in the database, and pretty easy to tell what something does just by looking at the name.
The only ones that are at all more complex to find are many-to-many tables, which I name things like:
Customers_Orders
Orders_Jobs
These, you kind of have to know the object structure of the database to find, but it's not too hard. I've thought of naming them based on the alphabetical order of the tables, but haven't tried that yet. (I ended up with Orders_Jobs because the first database I worked on, you had to have an order to have a job, but not every order had a job, so orders were a higher precedence than jobs. Jobs, here, refers to a work-order that would go to the Production Dept.)
As far as prefixing procs and views, I've never found that useful to me. Others may find it useful, but I don't find it any more clear to have "exec dbo.CustomersNew" vs "exec dbo.procCustomersNew". It's got "exec" in front of it, that pretty clearly makes it a proc. The only one where this matters much is views vs tables, since both get selected from. It would make sense there. But I find myself using so few views these days (in the last four or five years), that I don't bother much. (In other words, I should have a standard for naming views, but I don't.) In either case, a quick check of various system views or of the object tree in Management Studio, sorts it out pretty readily, in those rare cases where it matters.
- 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
June 2, 2008 at 9:46 am
A naming convention I was taught a long time ago (and still use today) is:
GetCustomer - select
InsCustomer - insert
UpdCustomer - update
DelCustomer - delete
June 2, 2008 at 10:10 am
Some good advice, and there are a few articles on this site (search naming conventions).
I try to get things to group, since that's how I use them. So everything with Customers, I'd use customers first in the name uspCustomerGetAll, uspCustomersUpdateName, etc. That way they appear near each other.
As for keeping track of them, it's hard. If you have a good development process, sysdepends works ok. If not, Red Gate Software (I work for Red Gate) sells a product that finds dependencies. You can use that to prune older stuff.
Other than that, you try to reuse stuff to keep it in developers' minds. You can try documenting, but that's hit and miss.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply