Worst Practice - Spaces in Object Names
-
Gary, thanks for the kind words. Any chance you could genericify that best practices document and submit as an article (or two)?
Andy
-
Andy,
At this point I just don't have time to get the legal stuff I would need to have done to do that. Plus I believe there is already one on the Technet CD. I'll have to check on that. I'm just a developer who also has to do DBA duties. I actually have nothing to do with the SQL Server team.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
I think the problem with Access is that it is an end-user database and as such has to be as simple and as forgiving as possible.
This is fine, but how many programmers start off with Access then migrate their skills to SQL Server?
Chances are that you pick up skills on the job and therefore the poor old Access developer learns about the pitfalls as he plummets into the hole grabbing handfuls of air as he falls.
I have a bee in my bonnet over the lack of training that companies put their employees through and the "make-do-and-mend" culture when it comes to tools.
After 5 days each on the original MS SQL 6.5 courses my productivity went through the roof and this obviously had massive benefits for my then employer.
I know that for small employers training costs are an issue but
- They gain on employee productivity.
- Their systems will be better built and thus more easily maintained.
- If they can't afford training then they can contribute to it and/or give employees paid leave specifically for training.
-
I figured it was of your own making, that's why it was interesting. If there is one on Technet, a link would be handy. We're hoping to have the topic covered in more detail soon, as one or more articles.
Andy
-
Wow! Hot topic... I don't know what the big deal is though. I like spaces in table names, column names, wherever...JUST KIDDING!!!
I think a "best practices" option should be added to YUKON so default naming conventions can be enforced. For example, pattern match object names.
No! Please don't ask for more Yukon features!! We have waited long enough already!!! 
I blame the practice on Access database users. Access allows this practice and then if the Access database has to be moved to SQL Server, those tasked with the move get bitten.
That would explain that horrible SQL Server sample database NorthWind... AARGH!!
Aaaah... fun with formatting and smileys. Can you tell it's Friday?!
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog -
What about spaces in the database name itself. I had a consultant come in (for another part of the IT dept), and pop a DB onto my server the was "Company Citrix Farm".
Blew up my stats SP_ that I had built. His reply was "Microsoft says it's Ok." I reamed him a new one. But it was a week before I had found this out. Took a year and a new consultant to get it corrected.
I encourage my fellow programmers to use the underscores in the table names and columns, and then CamelBack the variables in the programs. That way you end up easily distinguishing variables and table refs and you aren't likely to get wierd crossovers when working muptiple recordsets and programs.
Just my $0.02.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
I am glad you mentioned the spaces in the database name Jim! I had a SQL Server 7.0 server that I inherited and somehow a space got in the name of the database at the end! This gave the sqlmaint utility fits because if you create a folder with a space at the end, Windows takes it out. So when the sqlmaint utility tried to back up to the folder with the database name, it couldn't find it and the backup failed.
After this thread, we should probably just take spaces out of the English language altogether...
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog -
I love this thread! It is so refreshing to see persons so set on following a Best Practices mentality with SQL instead of shortcutting it. I see so much of this type of stuff at work on a daily basis.
Does anyone know of a Best Practices document for working with SQL? I have convinced the development group at my company to at least consider putting together some type of Naming Convention and Standards document so that everyone is on the same wave length.
I personally am a stickler for proper and neat coding. I see SQL and coding a a while as sort of Art form. If I had a good doc to start from it sure would make my life a lot easier. I'd like to add that if I can get something that either has Microsofts Name or approval on it that would be even better.Thanks!
Ed
Kindest Regards,
Just say No to Facebook! -
These might help. The first is related to security and should be of interest to both admins and developers. The second is more developer oriented as it relates to patterns and practices.
http://www.microsoft.com/sql/techinfo/administration/2000/security/default.asp
http://www.microsoft.com/resources/practices/database/default.mspx
Enjoy!
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog -
I too detest SPACES in ANY names, Directory, Files, Tables, Fields, etc. I have noticed that when using MS Access that IF there's a space in a field name, Access will promptly put that entry in "double quotes" because it takes the entry as a Text Value rather than a FieldName. THEN you have to REMOVE the " and replace them with []. What a pain! Underscores are better than spaces, but I still opt for just he UpperCase / LowerCase format.
Butch -
This sounds more like a Windows/Microsoft hate group. It's a small thing really. If SQL Developer and other tools were redesigned to handle spaces in a name, it wouldn't be an issue. If I wrote a letter and put an underscore under each space, it would look pretty weird. Spaces are normal characters, they have an ascii value. Why all the fuss? Just fix it.
Signed,
Yours_Truly_Today_But_It_Is_Awkward_To_Add_So_Many_Spaces_When_I_Type
Jamie
-
I don't see how this is about hate for Windows or Microsoft. There is some legitimate criticism of practices which defaulted to or seemed to encourage practices (spaces in names) that make life harder for everyone that has to interact with such names. But most people here are using Microsoft SQL Server, and many of us really like, and believe it is one of the best RDBMS ecosystems and has some of the best user communities.
This isn't just a tool thing, it's a programming language thing, fundamental to how parsers/compilers/interpreters can recognize & distinguish all the language elements. Most programming languages of which I'm aware treat white space as a delimiter between words (object/variable/parameter/method/function/procedure names, etc.). All client tools (e.g., SSMS) can handle spaces in names if the right syntax is used. Most RDBMS allow use of double quotes to wrap object names in order to handle non-standard naming (in some cases, this means even use of mixed case). SQL Server happens to allow both double quotes or square brackets. And it's very easy to alias columns names in selects if the tools need that (though many would argue that even that should be done in the client application, since it's a presentation issue).
There's nothing to fix except habits that make it harder to write code/queries. Imagine having to use a character other than space to separate words, given the design of keyboards and lifetime habit (user programming).
-
I'm in agreement with you about SQL Server. I am a fan from the very first time I used SQL Server 6.5, 7 , 2000 and so on. Yes, SQL Server handles spaces without difficulty... easily in fact. But if you sit with an Oracle and Linux person, they'll spew hate for code page 1252, spaces in names, Excel, Access and anything else they can find to poke a hole in Microsoft. My environment tends to interact with more of that crowd, so I get the "hate" frequently.
My thoughts are that SQL Server has no issue with spaces. If I import a CSV that has spaces in the column name to SQL Developer - well, frankly, why doesn't Oracle just fix that problem. I stumbled here for that reason and while I agree about how great a job Microsoft has done from the beginning with SQL Server, I don't think you'll find agreement about how Microsoft should handle white space. They do handle white space, in fact, they do so with flying colors. What's the problem with white space in a column name? I don't get it. I am spoiled from using Microsoft tools. But - sorry - sure feels like hate to me.
Jamie
-
Just my 2 cents...
- I hate anything that has to do with making me type something extra. And, no... I also don't use Underscores as a replacement character for spaces, either. I also don't use all lower case, all upper case, or camel casing. I typically use proper/title casing. Just my personal preference.
- I don't care for the look of code that's riddled with brackets, braces, or a shedload of parentheses. It makes it more difficult to read for me. Others don't seem to be bothered by it. There are times when brackets are required for "safety" to prevent running into spaces and other special characters, but not in regular code.
- To me (all of this is just my personal opinion/like/dislike)), putting spaces in column names should be reserved for the presentation layer. Yes, you can end up necessarily have to do such a thing in T-SQL when there is no other presentation layer but only in the final output... not as names of tables or columns. And I want to break out the ol' pork chop cannon every time I run across a server name with a dash in it.
- I have zero tolerance for quoted-identifiers. Save the quotes for string literals.
- I also have some rigidity when it comes to using "=" instead of "AS" when assigning expressions results to column names, making them all vertically aligned not only for ease in finding column names in complex code but to also make them easy to vertically copy for use in additional code that may use the same columns, and a whole lot more like what should be upper cased, lower cased, and title/mixed case.
- It took me a while to realize the advantages but I also use leading commas and logical/mathematical operators.
Again, all just my personal preferences and observations. And I'd much rather have parentheses vertically aligned on properly indented code. In other words, I believe in writing code according to the word represented by the "S" in SQL. 😀 I even have a structure that I follow when adding comments that explains the basic business reason for every lick of code as well as useful/informative flower boxes with usage examples and revision history.
--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)
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply