Coding Standards - Part 1
-
When naming index include the field names and the order.
e.g. IX_tblName_Field1_Field2
This helps when writing the where clause because you can tell by the name what order to write the criteria
E.g. This will not use the index
WHERE field2 = @Value2 and field1 = @Value1
This will use the index:
WHERE field1 = @Value1 and field2 = @Value2
Also it helps when viewing the execution plan becuase you know on what fields the index is being used.
Regards
AP
-
Interesting article. The naming convention for roles which we use is: rl {database name} {action} {object(s) worked upon}. An example of this might be a role which executes Stored Procedures in the Sales database, which would be named: rlSalesExecSPs.
The reasons for this level of granularity are that:
- “rl” distinguishes roles from other users as they are all held in the sysusers table and, from my perspective, it can be helpful to be able to distinguish users & roles
- the database name is useful as it clearly shows you where the role is used
- the “action” and “objects worked” on tell you something about what the role does; we try to use roles in a modular fashion so a particular user might have one role that executes Stored Procedures & another that calls Functions: rlSalesExecSPs & rlSalesCallFNs
-
Steve I enjoyed your article. Most of my DBA experience has been with Oracle. But the last 2 years have been SQL Server.
With indexes I prefer CDX for Clustered and NDX for non clustered but other than that I agree with your article.
One area I have noticed in coding standards that is almost always missing is comments.
Why do we get 2 different types of comments -- or /* */ and can we use them to our advantage.
I use -- for object headers and real documentation. Real documentation must always mention the object name. A comment like "This routine will calculte the gross margin" is no good. It must be usp_CalcGrossMargin will calculate ...
I use /* */ to comment out code or anything that is not ment to be documentation.
I had a script (in Oracle) that could extract all -- lines (coments).
I used that to produce the start of the real system documentation and modification history.
Regards
-
Interesting on the comments. Since some things are multiline, like a dscription of a proc, the /**/ is handy so you aren't editing around --s all day.
My tendancy is to use /**/ for the header comments for that reason. Inline, I tend to use -- for no other reason than I can quickly with CTRL-Shirt-C, comment out selected lines in QA. Uncomment is also very quick. If there are multiple statements in code that I want to comment out, but maybe run together, like in a script, I tend to use /**/.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
Bravo, Steve! I think of myself as already using good coding standards (almost all the same as yours,) but in fact, you showed me a few things I hadn't thought about.
Vyas Kondreddi has an approach to naming DTS packages that is worth taking a look at. It's part of this article:
http://vyaskn.tripod.com/sql_server_dts_best_practices.htm
Best regards,
SteveR
-
Hey Joe, why don't you tell us how you really feel!? <g>
I can report from my own experience that I do find T-SQL written by others in a style similar to what Steve suggests to be very readable. Also, other SQL Server developers have told me that they find my code easily understandable (also written with similar conventions.)
That said, how can I ignore Joe Celko? I will check out ISO-11179 and Google on "AIRMICS CMU SEI readablity" to see what you're saying - thanks - there's always something new to learn and a possible better way to aspire to.
Best regards,
SteveR
-
OK, I just did a few minutes surfing, and Joe does have a lot of worthwhile things to say about coding standards and readability out on the Web.
I suggest, for those who are interested, to do some more research on the Web and report back our findings in this thread.
For example, Joe talks about things like capitalization versus lowercase, underscores versus camelCase or PascalCase (or did I get that backwards,) how to treat reserved words versus user-defined objects, etc.
Joe, as always, thanks for your contributions to the Art. And please chime right in...
I have a feeling that this is going to be a very lllllllllllllllllooooooooonnnnnnnngggggg thread.
Not that there's anything wrong with that!
-
Almost hit the nail on the head. I could not agree more about using proper case for names in SQL Server - many studies have shown that mixed case is far more readable and therefore understandable than single (either upper or lower) case. The only major point which I would add is that underscores should be avoided! If you use lower case prefixes instead of suffixes for trigger names, index names, etc., an underscore is completely unnecessary, since the first letter following the prefix will be upper case. For example: tgriuOrders
tgrdCustomerPhoneNumbers
ixncuOrderDetailsOrderFK
ixcOrdersPK
You might notice that I would also suggest using upper-case PK/FK suffixes for primary key and foreign key column names - this makes coding far simpler when used consistently throughout the database.
Kindest Regards,
Matt Gauch
-
Note: there is one known issue in QA. If the comment block contains the word GO (or whatever is defined as the batch seperator in QA's options see Optionbs->Connection(tab)->Batch Seperator(text field)) on a line by itself it will throw the error:
Server: Msg 113, Level 15, State 1, Line 1
Missing end comment mark '*/'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '*'.
Ex.
/*
go
*/
-
Joe it is all fine and wonderful that ISO-11179 exists but until I read in your book "DATA & Databases: Concepts In Practice" I had never heard about it or saw anything suggesting it. As well, I have found no vendors who are pushing it or any software being built around the standards for enforcement in developement.
So as far as the world is concerned it doesn't exist, and until it is pushed considerably more into the SQL community at large it will never reach it's goal in the forseeable future.
Unfortunately until it is pushed harder people have no real choice but to develope their own personal standards. ANd the last text book I got my hands on made no mention and itself, I believe, broke several standards.
-
>>Some of us actually follow the basic standards of our trade. Some of us look a the US Government standards, since they are the **largest users of data processing on Earth **.<<
And many don't but mostly becuase they have little time and almost no compuntion to send them down this path.
>>Go to White Marsh consulting for the some of the details. ISO and the US Government are pushing ISO-11179.<<
Thanks for the reference.
>>Wake up and smell the coffee! The world is much more than just a simple PC on a desktop. The horizon is about 3 years.
I just wrote a book with the working title SQL PROGRAMMING STYLE that goes into formatting and naming conventions. <<
Maybe I should have said a bit differently as I have seen beyond the desktop sometimes more than I want to have had.
However won't lie I probably will find your book on EBay or ina used book store when I get to it, basically because I am cheap.
>>No, you can lift a mouse, click and find the standards. Also, textbooks are always a bit behind -- programmers are more "behinder" than text books these days.<<
Again bad wording on my part, I should have said "feel like they have to". But yes I found the standards and actually obtained a copy (a bit older).
But as for being "behinder" that is generally a personal choice, either you move forward and progress your knowledge, or stay "behinder". Found many mainframers live this way and get all knids of unglued when you move off to distributed systems.
-
Joe,
I downloaded the ISO standard and read Part 5, "Naming and identification principles for data elements." Thank you very much for cluing me into this - it wasn't even on my radar.
Before I continue, I need to mention a bit about my background. I have a masters degree in mechanical engineering and a masters in computers science (both pretty old.) I worked as a mechanical engineer in the electric power industry for 18 years, the last 6 of which were in support of a nuclear power plant. Then 10 years of IT support for the nuclear plant. Another 7 years as an application and database developer for a range of businesses brings us to the present. The point of all this is that I've consumed a lot of specs and standards in my time, and even written a few "local" specs and standards myself.
But I found the section of ISO 11179 that I read, Part 5, very difficult to understand. Furthermore, IMO, the examples are weak and insufficient. As a result, I think I understand some of *what* they are driving at, but not completely, and certainly don't understand the *why*
If I get their point, the standard provides 3 and sometimes 4 (Object Class, Property, Representation, and sometimes Qualifier) "pieces" that must make up a data element name.
I can understand Object Class. I can understand Property. But I don't understand the concept of Representation and Qualifier *as detailed by the standard*.
Part of this is the dense writing style. Part is because of the weak (IMO) examples.
Most of all, I don't get any sense of *WHY* they want you to use this approach. Here are some examples of what I mean.
Tree_Height_Measure... First of all, although I don't see where they mention this, all of the examples seem to be data elements that are Attributes of an Entity. OK, I see that Tree is the Object Class and Height is the property. But what exactly does "Measure" add that we don't already know? Isn't "Height" obviously a "Measure"? So what is this Representation part all about? Maybe you could give us a better example. I'm trying to think of some other word that I could use instead of "Measure" that would further modify ("represent??") Tree_Height such that it would imply a different attribute data element, and I'm stuck.
I have the same problem with another example - Cost_Total_Amount. What other Representation could be appended to Cost_Total other than Amount? I mean Cost_Total *is* an amount. You can see why I'm frustrated reading this - I just don't get it.
I have more heretical questions. The Standard (at least Part 5) doesn't explain to me the value of using the Object Class as part of the data element name. For example, Employee_Last_Name is an attribute of an Entity that is probably called Employee. What is the value of prepending "Employee" to each of the attributes that describe an Entity? I don't think of attributes as having an existance independent of the Entity they describe. So "Last_Name" is in my mind, tied to Employee, and I think of it as "Employee.Last_Name". Again, I'm not convinced there is any real value in "Employee.Employee_Last_Name."
Some of this, IMO, falls into the category of "religion" rather than something that has an intrinsic value. Now there is nothing wrong with religion. I was "brought up" in the database world with a certain religon that I still adhere to. But there are other "religions" out there that I think are equally valid, as long as the practitioners follow their religion consistantly.
So there are people who will find Employee.Employee_Last_Name better and others who much prefer Employee.Last_Name.
And that brings me to another point - what is the reason for having only unqiue attribute names? Again, my concept of life is that the attribute has no existance without the Entity, so Entity.Attribut *is* unique, isn't it? What am I missing? Is it possible that the "no duplicates" restriction is tied to the concept of the meta-data repository, which is envisioned such that each attribute data element name must be unique?
I've been talking just about attributes up until now. Does the standard cover Entity naming? I don't see it in this section at all, and I don't see how the ObjectClass-[Qualifier]-Property-Representation approach would even apply.
Joe, I'm not trying to be difficult here - I'm really grappling with the concepts.
Also, I must admit that I do approach *some* standards with a grain of salt. Now, I wouldn't do anything at all that conflicted with, for example, the ASME Boiler & Pressure Vessel Code. But I remember an IEEE standard of the '80's (I think) that was for an equipment numbering/naming convention to be used in the databases that were evolving at the time as nuclear, and then fossil power plant design and especially maintenance moved from paper-based systems to (mostly) mainframe databases and applications. The standard recommended a very complex hierarchial numbering protocol, totally ignoring the idea of a relational database representing entities and attributes. That is, there were a zillion things coded into the equipment ID, the Primary Key (although I don't know if those old IMS and similar systems had any concept of Primary Key - I was just an analyst at the time) - plant, generating unit, system, subsytem, etc etc... you almost didn't need other attributes once you were done with the Equipment ID 😉 You woulda hated it!
I know you will explain this much better in your book that is coming out next year - and I'll make sure to buy a copy (or at least get The Boss to buy one for me.) But in the meantime, can you shed some light as to what the ISO standard is getting at? ... and could you also comment on the questions that I raised? I would be very grateful.
Best regards,
SteveR
-
From what I have read, and please feel free to correct me if you feel it necessary, but the ISO standard seems more in response to years of frustration in people needing to use a "data dictionary" to discern cryptic names in database implementations rather than an attempt to implement some standard that is broadly useable across platforms. Now, before you go off on my "attack upon the standard" understand that I too have experienced great frustration in dealing with some of those forementioned "data dictionary" documents put out by DBA's and such but I also used them since, in many cases, I was dealing with an implementation that we wanted to have consistent across platforms and some were "big metal" mainframes that only allowed 8 characters for a column in a database. Such systems preclude the use of underscores and several other characters and make for some truly nasty names.
Now, with all of this noted above, the more I read of the ISO standard the more I came to the conclusion I did. While worth the effort to get to this point, there are still far too many platforms to be dealt with to fall to a single standard in an exclusive manner.
One of the things I have done consistently over my career is get rid of all vowels in words used and to also get rid of the "and", "the" and other similar words. I have also gravitated towards getting rid of prefixes as much as possible since, as all of us MS SQL types are well aware, the use of "sp_" in front of a procedure name can cause some serious problems and it is not the only prefix that can cause a problem. However, I still prefer to use "p" for procedures, "f" for functions, etc... So sue me.
The one thing I can not stand in the database world is the prefix on tables though... The very concept of using "t_" or "tbl_" drives me up a wall and everytime I run across one of those "MS Access experts" it is one of the first disagreements to arise in conversation.
-
Great discussions and glad to read about it. Always good to know there are standards out there beyond the PC world. Having worked in mostly non-government shops, I have not seen standards in many places, so the intention of the article was to introduce what I do and how I have found it to work. My work mostly is based on what I have seen and used, not on academic ideas.
That being said, I'll look into those references. Always glad to learn something new.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
Joe,
Being a data modeling purist, I completely agree that using PK/FK suffixes, naming a data element for how it is used, is WRONG. But, we're talking about PHYSICAL database design and implementation here, NOT standards for a logical data model. If a consultant came to me with prefixes or suffixes in his data attribute names in a data model, I'd fire him for not understanding the distinction between the logical and physical. My aim with a group of code jockeys is to get high quality (bug-free) code implemented quickly. Including the appropriate level of meta-data in database object names (i.e. columns) only helps achieve that objective.
Kindest Regards,
Matt Gauch
Viewing 15 posts - 31 through 45 (of 50 total)
You must be logged in to reply to this topic. Login to reply