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.
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.
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.
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.
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
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 tabletlkp - static or semi static lookup tableztbl - 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.
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 SPspd - 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 dependenciesspz - All non-application specific SPs (related to db processes etc)
Then we modified our naming suffixes to Noun-Verb combinations, e.g.:
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."