Best Practice/Standard Alias Schema/Table Convention

Question

Just looking for suggestions for best practices and/or standards for table aliases in a Query. I know this is very subjective...but just looking for some general guidelines from experts and their experience and what works and what might not work. Should we use all periods? Ex. [EDW.Member].[FirstName] Or underscores? Ex. [EDW_Member].[FirstName] Where [EDW] is our Schema Name and [Member] is our Table. Is there a preference to use "_" underscore as opposed to "." period or prefer some other designation to separate and segregate the two? Schema and Table Name And we do prefer to include the Schema Name as we also have a [REF] Schema in our [EDW] Database. Thanks for your review and am hoping for some solid replies with solid experience.

Answer 1

I'm not going to be able to provide you suggestions or best practices for naming so I'll let the others speak. However I would never recommend using a dot in a name. The reason is that if you do that then you'll always have to wrap the name in square brackets otherwise it could cause problems. SQL uses a dot to separate server, schema, table, column names. If you have a dot in the middle of one of those then it is going to get confused. For example EDW.member, is that the Member table of EDW schema, the Member column of the EDW table, or what? It gets even more confusing when you need the full name <server>.<schema>.EDW.Member.<column>. To prevent confusion you'll always have to wrap it in brackets (e.g. <server>.<schema>.[EDW.Member].<column>) which I personally don't like.

If you had to choose I would say use an underscore. But honestly I wouldn't even do that. I don't feel that EDW_Member is any more readable then simply EDWMember or EdwMember. While SQL doesn't care about the casing, it does make it easier for the rest of us. If you really, really need have clarification then you could also use schemas. So EDW.Member would be the Member table in the EDW schema. This is useful for databases with large, logically separated tables. Refer to WorldWideImporter database as an example.

Answer 2

I don't there are any general answers here. It depends quite a bit on local custom.

In a system I worked with for many years, we had three-letter abbreviations for all tables that were used in stored procedure names etc. These official abbreviations lend themselves well to be used as aliases. But obviously, that is not going to work some place where you don't have these established abbreviations.

Where I am now, I tend to use "natural" abbreviations as long as possible. For the Project table, I use P. For ProjectOrganization I use PO etc. But obviously sometimes this breaks down.

As for [EDW.Member] vs [EDW_Member], both are too long for my taste. I want the aliases to be short, to reduce the noise level. And aliases that needs quoting with brackets? No way!

But it is all a matter of personal taste.

Answer 3

If the alias is not shortcut, why use it? Just use the actual table name. As above I use simple abbreviations of a couple chars. When multiple joins to the same table just append a digit.