Want to start a fight between a bunch of DBAs or database developers? Ask them where the comma should be placed between columns in a SELECT statement. It’s actually a little disturbing just how much emotional value people place in this. Almost as good, tell a database developer you don’t like their naming standard. Hoo boy!
The purpose of a naming standard, I think most of us can agree, is for clarity. We’re defining language so that we all mean the same thing when we say it, right? We want to communicate clearly, so we’re going to implement a naming standard. Fine. Sounds good. And then, you get this:
dbo.tblDdltbl
Umm… Wait. What?
And there are hundreds of these, all with the same first three letters, tbl. Oh, wait, I get it. It’s tbl for Table. Because… um, we need to know what tables in our database are actually tables?
Where’s the clarity?
What about the rest, Ddltbl. If you’ve been to any presentation where I’ve explained Ddltbl (pronounced diddletibble) you must not reveal the secret. I’ll delete your comments. But feel free to guess if you don’t know. No one has ever guessed correctly. Why? Because it’s not even remotely clear. It’s an abbreviation, but not one you’ll find in a dictionary. No, instead, you’ll have to look this up in a special list that matches weird abbreviations, some of them weirder than Ddltbl, with real words. Clarity IS NULL.
OK, what about using spr_ & spu_ for stored procedures? Spr_ means a read procedure and spu_ means update. Clear right? And it’s clearly expandable to insert & delete & merge and procedures that do a mix of things… Wait, we can’t use spm_ for Mix because we’re using that for Merge. I know we’ll use spx_ for procedures that do a mix of things. What’s that, we already used spx_ for XML queries? What idiot did that? Me? Oh, well, let’s see, we’ll use spq_ for procedures that do a mix of things… And clarity just went away again. Not to mention the fact that I want to insert data into the InvoiceDescription table and to get type ahead I have to type ‘spi_I’ before I even get close to being able to select the correct table from code completion. That’s five characters. I’m lazy doggone it! I don’t want to type that much.
Let me now stand up and admit, WAY back at the beginning of my career in data management, I created an entire system with tables that were prefixed ‘tbl’ because I came out of development that used, what was called at the time Hungarian Notation, which was 1-3 characters before a variable to describe that variable. It was a holdover from the days when we needed to account for every letter used because the amount of memory & storage in systems was very tiny. By the gods, I even named every column with a prefix denoting its data type. And, I came up with the spr_, spu_, spd_, spi_, spm_, spq_, spx_ naming convention too (to my utter shame, and I think we had one or two others tossed in there that I don’t remember). Yes, I’ve inflicted this craziness on others. That’s a huge part of why I know it needs to stop (and no, I was NOT responsible for Ddltbl, I fought against it tooth & nail).
We actually have a common language that we can use to communicate clearly what objects are within our databases and what they do. It’s the written and spoken language that we use to communicate to each other when we’re not creating databases. In my case, this is English. Why don’t we just say that the table is Invoice (or Invoices, I’m not about to quibble on that) and that the procedure for adding data to the Invoice table is InvoiceAdd or InvoiceInsert? Why don’t we just use our language skills. Anyone who has had young children has repeated the phrase “Use your words” more than any of us care to think about. Let’s do the same thing with our database objects. Use your words!
Oh, and you should put your comma after the column.
The problem with English is that it is about as clear as esoteric naming standards like what you’ve described. I always try to remember this:
“Say what you mean, not what you say.” – S.I. Hayakawa, _Language in Though and Action_
That should be “Thought” and not “Though.”
Finally, someone who agrees with me that the comma goes AFTER the column name.
I think part of the problem in naming is due to a shortfall in SQL Server. Consider…SSMS is able to identify Stored Procedures and display them in the correct place in the menu. So there is some concept of object typing going on there.
Oh, but you cannot have 2 objects with the same name in a database. If both of those object were of the same type, I could see that. But if you can segregate things by type, why does the name matter? I’d like the Primary Key for all my tables to have the same name as the table. Makes them easy to find and because there are of type ‘primary key’, the IDE can easily distinguish what it is. SQL Server, alas, cannot.
So if SQL was better at recognizing objects by type, not just name, naming conventions would be easier. And now spu_ is irrelevant. The word Update in the name of the stored procedure tells you it updates, the rest of the name should tell you what object it updates, and the fact that it falls into the stored procedure bucket tells you it is a stored procedure.
I never write a stored procedure that updates a table and then another that would insert to that same table and then another to delete and certainly not merge. All my procedures begin with USP_, views with UV_ and functions(very rare) start with UF_, triggers with TR_(rare too).
My stored procedures have an @Mode parameter and by doing so I can tell it I need to execute an insert, update, delete, etc command and best of all I can always add functionality to the proc without breaking anything that calls it because the @mode parameter is there already and any internal changes to the proc are just that and invisible to the calling app or whatever else uses it. I also always add spare input variables (usually 3) to the proc for future use. Sometimes they never get touched and sometimes they do. I’ve never regretted this.
I should never have to prefix a table name with “tbl” because if all other objects are prefixed properly it *should* be safe to assume that the object is a table. Do others create a view or procedure with a name totally in violation of naming standards? Yes and they should be beaten with a very heavy chain with a padlock on the end.
And commas should always go after the column name then a space before the next column. And for Christ sake, indent code.
Grant,
naming conventions have evolved a lot lately, you are right about that.
There are several ways to use strategic naming of objects: now we have even the ability to create and use different filegroups and schemas as part of the distinguishing elements of the objects.
For example, all tables related to x are on filegroup A, the rest on B. And then the objects on filegroup A can belong to schema C or D, which defines even further the distinction. (x can be anything, really, from department to developer even)
Furthermore, there is a nice feature Extended Properties, which can be used for every object to document and to search. (Yes, some coding is needed, but Red-gate has great tools for that as well).
And finally, I really like to use things like “tblDdltbl_NOT_For_PROD” so I force developers to know that the table is in testing stage now and so they do not write code which pulls data for the production sites. (long story and I can’t post it here, but it makes sense, trust me on that).
Feodor
Hey Brian,
Yeah, not only is English grossly imperfect, but those of us who use it are even more so (myself most of all). But we have a chance at agreeing on what the word Invoice might mean. I’ve heard hundreds of guesses at what Ddltbl means.
Hi James,
And if it works for you, go for it. Still seems crazy to me, but I won’t get in your way.
Oh, but your multi-layered procs, you do know that they will compile all statements within the proc on the first call, regardless of the @Mode value passed, right? If you regularly UPDATE a single row, but DELETE thousands, one requiring a seek, the other scan on the execution plans, you might see the opposite because of the compile. Just saying.
Feodor,
Yeah, I know about the use of filegroups & schemas, etc. Good call. They really do change how we can manage things.
I’m OK with a name like that during development. Heck, I assume development is crazy cowboy land where anything goes. It’s after that it matters.
I understand how @James stored procedures may create some ‘interesting’ execution plans. Wouldn’t a stored procedure that used a MERGE have the same issue.
Oh yeah, after indentation (Thanks, James) capitalize keywords!
MERGE itself creates interesting execution plans, yes. But different than what you get from multiple statements because MERGE is different. Note, I didn’t say better. It may or may not be.
Grant,
Yes, I’m aware of the compilation but these types of procedures are not called thousands of times a minute. Some more frequently than others and some rarely like once a day. I also use dynamic SQL a good bit because a lot of procedures had to exist for multiple lines of business. This was all for an insurance claims package and as an example, there would be a report for Workers’ Comp and one for General Liability and yet another for Accident and Sickness. The reports looked largely the same (because they served the same purpose) but pulled from different tables and different column names (WC prefix for Comp and GL for Liability). By using a mode I could prepare the SQL statement with the proper table name and where necessary the proper column names (injury date or loss date). So the obvious result was a single variable and and a single execution. Best of all there was only one object to compile instead of an SP for each line of coverage.
I know this may not sound desirable in many cases but this wasn’t for an in-house app. If an update has to be sent to clients it is easier to push out a single procedure that is a jack of all trades for its intended purposes.
To be clear, my update, delete, insert, select, etc are never in separate statements in one procedure. All are in a single dynamic variable so that would certainly cut down on compilation or so I would hope.
I have seen the naming convention argument at both conferences and my own team. It does have a tendency to fire people up but I always argue against any kind of object type prefix.
We do prefix our object but we prefix them with with a standard company abbreviation that relates data to a product module. For example, any views, stored procedures, etc. related to Medicaid get prefixed with “MDCD” so I know what it’s real attachment is. If I prefix a view with “tbl” and later decide it’s a performance issue and want to build a table out, I don’t want to have the wrong “standard” prefix now and I also don’t want to spend hours fixing anything that references the view to remove “tbl”. Outside of that, I’ve always found that sys.objects.type_desc solves most of my questions about what something is.
Also, I’ve grown to prefer commas first, but only because it let’s me easily identify where my list ends.
Honestly I don’t think it really matters what naming convention you use as long as you are consistent. By by “you” I mean everyone who touches that particular application uses the same naming convention for that particular application. At least that way if you are given a set of rules then you can (hopefully) understand what is going on.
BTW I think the comma should go in the middle of the column.
Yep… commas after the column. 🙂
I partially agree with the post and disagree to all (except column commas) of Bryant McClellan’s comment. 🙂
My ideal is for tables named according to their data… customer, customer_comment, product….. but it helps sooooooo very much for views to be v_customer_address, v_product_by_type….. and stored procedures to be s_inventory_recon, s_product_archive…… Why? Because it’s huge to me to know the difference when reading code between a table being selected or a view. And knowing that columns are being selected from a view instead of a data returning procedure or vice versa. It’s quick, simple, yet abundantly clear.
Sometimes determining the type of object is painless, sometimes not. A simple v_ or s_ solves that problem so easily without mucking up object names or adding complexity.
The number one rule – unbreakable rule – is for the same data to have the same name in all objects….. invoice_dt is the same data type and contains the same kind of data in every single table it appears in.
The idea of having a primary key named the same as a table wouldn’t work for me. There may be compound keys and “customer” isn’t enough information if it is a field. I know customer_type_id and customer_nam are fields and what kind of data exists within them even when I can’t see it. So quick and consistent to work with.
But then… there is no universal best way here. Only really rotten ways to avoid, such as tblDbltbl. 🙂
Anne
Agree 100%. I’ve never liked the prefixes on any objects except perhaps stored procs. The only reason I prefix those is to keep groups of them together when looking for them in EM. One of the things I wish MS would ‘borrow’ from oracle is packages. They don’t have to be compiled, just for organzations’ sake.
Oh, and comma after the column, you heathens.
Scott
Comma after column.
No underscores in names! Use CamelCase.
and use begin…end even if block of code is one-liner.
And I didn’t even mention semi-colons.
Oh yeah, Pascal Case. Then again, caMel case automatically gives you prefixes. Isn’t that where the practice was born?
Semi-colons at the end of each statement (Had to climb aboard that wagon).
As @Michael points out, liberal use of BEGIN/END blocks.
And in stored procedures and triggers, SET NOCOUNT ON. Always.
[…] SQL Server Naming Standards - Grant Fritchey (Blog|Twitter) has his say on this highly personal topic. As you can imagine, the comments are quite passionate. […]
[…] Fritchey has a blog post about naming standards, including an excerable tblDdltbl. The comments thread then becomes people defending their […]
Commas before.
Down with unnecessary semicolons.
Down with unnecessary Begin … Ends.
vw…. and sp… prefixes – tables are off limits.
I could never understand prefixing tables with tbl etc – until I started accessing databases through reporting software. These frequently show ‘available objects’ and it’s really useful to know what your looking when your given a list that includes tables, views and stored procedures. It may not matter to the dba but it is a kindness to others.
Hi Julie,
Thanks for the feedback. But does it matter to the report builder where they’re getting data? If a view has what they want does it matter that it’s designated as a view through the name? I wouldn’t think so in most circumstances. I guess it depends on what the report building tool is doing.
Commas go at the beginning of the line.
“Hungarian” notation was a Microsoft convention before dot net, so named for the home country of Charles Simonyi.
Putting commas at the beginning of the line is just more practical. Say I want to test a query like these without val3–which is easier to comment out?
SELECT
val1,
val2,
–val3 –oops, it breaks
SELECT
val1
,val2
–,val3 –works fine
SWillis
But what if I want to do this
SELECT –val1
,val2
,val3
That breaks too. Just saying. I actually don’t care about the comma’s that much. I was mainly stating that as a joke, but I love how everyone engages on it.
I went back and forth with myself about tibbling, especially Tables, for years. This is what decided it in favor of, and I never see this mentioned:
Whenever one changes existing functionality, it’s essential to crawl code to scope the impact and be sure not to break anything. If my Tables are named “Noun”, I’m liable to get a lot of spurious hits (depending on the actual noun). If I get a hit on “tblNoun”, I’m pretty sure it’s my Table.
SWills, the same argument can be used for a comma at the end of the field. Say I want to test without Val1
SELECT
— val1, –works fine
val2,
val3
SELECT
— val1 –oops, it breaks
,val2
,val3
Kenneth,
Sorry, didn’t see you had commented there. Thanks!
I agree with Kenneth Fisher…. and not only do commas before columns fail to render code easier to debug, they also look terrible to me. And all of us have our habits, and I care very much about the readability of my code. I am so used to writing it with commas after columns that I couldn’t do it any other way. So, of course, it’s the best. 🙂
Any good developer and/or DBA should be able to adjust to any style. When doing modifications the modification should maintain the same style. When working for another company… consultants… you should adhere to their standards and conventions. I’ve been working in IT for over 30 years and any developer that can’t make adjustments on the fly needs to figure out that it is not he or she that is important, it is the job that is important. It is only in those rare occasions where you are defining standards and conventions from scratch that any of this really matters.
I agree with Scott. I’ll work with whatever style my client’s db is using if not totally outrageous, best practices or not. As for commas before or after, Ken pointed out one exception. I still find commenting and uncommenting lines during development a lot easier when the comma is in front. But it’s not a hill I would die on. That’s why RedGate’s SQL Prompt (or whatever tool one may use) is so valuable…set up your code formatting according to the production specs, then ‘CTRL-K-Y’ and like magic the code is reformatted and everyone is happy! 🙂
VERY rarely do I comment out the first field in a SELECT statement – it’s usually the primary key. But I often comment out other fields, and fairly regularly it’s the last one, that’s just been added and I’m experimenting with something new. In that case, commas before make much more sense. And I’m lucky – I’m a one-man show, so I can do whatever I please.
Nice article topic! I too have struggled with naming convention for years. The first thing that drove me nuts was trying rem out lines from large SELECT and UPDATE statements were the comma was after the column. Years ago I switched to placing the comma before the column name and never looked back.
SELECT
[MyTableID]
,[MyProductionColumn1]
,[MyProductionColumn2]
— Testing Select Columns Easy to un-Rem
–,[MyTestingColumn3]
–,[MyTestingColumn4]
,[MyProductionColumn3]
,[MyProductionColumn4]
WHERE
— Testing Where Clauses Easy to un-Rem
–[MyTestingColumn3] IS NOT NULL
–AND [MyTestingColumn4] IS NULL
[MyProductionColumn3] IS NOT NULL
AND [MyProductionColumn4] IS NULL
Navigating a large 50+ column MERGE statement I can Rem and Un-Rem in seconds (placing the cursor before the Rem and delete, delete, enter, delete, delete, enter etc, etc. then hyphen, hyphen, enter, hyphen, hyphen, enter etc, etc.) The simplist solution is often the best!
I have adopted camel case for our naming convention and I’ve written an application that utilizes this for ad-hoc reports so that the end user sees their report headers in plain english.
The heart of this application is a function “CamelCaseNameSplit”.
Grant,
I’ll forgive you, but only this once.
I still vote for commas in the middle
SELECT
va,l1
va,l2
va,l3
It doesn’t work but it looks amusing. (FYI that is my vote for it doesn’t matter, use what works for you.)
I had successful experience in SQL naming and I like suggest it to others. mainly I am agree with what Grant write and this is a complimentary. I do this for 2 MIS project with more than 300 tables and about 10 person as team member and we were all satisfied.
1. use schema for group of tables which they are about one subject in short form. for example SAL for Sales. you can have list for describing these short form and your list is not long.
2.Don’t use abbreviations as much as it is possible
3.use English name of entities for table names. Invoice was very good example. and when you combine it with schema it goes more clear…
SAL.Invoice= invoice for sales system or department.
PUR.Invoice = invoice for purchase system or department.
4.if functionality is important for you use it at end for example SAL.Invoice_Update.
regularly each project has its own standard. and you use 1 way for your update. so are sure it is procedure and it is not function or trigger. if still you want sp in naming you can add it at end. SAL.Invoice_Update_SP
5.For naming a key use this rule: Table name+ID
for both PK and FK. it helps you a lot.for example in joining in knowing tables relationships, etc.
6.when you have a table with 2 word name use main name at first. for example InvoiceDiscount is much better than DiscountInvoice.
IMPORTANT: last rule is important in managing your DB objects, better clarity,etc.
look at your tables list. how could you understand what are the tables in the direct relation with one table. I mean all make one subject?
I can find it very easily. I just roll up scroll and go at right place. and then just look and do nothing. with this naming rules things go after each other. for example my tables in relation with Invoice are next to it:
SAL.Invoice
SAL.InvoiceDetails
SAL.InvoiceDiscounts
SAL.InvoiceTax
and like this way I can find nearly all procedures for invoice
SAL.Invoice_Insert
SAL.Invoice_Validation
SAL.InvoiceDetail_CalculateDiscount
VERY nice article, Grant! Glad to see a kindred spirit on naming and formatting.
I had thought about publishing my own naming and formatting standards but then decided the better of it. It’s just too opinionated a subject for me so I write code and demand folks follow my lead in my shop or get out. If I’m in someone else’s shop, then I’ll follow whatever standard they have or the actual conventions that show up in the existing database I may be working on. If it’s all over the place, then I’ll write it my way. 😉
Your good article even managed to pull a couple of people out of the woodwork who justify non-scalable code with low usage or low row counts. Well done. 😉
Hey Jeff,
Glad to hear you agree with me. But I wouldn’t have expected anything else. Jokes about comma’s aside, I’m just trying to make things clear.
Heh… Sorry Grant. I wasn’t jabbing at you. It’s just my ironic way of agreeing with you.
Again, great blog article. Keep up the great work.
BWAA-HAAA!!!! Well I’ll be! Even the Scary DBA gets spammed by moroffs! 🙂
Wow! Two in a row! I don’t know about anyone else but I’ll never ever use someone that spammed a thread like this… especially law firms that really should know better. Can’t trust them to do the right thing because they’ve already proven that they won’t.
Hey Grant, looks like you need to do a bit of housekeeping in the responses. 🙂
Wow, sorry about that guys. Cleaned out the mess.
In my opinion you need to maintain consistancy in a database and not mix standards. As long as it is meaningful and field names like GRBP are not used – I don’t see the problem. Same goes for objects names camel case or _ for clarification.
Oh and commas – between the column names please – they work better there
I absolutely agree that consistancy is one of the most important things in most cases. The only time I don’t agree is when the “consistancy” is consistantly bad.
Unfortunately for both me and some of the shops I’ve worked in, “consistantly bad” has been the norm rather than the exception.
There’s also a strong correlation between the amount of “crap code” and how many people are allowed to promote their own code to production.
I’m with Jeff. Consistency is important, but when it’s consistently bad, that’s a problem. DDLTBL was consistent, but stupid.
Great read, good discussions in comments too. I like standards and guidelines but unfortunately my workplace has them but doesn’t enforce them and now we have new staff writing their own but same problem no enforcement. We also don’t have DBAs.
What is everyone’s thoughts on OLTP AND OLAP two different standards? Thanks in advance.
Hello Heidi,
Not enforcing a standard is basically the same as
not having one. Not much you can do about that.
As to OLTP & OLAP, no, I don’t see much difference really. Same thing if I’m working in any programming language. I want to name things in a way that assists clarity & communication. IT is tough enough without adding a secret language on top of the already obscure & obtuse languages that we use.
Of course, just opinions.