January 20, 2015 at 2:58 pm
RonKyle (1/20/2015)
It's a real fun trip through the code if even one well used column has its datatype changed.
Just because something is difficult on change doesn't mean it's a bad idea. It seems to me useful to have for example intThirdParty and strThirdParty. One is the integer key and the other the string name. A data type change for either one is extremely unlikely. In real life the most likely change is among integers, but I have gone to using int for tinyint, smallint, int. I'd probably use it for bigint, too, but I haven't needed that yet. I've worked with databases without this with generally minimal confusion, and I can see both sides, but it seems odd that so many dismiss it out of hand.
Considering the difficulty in change or the mistake of leaving such a change alone, how is such a naming convention a good idea in T-SQL?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 20, 2015 at 3:51 pm
I don't like those naming conventions because they can change. Imagine someone says create PO numbers. You do intPO or something. Then a year down the road you find you need 20150120a as well as 20150120. While changing the data type from int to varchar can be a pain, it could be much, much more distruptive to change names in code.
While this doesn't happen often, it doesn't need do. One change and we can't depend on the prefix to mean anything for columns.
To me, I think you have to consider the impact well beyond your time, or even the tribal knowledge of your staff.
January 21, 2015 at 2:06 am
Jeff Moden (1/20/2015)
John Mitchell-245523 (1/15/2015)
SQLvis (1/15/2015)
Is there a list of standard object nomenclature for indexes, procedures, functions, and views (etc) that most teams should follow?Not really. It's more important to have a convention or standard than what that convention actually is. Decide on something that works for you and your colleagues, and stick to it.
John
I have to disagree with that a bit. There have been times where we've changed views to tables and tables to views. If the Hungarian Naming Convention (or other similar convention) is used, then in order to prevent across the board changes to code, you either need to make a synonym (ugh!) for it or change the code or you can end up with a table like vw_tablename or a view that looks like tbl_viewname.
The same goes for column names. I've seen a number of databases that use the old Hungarian Notation Style of prefixing variable names and column names with the abbreviation for the base datatype. It's a real fun trip through the code if even one well used column has its datatype changed.
Of course, the "sp_" naming convention absolutely must be avoided for user stored procedures for reasons previously stated whether someone makes it a standard or not.
Jeff
Yes, I share your distaste for Hungarian notation. But some people swear by it, and if it works for them, who am I to tell them to avoid it? Maybe I should have also pointed out that not all naming conventions are created equal, and each has its advantages and drawbacks, its advocates and its detractors.
John
January 21, 2015 at 6:55 am
You do intPO or something
As a rule, I don't make non-mathematical numbers integers but strings. Subtracting one PO account from another gets me nothing useful. There is also issues with holding leading zeros, which is why SSNs should always be strings (and I have seen the occasional integer SSN). If it's based off an incrementing count, that would be an exception, but then the scenario you propose wouldn't come into play.
January 21, 2015 at 8:13 am
John Mitchell-245523 (1/21/2015)
JeffYes, I share your distaste for Hungarian notation. But some people swear by it, and if it works for them, who am I to tell them to avoid it?
YOU are the DBA. YOU should be the one to make the difference especially if they've done something absolutely stupid like prefixing their stored procedures with "sp_". YOU are the one that needs to teach the people that don't know any better. YOU are the DBA and you need to not only mentor people but make a stand for better code and code that is durable.
Maybe I should have also pointed out that not all naming conventions are created equal, and each has its advantages and drawbacks, its advocates and its detractors.
No need. That's common knowledge. What I'm pointing out is that Hungarian Notation is databases has many more detractors than advantages.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 21, 2015 at 8:17 am
Yes, I'm the DBA here and Hungarian notation does not form part of our standards. But I'm not the DBA everywhere, and I'm not arrogant enough to suggest that what works for my organisation will necessarily be best for everybody else's.
John
Edit - just to clarify: when I said "But some people swear by it, and if it works for them, who am I to tell them to avoid it?", I wasn't referring to my own colleagues. I meant that if other DBAs in other organisations have a reason to use Hungarian notation, that's absolutely fine by me.
January 21, 2015 at 8:41 am
There's nothing arrogant about it, John. There are many DBAs that simply haven't run into the problems associated with "tbl-ling" and they need to be made aware. Then, if they decide to continue down the path that they're on, so be it. I will say that when I become a new member of a given DBA team, that I will press very hard to have such a change made and get it into the standards. I'm still cleaning up "sp_" junk and "tbl_" notations that have become views (and vice versa) and a myriad of column names that no longer make sense that others have put into our legacy databases using Hungarian Notation.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 21, 2015 at 8:53 am
I'm still cleaning up "sp_" junk and "tbl_" notations that have become views (and vice versa) and a myriad of column names that no longer make sense that others have put into our legacy databases using Hungarian Notation.
Just because you are cleaning up these things doesn't mean Hungarian notation is bad. I never allowed "sp" and took it farther and used "ins_", "sel_" etc. I don't find views very useful in an OLTP environment, if only because I don't have a need to insulate the tables from the programmers. I find them more useful in an OLAP environment. But when the tables transition to a view, the name does change. It has to, as the table still exists.
January 21, 2015 at 9:27 am
RonKyle (1/21/2015)
Just because you are cleaning up these things doesn't mean Hungarian notation is bad.
You'll have to pardon me when I say that, IMHO, it's horrible especially when it comes to databases.
I don't find views very useful in an OLTP environment, if only because I don't have a need to insulate the tables from the programmers. I find them more useful in an OLAP environment. But when the tables transition to a view, the name does change. It has to, as the table still exists.
I don't normally change tables to views to insulate programmers, either. That normally requires a blanket. 🙂
But, I do use the technique a whole lot especially when I convert a large table to a partitioned view. The individual partitions are all in separately named tables and the view has the name of the original table so that no code changes need to be made in other areas. I've also renamed tables and put a view with the original table name over the top of them along with an INSTEAD OF trigger to pull of some preprocessing magic that would have otherwise required some huge across the board code changes (both front-end and DB objects) because of some very poor programming such as building the business logic into multiple different areas of code instead of into the database itself. I've also had to make views when the underlying table suffers a wholesale change. For example, we had a field-level audit table that used VARCHAR(8000) for the OldValue and NewValue columns and the people before me (I'll call them idiots because of how bad things were) were storing formatted dates, dollar amounts, and worse. We changed the underlying table to use SQL_Variant and stopped formatting data that went into this audit table so that it actually stood the chance of allowing SARGable queries against it but there was also a shedload of legacy code that needed to present the formatted data. I wrote a view named the same as the original table and renamed the underlying table. I also got rid of the god-awful generic CLR triggers that were responsible not only for the formatting on insert but also because they were horribly inefficient and slow (4 minutes to update 4 columns on just 10K rows on an (ugh!) 137 column wide source table... got that down to sub-second).
I've also had to do the reverse and change views to fully materialized tables that are updated by jobs because of poor design of the original underlying tables causing massive performance problems.
Because of Hungarian notation, we now have a fair number of views with a "tbl_" prefix and a fair number of tables with a "vw_" prefix and a ton of column names that were prefixed with the bloody datatype that have all changed. There's no time to go an fix all that junk.
Whether anyone thinks it's arrogant or controlling or what have, Hungarian Notation in databases and database related code is one of the worst things that you can do. It's not something that anyone should tolerate as a "well, at least it's consistent" advantage because as time wears on, it won't be unless you also accept the status quo of poorly designed databases and code.
Heh... shifting gears, anyone want to talk about what I think of "portable code" of ANSI/ISO compliant code while I'm on a tear? How about the use underscores and Pascal casing? 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
January 21, 2015 at 9:49 am
Whether anyone thinks it's arrogant or controlling or what have
I only said it's arrogant to tell someone that you don't know and whose environment and circumstances you don't know that it necessarily won't work for them. Anyway, let's flog this dead horse no more!
Heh... shifting gears, anyone want to talk about what I think of "portable code" of ANSI/ISO compliant code while I'm on a tear? How about the use underscores and Pascal casing?
Well off-topic now, but I think I can guess. I use ANSI compliant where it's the only distinguishing feature between two alternatives. For example, CURRENT_TIMESTAMP and GETDATE() both evaluate to the same thing, so I use the former because it's ANSI compliant. If something proprietary works better than something ANSI, I have no qualms about using it. I don't write code on the off-chance that I may one day want to put it on an Oracle server. As for underscores and Pascal (is that the the same as camel?), I think I prefer camel - I think it's more pleasing on the eye and it makes for ever so slightly shorter object names. It doesn't bother me if research has been done that says that underscore names are easier to read - I use what suits me. Unless, of course, there's an existing (non-Hungarian!) convention in place already.
John
January 21, 2015 at 6:09 pm
PBM is awesome and would work perfectly in this situation. I work with a group that has over 1000 policies monitoring 300+ servers including Dev and Production domains.
Deploying every policy you have is as simple as export and import so keeping the two in sync isn't really an issue.
Use the EPM Framework on Codeplex to really leverage policies for exception based SQL server management.
http://epmframework.codeplex.com/
The only negative to policies is that they aren't greatly documented so getting up to speed took longer than we would have liked.
January 21, 2015 at 6:42 pm
John Mitchell-245523 (1/21/2015)
Whether anyone thinks it's arrogant or controlling or what have
I only said it's arrogant to tell someone that you don't know and whose environment and circumstances you don't know that it necessarily won't work for them. Anyway, let's flog this dead horse no more!
I Can't. You keep replying with things that I disagree with! 😉
I can honestly say that I've been in enough different environments and have seen so much of what can go wrong that I can honestly say that I know if you're using wide-spread Hungarian Notation as a standard or by rote, then you're doing it wrong and that you will have the "opportunity" to find out how wrong it is when it ultimately bites you.
If someone decides not to heed that advice and they don't work in my shop, then that's their problem. If they're in my shop, then it's my problem and it will be met with irresistible force. Yes, there are exceptions to every rule but they're far and few between when it comes to Hungarian Notation.
Ok. Now we can quit. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
January 21, 2015 at 7:40 pm
John Mitchell-245523 (1/21/2015)
Well off-topic now, but I think I can guess. I use ANSI compliant where it's the only distinguishing feature between two alternatives. For example, CURRENT_TIMESTAMP and GETDATE() both evaluate to the same thing, so I use the former because it's ANSI compliant. If something proprietary works better than something ANSI, I have no qualms about using it. I don't write code on the off-chance that I may one day want to put it on an Oracle server.
+1000. I don't believe in crippling capabilities for the sake of the mythical transparent migration. As soon as you create a variable, your code is suddenly no longer 100% portable nor even easily portable.
As for underscores and Pascal (is that the the same as camel?), I think I prefer camel - I think it's more pleasing on the eye and it makes for ever so slightly shorter object names. It doesn't bother me if research has been done that says that underscore names are easier to read - I use what suits me.
John
Me too! And I disagree with the research about underscores making things more readable especially in the midst of complex formulas.
Since a lot of people confuse Camel Case and Pascal Case and Camel Case does actually have more than one form (according to some http://en.wikipedia.org/wiki/CamelCase), lets say specifically that I really like it where every word in an object name starts with a capital letter and that certain abbreviations are always upper case (ID for example). I'll also say that all else considered and that even Microsoft doesn't appear to have a standard in either their objects or code, I won't but anyone's chops during a code review for if they prefer that same thing but with a lower case first letter (front end developers love that form) in their code but I want things like column names, table names, and names of other objects to follow the "first letter of every word rule" and, for the sake of consistency, to not generally use underscores except where certain exceptions have been laid out by project for purposes of grouping by name (and no... it's not Hungarian Notation). I won't tolerate all lower or upper case at all unless it pops up on Intelli_Sense. Like I said, MS has no naming convention to speak of.
But I do absolutely insist on other things to make all code look the same. All caps for SQL "words", lower case aliases, short lower case schema names, etc, etc.
As for underscores, I also hate typing the damned things and I hate Intelli-Sense. Try typing t.N and see what it comes up with. Try typing INT and see what it comes up with. Try typing ID and see what it comes up with. BLEAH! There's also a practical reason why I don't care for them. As a DBA, I will many times do searches for objects in sys.SQL_Modules. The underscore is a single character wild card for LIKE. Yep... I can work around that but don't like it when I have to. I guess the biggest reason I don't like it is because it was used a lot in Oracle because the default for the return of object and column names is all caps and the underscores were bloody well necessary for readability.
And I really feel sorry for anyone that made their whole database case sensitive. It should only be so in the columns that will contain case sensitive data.
I don't use CURRENT_TIMESTAMP for the same reasons that I don't use underscores and I don't use unnecessarily long names. I even think that GETDATE() is too long. Neither identify what is actually being returned. If I had my druthers, today's date with no time would be TODAY, today's date with the current time would be NOW, and just the current time would be TIME.
Unless, of course, there's an existing (non-Hungarian!) convention in place already.
BWAAA-HAAAA!!!! In the interest of everyone else that we've bored with that subject, I'm going to let that go. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply