usp stored procedure prefix
-
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 😉
-
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
-
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.
-
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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
The only place I've ever used Hungarian notation in a database is for variables inside procedures, and I'm moving away from it. I was originally taught about databases about 24 years ago and learned it that way. I'm so used to it that I didn't even question it until about 6 months ago. I'm slowly moving away from it, but it's so natural to me that it takes time to make the adjustment.
As for database objects, I think it has no place at all. There are some people who use it where I work and I can't stand it.
-
I think prefixing database objects makes code more difficult to read, initially. Eventually the brain gets used to ignoring prefixes and one could argue maybe even begins to expect it. Luckily one of our greatest qualities, maybe our greatest, is that we can adapt.
The consistency argument is where it the issue gets settled for me. The point about wanting to convert a table to a view, or vice-versa, when refactoring a database has merit. This is a point against using prefixes (or suffixes for that matter, I have seen _vw suffixes in a naming convention too) and so by logical extension we should not use prefixes for any objects.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Intellisense is fine, but there is the tried and true ALT+F1. In SSMS simply select the object name in the query window and press ALT+F1 which executes the sp_help procedure and you get all kinds of useful information.
I personally don't use suffixes or prefixes... except for prefix on functions fn_blah and suffix on triggers [tableName]_trInsUpd ... I know, weird ain't it.
For procedures I use an [Object]_[Action][Modifier] naming convention. This allows similar objects to sort together... Customer_Get, Customer_GetByPhoneNumber, Customer_Insert, Customer_Update, Customer_Delete, Customer_DeleteInactive. Rather than all the Gets grouped together. For procedures, I use the column name and its data type for the parameter. Why make life complicated. I don't encapsulate a data type in the column name, but I do use a suffix of "On" for dates (i.e. CreatedOn, UpdatedOn, PurchasedOn, OrderOn). When it comes to bit columns I tend to use "Is" or "Has" as a prefix. I also try to name bit columns in a way that "true" is the normal desired value, such as IsActive rather than IsDeleted. I dunno, a WHERE clause with IsActive = 1 reads better than IsDeleted = 0. I tend to use "Id" rather than "ID" and "Cd" rather than "Code" for identity and code columns... but that's just me.
If you're simply reading the SQL code there are only a few things you need to know.
- You Select from Tables, Views and Synonyms.
- If you are doing a Delete, Insert or Update... it's probably a Table, but it could be a View with an instead trigger, a very simple (single table) view or a Synonym in another database and/or server.
- If you are SELECTing FROM something and it has/requires ()... it's a table valued function - and it doesn't matter if it's inline or multi-statement or SQLCLR.
- If you are SELECTing or SETting something and it has/requires ()... it's a scalar T-SQL or SQLCLR function.
- If you're EXECuting something... it's a stored procedure (T-SQL) or SQLCLR.
- If you're unsure of what something is - ALT+F1 should help. *You may have to change the object name and schema into a string when the schema isn't your default schema.
This isn't rocket science or brain surgery. We have four very simple commands... SELECT, INSERT, UPDATE and DELETE. Everything after that is fluff to baffle the rabble.
--Paul Hunter
-
Interesting thread. Just found it, and decided to add a few thoughts.
I don't think there's fundamentally anything wrong with usp_, but I'm not sure it's helpful. These days we have lots of tools, including a few free search tools (like SQL Search: https://www.red-gate.com/products/sql-development/sql-search/ ) and intellisense to help us find things.
If I use Object Explorer (OE) to look for things, they're already separated. Looking at list of procedures would be
usp_xxx
usp_yyy
usp_zzz
...
And my brain learns to ignore usp_, which means I'm really wasting time.
I read Aaron Bertrand's notes on naming(http://blogs.sqlsentry.com/aaronbertrand/subjectivity-naming-standards/), and I find myself agreeing. I want to learn something from the name. Over the years I've tried different naming ideas at jobs, and I like the idea of trying to group things together.
If I have objects that deal with the Customers, then I want those.
Customers (table or view)
CustomerNames (view of customer ids, names, maybe something commonly needed)
CustomerAdd (proc to add to customers)
CustomerDeleteCompany (proc to delete group of customers)
I can easily find out what these do, but when I'm looking to work with specific code, I want the objects to somehow relate to that entity.
The idea that I want to know a view from a table from the name is silly. I want to know, of course, but tables become views. This is refactoring. Perfect example I expect some of us will do in the next few years: add encryption. I'm going to have to move data around, and I may not want the outage. Instead, I'll rename the table and create a view with the old name. Then I can move data around underneath without changing my app.
Disclosure: I work for Redgate Software.
-
We need a thumbs up/down button and a count for each on posts. Sometimes all I want to do is know if a majority of readers agree or disagree with a particular comment... without having to add my 2-cents.
--Paul Hunter
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply