Hungarian Notation To Be or Not To Be
-
The Data Warehouse team decided to use Hungarian notation on their SQL Server database (they aren't using the OLAP engine for their DW), and they decided to use the positional features of T-SQL in their code so if they had to change a datatype, column, etc. they wouldn't have to change their T-SQL code, because they'd rely on the position of the columns to handle any changes. They love Select *'s, Order by 1, and my all time favorite (sarcasm thick here) is the case statement in the Order By Clause. Having said that, now with .NET changing from passing parameters to SPROCs using a positional process, .NET requires you to name the parameter. So now, if you change an object's name in the database, you're more likely to have to crack your code all the way down to the web app. What are ya'll's thoughts on using Hungarian Notation rather than the good ol' Microsoft way, of calling it like it is?
-
I hate the idea of using positional arguments for code. How the hell are you supposed to know what ORDER BY 3 means without trawling through the code?
The argument put forward by your team seems to imply that field names are changing. I NEVER change field names precisely because doing so will break existing code.
If you rely on ordinal positions what happens if one of your team inserts a column? All of a sudden column 2 doesn't mean what you thought it should. This is also a good reason not to use SELECT *.
As far as using Hungarian notation my colleagues and I went through the debate and came out in favour of using it.
Yes, if the use of a variable changes then there are issues with altering the code but this is offset by looking at a variable and knowning just from looking at that variable name that it is a member of a class and pointer to a string.
If you have to change a variable within your code then search/replace tends to deal with it quite well thankyou very much, plus the compiler deals with the rest.
The main argument against Hungarian is that the variable names it produces are not tied to the code and can therefore become meaningless. You could say much the same thing about comments in your code and the comments are a damned site less connected to your code than the variables.
Unless you have meticulous documentation (sorry, I got distracted by the all England porcine aerobatic display team outside my window)
tracking down what a variable is supposed to be is in reams of code is not a fun job. Even with documentation you are still going to be taking a break from your code to look it up!Fashions come and go in IT and Hungarian is the equivalent of good strong shoes and sensible trousers.
-
Hungarian Notition is great for apps but not databases. You change one field and then you have to change 15 stored procedures, 13 views, 3 triggers, etc all because of a name change.
The only Hungarian Notation you shoud use if any is to stipulate between table and field and not any further than that (as in int or varchar) as this will not change. So if you had a table with customers in it call it tblCustomer and its fields could be fldCustomerId, fldFamilyName, fldGivenName, ... etc. You could change fldCustomerId from int to string and never have a major problem (I'm not saying you won't have a problem - but it won't be caused by a name change that for sure).
Besides a RDBMS isn't designed to communicate with .NET, VB, Java or whatever is being used. It's designed to store information and allow it to be retrieved in an effective way. So keep it effective. Don't allow the technology to dictate the design, good design should always shine through.
-
Ditto to keeping Hungarian out of databases. I'm a firm believer in using it in code, but the only trace of it I use in databases is a 'vw' prefix to distinguish views from tables. These are two different objects that can often be used interchangably so it makes sense to do it, but it is unlikely you would confuse a field with a table.
Anyone using 'SELECT *' or 'ORDER BY 3' in production code should be retrained immediatly, preferably in a medieval dungeon.
But I have to admit to having found use for the CASE statement in the ORDER BY clause. I'm not sure why this would be considered evil, feel free to enlighten me.
-
I think the CASE in order by statements causes recompiles but I couldn't swear to it.
I prefix stored procedures with usp and functions with fn.
To be honest I have moved away from tbl and vw as prefixes for views and tables precisely because they are interchangeable.
An MS example would be the syslogins view.
I use Hungarian for variables and arguments within stored procedures but I don't bother for fields. It is just being over-zealous.
-
I prefer to keep Hungarian Notation strictly to the applications and leave them out of the databases. However, we do end up using some variation of it when naming stored procedures and views. Our views are set aside by v_ or view_ and our stored procedures normally start with pr. Otherwise, tables and fields don't use the Hungarian Notation.
And if I saw a SELECT * or an ORDER BY 3 in any of the code I had to work with, I'd definitely be re-writing it. From a programmer's standpoint, I'd like to know just what I'm working with and * and 3 really don't help.
-
One plus point on using prefixes is that you are unlikely to use a reserved word accidentally.
Given the plethora or languages out there it becomes ever easier to do this.
I had to debug a SSJS app and found that someone had used a variable name called "mid".
-
I do not think Hungarian Notation (or a variabt thereof) is bad at all for SQL. For example, we use the following prefixes:
tbl - standard data entry table
tlkp - static or semi static lookup table
ztbl - private system-level or application-level table (not for end-user eyes)
ttmp - semi-persistent temporary tables (usually reserved for load programs undergoing debugging or other testing)
We then insert some kind of DB or App identifier into the object name, so that we can easily differentiate items if a script happens to appear before us without a "USE" statement. Here, OSYS refers to the application or database.
tbl_OSYS_[name suffix]
We do this for all other objects (see below).
We also use this standard naming to deal with some problems in naming other objects. We used to do things like
spi - Insert SP
spd - Delete SP
... etc. However this became unwieldy in practice, as when developers tried to build classes, they really had to hunt around for items that were based on common objects. So we revised our practice to the following:
spa - All SPs that have are not dependent on other SPs.
sp0_ - All SPs which have dependencies
spz - All non-application specific SPs (related to db processes etc)
Then we modified our naming suffixes to Noun-Verb combinations, e.g.:
spa_OSYS_CustomerDetailGet
spa_OSYS_CustomerDetailAdd
spa_OSYS_CustomerDetailDelete
spa_OSYS_CustomerDetailSet
spa_OSYS_CustomerSummaryListGet
While we're at it, parameters and variables prefixes are structured as well:
@parm - input parameter
@pout - output parameter
@pio - input/output parameter
@var - local variable
While not ideal, and probably redundant in some ways, it works for us. It has really helped our developers, and even helps the DBAs easily and quickly identify objects associated with and app or database, even from a printout.
I'm sure folks can poke holes in this (there are more modern techniques, I'm know), and alternative ideas are welcome. But as Ken Getz once said [paraphrasing here], "use a naming convention, ANY naming convention, as long as it's logical and consistent."
-
The general recommendation is that you don't use sp_ as a prefix for stored procedures because internally SQL Server will look in the MASTER database first causing a cache miss.
I'm with you on the consistency thing. Having a standard that changes with the weather (especially in these global warming times) is not a good idea.
There are practices that are like Mercedes with the single complex windscreen wiper. A bad idea in principle but very well executed.
-
Sorry, it's sp0_[Name], not sp_....
-
I definately you should have some naming standards, just not Hungarian.
For example, we had a manager who heard about the concept of metadata and completely misunderstood it, but soon all our lookup tables had to have a "Meta" prefix. He's gone now, and we have to hear from every new hire about how these tables aren't metadata, but all the programmers know that they can look up xxxID in the Metaxxx table.
So even misguided naming conventions can be useful.
-
I remember the first SQL training I did (outside of Uni) when I started work - the trainer said one thing that I rememebered - "If you're going to get it wrong - get it consistently wrong". Even if you do have to change all the code - at least the search and replace will be made easier!
-
I would go further and say that "wrong" tends to be a matter of opinion rather than hard fact.
-
Thank you all! This has been a very enlightening discussion, and I now have an interesting wealth of information to hand over. I'd like to thank Joe Celko for his input, considering, I took all my naming conventions from his books early in my career and have never had any issues with them. Thanks again!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply