Any Cons to long column names.

Question

Any Cons to long column names.

repent_kog_is_near

SSCertifiable

Points: 7278
More actions
October 25, 2009 at 6:45 pm

#96157

SQL Server allows upto 128 characters for Column names. Perhaps no one will use such a long name.
But is there any disadvatanges in terms for programmers ,or in any other situations where a columns with 50 or 60 or more character length can cause any issues or problems.
thanks
Dan

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Yes, typing them in. Many developers (including me) find it both onerous and error prone to have to type in such long names.

You will also find that many DB tools do not handle them very well or have little idiosyncrasies that have no good solution, like making the displayed columns really wide, but making the columns smaller means that you cannot see the full names of the columns, especially bad if multiple column names have the same initial characters (annoyingly common with Very Long Names).

There theres what happens to you in many queries: lets say you have a 50 character table names and column names just as long, now try writing even a moderately complex query with a self-join and some reasonable column calculations and CASE functions. What a visual mess, the only thing worse than having to write that is having to read it later on, Ugh.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Steve Jones - SSC Editor SSC Guru Points: 736632 More actions · Answer 2

I'm with Barry. It's annoying, and a hassle.

However, with 2008, and some modern tools, intellisense makes this less onerous. no other disadvantages I'm aware of. I believe the name is only stored in the meta data, not on each row or page of the table, so I can't imagine it impacting I/O.

I guess it makes your proc code larger, but other than that, I wouldn't think it matters.

Jeff Moden SSC Guru Points: 1004435 More actions · Answer 3

I can click and drag a column name both in QA and SSMS so I don't really care how long they are.

What I do care about is a column name that describes the data in the column... period. Column names should not be written as a sentence and should not reference the table or database they're in except for (possibly) the PK of a table if it's a surrogate PK such as CustomerID on the Customer table or EmployeeID on the Employee table. They should not have Hungarian notation and they should usually not be pluralized. "ZipCodes" is not the correct name for the "ZipCode" column. They should only describe the content of the column they name. Usually, something less than 30 characters will suffice but that should never be a hard limit IMHO opinion... heh... well except in Oracle. 😉

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150468 More actions · Answer 4

I've come across what I believe to be both ends of the spectrum on this one.

First up, an old application built to be compatible with any back-end database (restrain yourself Jeff!) which defined every table, column, and object at a fixed-length of 8 ANSI characters. Depending on the object, each character in the sequence told you something about something: for example the first three characters of every column name identified the table it belonged to. Once you have spent some time in a database with column names like IVTRDUPT and PSGBNLL2, you quickly come to dislike the original architect quite intensely - trust me on that.

Secondly, and very recently, a came across a design that had column names in excess of 50 characters in just about every case. The designer had attempted to cover just about every aspect of the column's function in its name. Not only was it quite amazingly tedious to work with, it was also extremely brittle: the information in the column names is likely to be out of date within about 15 minutes of going live. Did I mention that the column names often had spaces in them? Brillant.

At the risk of stating the bleeding obvious: column names should be as succinct as possible while still giving a good idea of what it is for. Oh, and undersores or PascalCase...never spaces, and never mix the two 😎

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

dmoldovan SSCertifiable Points: 6334 More actions · Answer 5

I agree with Paul. My husband is currently working with ERP LN (former BAAN) and the column names are "horror"...:-)

Andrew Gothard-467944 Hall of Fame Points: 3928 More actions · Answer 6

50 Characters? That's not a column name, that's an essay!

Silverfox SSC-Insane Points: 23150 More actions · Answer 7

You should try working with sage then, the tables and colums are just a jumble of the alphabet, the main reason sage consultants get paid so much 😀

Hungarian notation has its place, Jeff, but it was more widely used for variable naming rather than table or column definition. I used Hungarian notation for over 12 years when I was working as a developer, I will point out I never used it with SQL Server 🙂

All the other points mentioned are valid, not everyone has access to gui or tools so restricting the columns length did make sense. the 8 length table name refers back to the dos days, when you had the restriction on file name size.

--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]

Paul White SSC Guru Points: 150468 More actions · Answer 8

Andrew Gothard-467944 (10/26/2009)
50 Characters? That's not a column name, that's an essay!

Apparently, in the next release, all data will be stored compressed in the column names - there won't be any data in the tables at all. I'm told that the implementation will be astonishingly fast since it only ever changes metadata.

LOL

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004435 More actions · Answer 9

Silverfox (10/26/2009)
Hungarian notation has its place, Jeff, but it was more widely used for variable naming rather than table or column definition. I used Hungarian notation for over 12 years when I was working as a developer, I will point out I never used it with SQL Server 🙂

Heh... to be sure, I was talking only about SQL. 😉 But, now that you mention it, I hated it in other places, as well. Maybe things have changed a bit (I haven't had to work with serious GUI code for about 12 years) but it used to tick me off to have to find HN everywhere in the code because some flippin' designer decided to change the datatype. It was a real chore.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Andrew Gothard-467944 Hall of Fame Points: 3928 More actions · Answer 10

dmoldovan (10/26/2009)
I agree with Paul. My husband is currently working with ERP LN (former BAAN) and the column names are "horror"...:-)

Ah, good old ttdssls045123.t_dqua etc, etc.

How I don't miss the BaaN data structure

dmoldovan SSCertifiable Points: 6334 More actions · Answer 11

Ah, good old ttdssls045123.t_dqua etc, etc.
How I don't miss the BaaN data structure

...however, from your nickname I can see that you don't forget the style easily(?)...:-)

Garadin One Orange Chip Points: 29613 More actions · Answer 12

Silverfox (10/26/2009)
You should try working with sage then, the tables and colums are just a jumble of the alphabet, the main reason sage consultants get paid so much 😀

I have to deal with that here a little bit. Worse yet, we're still on the VFP version. Single row lookups taking over 10 minutes, huzzah. At least they have a data dictionary for the tables, or I'd never find anything.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Silverfox SSC-Insane Points: 23150 More actions · Answer 13

Jeff Moden (10/26/2009)
Silverfox (10/26/2009)
Hungarian notation has its place, Jeff, but it was more widely used for variable naming rather than table or column definition. I used Hungarian notation for over 12 years when I was working as a developer, I will point out I never used it with SQL Server 🙂
Heh... to be sure, I was talking only about SQL. 😉 But, now that you mention it, I hated it in other places, as well. Maybe things have changed a bit (I haven't had to work with serious GUI code for about 12 years) but it used to tick me off to have to find HN everywhere in the code because some flippin' designer decided to change the datatype. It was a real chore.

You had it easy then :-), I was a Foxpro specialist, foxpro is loosely typed so the variable type can change so. you could say

lcSurname = "Smith"

then say

lcSurname = 12

and that is perfectly fine 😉 and doesnt causes any issues. so you could imagine seeing a variable declared as a string then seeing it assigned and treated as a numeric two lines later.

--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]

Jeff Moden SSC Guru Points: 1004435 More actions · Answer 14

Heh... sounds like VBS...

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)