THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Why abbreviate schema names?

Here’s a quick question for you. Do you abbreviate the names of schemas in SQL Server? I ask because I see that a lot of people do and quite frankly I don’t really see a justification for it. Let me show you what I mean. What is more meaningful? This:

abbreviated schema names

Or this:

unabbreviated schema names

Personally speaking, I would rather deal with

  • [product], [reconciliation], [report], [sales]

than

  • [prd], [rec], [rpt], [sls]

because they’re easier to understand (and that’s vital when introducing newcomers to a project] yet I’m clearly in a minority because most places I go people seem to prefer to use abbreviated three letter schema names like I show in the first screenshot above.

I asked the same question on Twitter and here are some of the responses that I got:

image

image

image

Adam Machanic raise a very good point in his tweet (above) about intellisense – it negates the only justification I can think for abbreviated schema names, that being that they are quicker to type.

The consensus from those tweets seems to be that abbreviating schema names isn’t commonly practised but that doesn’t jive with what I see in my work day-in day-out. So, dear reader, how about you? Do you use abbreviated schema names or not? Note that there is no right or wrong answer here, I’m just interested to know.

@JamieT

P.S. You do USE schemas, right? ;)

Published Monday, March 08, 2010 11:07 PM by jamiet
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Nathan Griffiths said:

I've never seen anywhere using three-letter schema names but your comments are right, I can't think of any justification for naming schemas like that either. We do use schemas and they are a great feature, but I have seen some instances where developers have started to treat the schema name as part of the object name, e.g. "Policy.Details" instead of "Policy.PolicyDetails" - this often leads to multiple tables with the same name on different schemas, e.g. "Contract.Details" and "Policy.Details"  - then someone refers to the "Details" table.... I think this can lead to unnecessary confusion.

March 8, 2010 6:28 PM
 

AaronBertrand said:

I agree with Nathan, I don't see anything as obtuse as the example you cite, but as a counter-point I do agree that typing longer schemas can be annoying in many-table queries (one of the reasons I like dbo).  I am using schemas but I generally turn off IntelliSense as it seems to get in my way more often than it helps.  Every couple of months I give it another spin (both the built-in and the Red-Gate version) but I always tend to turn it off quickly.

March 8, 2010 6:44 PM
 

Tom Groszko said:

Likely SQL developers do it for the same reason they obfuscate table name aliases. With intellesence typing is not an issue.

March 8, 2010 7:18 PM
 

James @ BI Monkey said:

All abbreviation in Column / Table / Filed names drives me nuts. There's no need for it, especially now intellisense is here to save your fingers. Why call a table ClmRecGrsAmt when you can call it ClaimRecievedGrossAmount, something that may actually make sense?

March 9, 2010 12:38 AM
 

jamiet said:

Ah aliases....I must admit I do use aliases which are, inherently, abbreviations. I might have to admit to double standards here :)

March 9, 2010 3:55 AM
 

Bill said:

Our data modeling team are all former Mainframe COBOL folks and the naming standard for tables was 18 characters when I started.  After petitioning the Standards Committee, yes we have one, we were able to have the length of table names increased to 32 characters---ostensibly to make things more readable.  Now instead of SR_M_ADV_IVC_HST_S we get things like SHRCLASMSLSLD_PRTFFDSHRCLAS_VM

March 9, 2010 4:45 AM
 

David Wynne said:

Hello from the C# world - abbreviations and prefixes/Hungarian notation where long ago deemed a code smell.  Intellisence does much to negate the need, but the bottom line is about readability, maintainability and lots of other stuff that ends in ability.

If you're basing architectural decisions on the "how quickly I can type a name" - you need to step back and ask yourself some questions.

March 9, 2010 4:57 AM
 

Ben E said:

David's hit the nail on the head there: naming conventions should be driven by clarity rather then brevity.

Code should aim to be self-documenting as much as possible, which isn't going to happen if object names are unnecessarily abbreviated.

March 9, 2010 6:14 AM
 

Eric Wisdahl said:

Abbreviations in schema, table and column names drive me crazy.  Then again, so do aliases where you aren't referencing the same table multiple times.  Spell it out so that there is no ambiguity for the next person that comes along.  I don't need to search through the code to find out which table T39 belongs to... And I don't need to wonder what the Act schema should be used for (Actuarial? Accounting?).

March 9, 2010 8:59 AM
 

WIDBA said:

We have both - some abbreviations work cause they are so well known, when their is any ambiguity, we move towards a short word that makes sense.  I don't see the need for automatedpaymentsystem.outstandingcollections when AP will do.  

but as you mentioned, right/wrong does not apply.  too short and its obscure, too long and its cumbersome.

March 9, 2010 1:36 PM
 

Home said:

Two reasons

1. Some database products has limit of chars in name

2. Once your product support multiple databases, you must use the most restricted one

Cheers

March 13, 2010 1:56 PM
 

Dave F said:

Much ado about nothing. Move on.

March 14, 2010 10:51 AM
 

Dave F said:

Much ado about nothing.

April 1, 2010 1:15 AM
 

Chris Anderson said:

Defintely a balance.  Generally I err on the side of spelling out reasonably descriptive names for table and column names, but do abbreviate schemas names.  I think it's one of those things thats so pervasive in a particular app domain, that to constantly see it spelled out is counter-productive.  Think the people who use 'tbl*' as a naming convention for tables.  After about the third query you write, you're thinking "Yes, I know it's a table...".  Given an hour or so orientation to a database, I would expect most db professionals to quickly acclimate a few abbreviated objects (e.g. that the 'rec' schema refers to 'reconciliation', or 'recreation', or 'recordsales', etc. whatever it means to that database)

August 27, 2010 5:22 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement