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


Naming Conventions - Table and Column Names


Naming Conventions - Table and Column Names

Author
Message
BethF
BethF
SSC Veteran
SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)

Group: General Forum Members
Points: 228 Visits: 75
I'm pretty old school and I've always created tables and columns following a specified naming conventions that doesn't include spaces or underscores and uses camel case. As I set out to design a new data mart that will be used primarily by non-tech people using Power Pivot, Power BI, and other assorted BI tools, I'm wondering if it is time to change the approach. In the past, I've made SSAS my "presentation layer" with business names but with the abundance of all of these new-fangled BI and reporting tools and pretty smart users, I'm wondering if I need to bring it down to the mart/star schema level. Most of the columns would then end up with a name that includes a space. I know it might be more difficult for us dinosaurs who still write T-SQL but we don't code for us, we code for our stakeholders.

I'm interested to hear others' opinion about this. Particularly if there are any performance implications to using column names with spaces.

Thanks in advance for your feedback!



below86
below86
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4010 Visits: 3780
IMHO, never ever use spaces. Use underscores to make it more readable to the users. I prefer underscores to camel, but I usually follow what the 'standard' is for the company I'm working for. Most users I've dealt with never complained about the name have an underscore versus spaces.

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
xsevensinzx
xsevensinzx
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11516 Visits: 4357
I personally use camel case on everything. I really dislike underscores and trying to break the habit of hyphens mostly because all the veteran SQL Server guys hate having to use square brackets to query data (even though I secretly still use square brackets to square up my code anyways).

UserID, ClientID, FactSales, DimProduct, DMClient, I love it all and see no issue using whatever you like on the backend. When it comes to the presentation layer, I normally name it to whatever the end user is looking for. That means, ProductName is going to be converted to Product Name. Metrics may go from TotalRevenue to Total Revenue By Day or whatever they like. It matters to me not because that's the front-end and I'm responsible mostly for the back-end.

In my experience, there is always big debates on what approach you take. The one thing that will always remain true to all, remaining consistent is the most important part to your decision. Chaotic naming conventions weaken the strongest man (or woman).

P.S

Consistently using square brackets didn't save me. This is likely one of the exceptions to the rule. But, I like to watch the world burn. Whistling
Arsh
Arsh
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2343 Visits: 874
Spaces should never ever be used ... Though they don't make a difference technically , underscores make it more readable. Assigning one fixed length module-name as a prefix (Fin_<tablename/viewname>Wink makes a good practice, the benefits of which can't be anticipated sometimes. Its a lot of relief when one has a need to develop an application for audits /security / licensing purposes especially where you can easily identify objects which follow one naming convention.

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (540K reputation)SSC Guru (540K reputation)SSC Guru (540K reputation)SSC Guru (540K reputation)SSC Guru (540K reputation)SSC Guru (540K reputation)SSC Guru (540K reputation)SSC Guru (540K reputation)

Group: General Forum Members
Points: 540257 Visits: 44587
I agree with the idea of avoiding both spaces and underscores for most things. Avoiding spaces should be a "commandment". As with spaces, I don't ever use underscores to merely to separate words. I do, sometimes, use a logical grouping "prefix" separated by an underscore from the rest of the column name.

The bottom line for me is to never format column names based on what people want to see on a report or a screen. That's why there are such things as column aliases.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (238K reputation)SSC Guru (238K reputation)SSC Guru (238K reputation)SSC Guru (238K reputation)SSC Guru (238K reputation)SSC Guru (238K reputation)SSC Guru (238K reputation)SSC Guru (238K reputation)

Group: General Forum Members
Points: 238609 Visits: 40646
As for the square brackets, I have gotten in the habit of using them more. And, since I write a lot of dynamic SQL, I have made it even more important to use them in the dynamic code. Some of it is due to column names used in quite a few tables. Some weren't reserved words back in SQL Server 2005 but are now. But more importantly are the bizarre names that I have found for some indexes that were created elsewhere and some of code that worked perfectly fine suddenly failed. Imagine an index name in production that looks something like this: <index_name, sysname, ind_test>.


Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Tom Thomson
Tom Thomson
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53256 Visits: 13163
I try to avoid have anything that needs quoting to be a valid name, and except in extremely rare circumstances (eg something used as a name suddenly becomes a reserved word) that comletely rules out spaces within table and column names (as well as eliminating any need for square brackets or similar punctuation). I've sometimes given table names prefixes that indicate the general area they are relevant to and seperate that from the rest of the name by an underscore - so for example in an an in-room entertainment and other services system for hotels there could be prefixes like Movie_, Music_, OffficeTools_, Restaurant_,RoomService_, Internet_, Directory_, Telephone_, Messaging_, Email_ and so on. As you can see, I will use CamelCase to indicate word boundaries within a prefix, and also for boundaries within the rest of a name. I also sometimes use the same sort of prefixing for column names, and always have a rule that if the same column occurs in two or more tables (by the same column I mean a column representing the same real-world attribue) all those tables must use the same name for it; this forces the use of table aliases in DML statements involving more than one table unless the writer decides to use full table names to qualify column names (which can take up a lot of space on the page or screen in non-trivial DML statements). It also tends to avoid crazy errors resulting from joining on mismatched columns.

Tom

Ed Wagner
Ed Wagner
SSC Guru
SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)

Group: General Forum Members
Points: 167915 Visits: 11732
I avoid both spaces and underscores. Back when I did Oracle, underscores were the norm and everything was converted to upper case anyway. Now, I don't have to use them and don't. I really dislike spaces. When I need to write for the end user, I'll use aliases.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Ed Wagner
Ed Wagner
SSC Guru
SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)

Group: General Forum Members
Points: 167915 Visits: 11732
Lynn Pettis - Wednesday, September 27, 2017 10:26 AM
Imagine an index name in production that looks something like this: <index_name, sysname, ind_test>.

It looks like someone used a template to create an index and didn't bother replacing the name. Sigh.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Steve Jones
Steve Jones
SSC Guru
SSC Guru (349K reputation)SSC Guru (349K reputation)SSC Guru (349K reputation)SSC Guru (349K reputation)SSC Guru (349K reputation)SSC Guru (349K reputation)SSC Guru (349K reputation)SSC Guru (349K reputation)

Group: Administrators
Points: 349800 Visits: 20195
I would never allow spaces. It creates far too much confusion and potential issues with "ProductID" and "ProductID ". Even "Product ID" can be misread.
Underscores I dislike, but if that's what's there, I wouldn't refactor.

I did see a design presentation that I liked. This made every column name unique, so that we always knew were data was. This was something like:

create table product
( Productkey int
, Productname varchar
, productdescription
)
create table order
(orderkey int
, OrderProductKey int references Product(productkey)
, qty int
)


I'm torn on this. Knowing every item is interseting. Not sure of the value, and if you didn't have DRI this could be a mess, but it was interesting food for thought.

I am a bit fan of describing columns more completely with intellisense and 128 char identifiers. Make it easy to understand the column.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search