SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SQL Server Naming Standards

dirtywindowWant 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:


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 Scary DBA

I have twenty+ years experience in IT. That time was spent in technical support, development and database administration. I work forRed Gate Software as a Product Evangelist. I write articles for publication at SQL Server Central, Simple-Talk, PASS Book Reviews and SQL Server Standard. I have published two books, ”Understanding SQL Server Execution Plans” and “SQL Server 2008 Query Performance Tuning Distilled.” I’m one of the founding officers of the Southern New England SQL Server Users Group and its current president. I also work on part-time, short-term, off-site consulting contracts. In 2009 and 2010 I was awarded as a Microsoft SQL Server MVP. In the past I’ve been called rough, intimidating and scary. To which I usually reply, “Good.” You can contact me through grant -at- scarydba dot kom (unobfuscate as necessary).


Leave a comment on the original post [www.scarydba.com, opens in a new window]

Loading comments...