August 27, 2008 at 8:36 pm
I'm looking for an industry wide standard for naming tables, views etc within an SQL database. I've found plenty on the net, and this site, but they are all slightly different.
I was hoping there was a set of standards published by Mirocosoft or an industry body?? Or a well known standard?? e.g http://msdn.microsoft.com/en-us/library/xzf533w0(VS.71).aspx
August 27, 2008 at 9:39 pm
I remember years ago MS had some recommendations but even that there was nothing concrete that I had found. Still haven't seen any yet. If you find them please post so that I can see them too. 😀
The most important thing is to pick one and then stick to it. That alone will take enough work to get implemented and consistent especially if your development team is more than just you.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 28, 2008 at 6:43 am
The closest thing you will get is to not use spaces or reserved words in table and column names. My personal opinion is to that table names should be plural and because I always install SQL Server with a Case Insensitive collation I like all lower case names with "_" separating words. So something like customer_addresses.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 28, 2008 at 6:45 am
Jack - Is that just so that you don't have to hit the shift key. 😛
Seriously though, I would like to hear your rationale on that. Thanks for sharing in advance.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 28, 2008 at 6:54 am
I'm assuming you mean my rationale for lower case with "_" (I have to hit shift for the "_").
As I said since I use a case insensitive collation it really makes no sense to use camel case or hungarian notation. Another reason is that case DOES matter in the procedure cache, so select * from CustomerAddresses and select * from customeraddresses return the same results but have different plans. So I like customer_addresses because I always type it the same way.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 28, 2008 at 7:06 am
Jack Corbett (8/28/2008)
Yeah, that was my humor. Sorry, most people miss it, not just you. Bad David.
Thanks for sharing your other thoughts as well. I have always tried to glean from others on their reasons for standards. There are a lot of variations out there and many have some great reasons for doing things the way they do. Yours sounds pretty reasonable as well.
Thanks agian.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 28, 2008 at 7:14 am
I have attached the doc I use for myself.
August 28, 2008 at 7:15 am
I don't know of a major standard. A few guidelines, like don't use reserved words in the table name, sure, but no real standards.
I use CamelCase for table names, and use the "_" character for join tables in many-to-many relations. Either one works.
Just pick something you like, document it, and stick to it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 28, 2008 at 7:30 am
GSquared (8/28/2008)
I don't know of a major standard. A few guidelines, like don't use reserved words in the table name, sure, but no real standards.I use CamelCase for table names, and use the "_" character for join tables in many-to-many relations. Either one works.
Just pick something you like, document it, and stick to it.
True, I agree to that. Whatever you adopt, make it the standard, document it and most importantly, stick to that.
August 28, 2008 at 5:17 pm
Thanks for the document Rajan.
It looks like if I want to use a standard and I'll have to just create one.
Thanks..
September 10, 2008 at 4:15 pm
Over my carreer I have worked at several different companies. They all had different standards.
Here is what I currently try to do...
Rule 1: No spaces in object names, or field names!
Rule 2: No reserved words in object names or field names.
Rule 3: I like to use CamelCase for object names and they are always singular.
Rule 4: No Hungarian notation in object names.
Rule 5: No underscores in object names with the exception of SPs, indexes, FK's
For SPs, I will use an underscore as the last character to denote a "Private" sp. IE: one that is only called by a DBA or another SP. We do this as we use a script to assign permissions to our objects and this way I can exclude those SPs from getting Execute permissions assigned to them in the script without having to know what their names are.
For Indexes the name is typically XIETableName_FieldList
For FKs the name is FKChildTable_ParentTable
Gary Johnson
Sr Database Engineer
September 11, 2008 at 1:15 am
I would also suggest that if you are adding a date to a table you use YYYYMMDD if for no other reason than being able to view them in order.
Another option for prefixing UDF's is 'tfn' for Table-valued Function, 'sfn' for Scalar and 'afn' for Aggregate. This way helps claify what type of UDF it is.
I have loads of rules I've set myself for naming, just like many others, and reading peoples views here has got me thinkging about one or two of my own, so I'm keen to see what other people have to say on the matter.
September 11, 2008 at 8:25 am
wildh (9/11/2008)
I would also suggest that if you are adding a date to a table you use YYYYMMDD if for no other reason than being able to view them in order.
Whereas I would put the date as a value in the table, not in the name of the table.
It's all based on what works best for you and your team.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 12, 2008 at 3:09 am
When all is said and done, naming standards are not very important. (cue flames!)
I agree they can help make life easier for the DBA, but do they add any value to the business?
I think it is right for a DBA to organise their own work environment to make things easier, and naming standards have a place in this. It is also right for a DBA to work with the Development team to establish a common naming standard. You can tell how much management value naming standards by the amount of support given to a DBA when the standards are violated. If a manager has to choose between spending on a performance fix or a naming standard fix, always be surprised if they choose to spend to fix the naming standard violation.
However, if you use a tool to aid object design (e.g. Erwin, etc) and that tool needs names to use CamelCase or to use separated_names, then you will get support to fix violations that are not freindly to the tool. Against that, the business will inevitably buy some products that violate all your naming standards but these will need the same level of DBA support as fully-compliant code.
There are a few areas where you can set standards that will get management backing, as below, but CamelCase and many other things are personal preference:
* Never start a user stored procedure with 'sp_', as this has a known performance hit.
* Use the same name for the same data item wherever it is referenced, to minimise confusion.
* Objects should have the same name in your Dev Test and Prod environments, so that deployments can be scripted rather than needing ad-hoc manual intervention.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
September 12, 2008 at 8:34 am
Naming standards do add value to the business, in that they make future development/debugging faster and easier.
Name a business-critical table "Table", give its columns names that start with "Col1", "Col2", and so on.
Just see how much added expense there is a year later when it needs to have new functionality added to the database and the applications on top of it.
Money removed from the bottom line has value at least equal to money added to income. Often more.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply