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

Hungarian Notation To Be or Not To Be Expand / Collapse
Author
Message
Posted Saturday, September 25, 2004 10:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 19, 2012 4:00 PM
Points: 100, Visits: 168

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?

 




Post #138814
Posted Monday, September 27, 2004 2:06 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:31 PM
Points: 2,914, Visits: 1,846

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. 

 



LinkedIn Profile
Newbie on www.simple-talk.com
Post #138848
Posted Monday, September 27, 2004 2:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 30, 2007 2:42 AM
Points: 150, Visits: 1
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.
Post #138854
Posted Monday, September 27, 2004 7:25 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 1:50 PM
Points: 2,845, Visits: 1,160

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.




Post #138880
Posted Monday, September 27, 2004 7:30 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 12:29 PM
Points: 1,945, Visits: 3,121

Bingo!  Hungarian Notition was meant for weakly typed languages, not for a data model.  They ought to be using ISO-11179 metadata standards for their naming convention.   But I bet nobody reads standards. 

Things like "tblCustomer" look so silly; there is only one data structure in SQL, so this prefix is redundant.  Using ISO rules, this is a piece of furniture that buys things from us; likewise "vw_orders" are orders placed by or for a Volkswagen.

People like this confuse a field with a column and rows with records.  Fields get meaning from where they are used and read; columns have a domains that give them meaning in themselves. 

Likewise, since fields are in sequential order in a record, a positional number makes sense; but columns and rows are not ordered. 

What has been described is basically a (bad) 1950's file system forces into SQL.

I am working a short book (I am about 10,000 words into it now) on SQL Style.  If anyone has ideas, comments or some really sucky code to share, then please email me with it. 

 



Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #138882
Posted Monday, September 27, 2004 7:33 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:31 PM
Points: 2,914, Visits: 1,846

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.



LinkedIn Profile
Newbie on www.simple-talk.com
Post #138884
Posted Monday, September 27, 2004 7:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 15, 2009 3:23 PM
Points: 198, Visits: 263

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.

Post #138887
Posted Monday, September 27, 2004 8:26 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 12:29 PM
Points: 1,945, Visits: 3,121

The ISO Standard is a "<entity><attribute>" or  model, with a scale or encoding defined in the data dictionary.  For example, I might have "tree_diameter" and know that it is in centimeters.   the use of prefixes screws up the data dictionary; I can have "tbl_tree" and "vw_tree" and not know it. 

Because they model sets, tables and views should have plural or collective names.  You cannot confuse a column or scalar with a table using that convention. 

Procedures are supposed to be names with a "<verb><object>" format.  And object needs to be something meaningful in the data model.  I hate the dynamic SQL stuff that would have to be named "Show some column to be determined by the currntr random user in some table, also to be determined by the currntr random user"  ort perhaps "Show me the weight of a Moose, an apple, or Britteny Spears".  It is as if Software Engineering never existed. 

Putting an expression in an ORDER BY is not SQL-92; it depends on the fact that SQL Server is still a sequential file structure under the covers.  The ORDER BY is part of a DECLARE CURSOR statement in Standard SQL, so you are needlessly destroying portability.

However the objection to the CASE expression in the ORDER BY for me is that I want to see the column(s) used for the sorting in the front end so my host program can use them.

How about the guys who believe there is a magical "id" on every entity in the data model?  let's re-invent the record number and other exposed physical locators!  We all know that God put a 17 digit Hebrew number on everything in creation and you need a Rabbi trained in the Kabala to see it

 



Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #138928
Posted Monday, September 27, 2004 8:34 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:31 PM
Points: 2,914, Visits: 1,846

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



LinkedIn Profile
Newbie on www.simple-talk.com
Post #138932
Posted Monday, September 27, 2004 10:27 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, February 9, 2009 12:04 PM
Points: 238, Visits: 4

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




Post #138955
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse