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