May 21, 2009 at 5:17 pm
I have been told that you shouldn’t use T-SQL keywords as table column names as a rule, but if you do use them, put brackets around them.
For example, do this:
CREATE TABLE Table1 ([Name] varchar(50), [Description] varchar(50))
instead of this…
CREATE TABLE Table1 (Name varchar(50), Description varchar(50))
My question is why is that the case?
It looks like SQL Server can determine when it is a column and when it is a keyword (even in SELECT statements).
May 21, 2009 at 5:32 pm
It fails in some places, with some keywords, but you are right. For the most part, SQL Server can figure it out.
However, it also messes up color coding, and confuses people. Someone might not realize that you have an object name there and think the query is broken. It's just a bad practice.
May 21, 2009 at 5:41 pm
[font="Verdana"]Several things here.
You are not designing or writing for SQL Server, you are writing and designing for humans. Keep that in mind.
What do I mean? Well, for SQL Server, you could do the following:
create table A(B int, C int);
SQL Server is fine with that, but any human trying to ready your code is going to throw a fit!
So in a design, when you use the word "name"... the name of what? When your column name appears in SQL code, what is it you are naming? I would have to continually refer to the context of the column name to actually get which name you are talking about. For example, if you are working with a combination of people and products, is it the product name or the name of the person?
Now some designers (er, myself included at times) rely on developers being smart and prefixing the column names meaningfully. For example:
select product.name, person.name from product inner join person on ...
However, I think that's a bit lazy, and is making assumptions about how the coder is going to develop. So these days I prefer the form:
select ProductName, PersonName from product inner join person on ...
I don't always prefix the name of a column with the name of the table, just where it makes sense so that I don't need to refer to context to get the meaning of the column. For example, it's pretty clear that a BirthDate probably isn't going to refer to a product, and a NumberInStock isn't going to refer to a person.
So the rule of thumb is: you are designing for the people working with your system, not for SQL Server. Oh sure, it has to be valid SQL Server constructs, but that's the bare minimum, not the design level you are aiming for.
[/font]
May 22, 2009 at 10:44 am
I agree with Bruce. With additional idea that:
select product.name, person.name from product inner join person on ...
Will send the columns name and name to the calling application so how does the calling application know which name is product name and which is person name. You may save a few keystrokes on creation, but then whenever you query the tables you add keystrokes by having to alias the columns like this:
select product.name as ProductName, person.name as PersonName from product inner join person on ...
Where the aliases should have been the column names in the first place.
Edit: fixed typo
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 23, 2009 at 12:08 am
I appreciate the explanations. I came across some SELECT statements written by a co-worker with keyword columns in at least five different places and I realized that while I knew you weren't "supposed" to do that, I didn't actually know why. But it sounds like you avoid keywords for the cases where SQL can't interpret the difference, readability, and ease of maintenance for future supporters of the code. Thanks!
May 23, 2009 at 2:48 am
Generally I agree with Steve, Bruce and Jack.
However a column "Name" seems to be no problem in my book. In SSMS2k8 it is no keyword anymore. Real problems are real keywords like From, To, Group, ...
May 27, 2009 at 1:55 am
I tend to try quite hard to avoid keywords and other reserved terms, partly because it just seems 'wrong' somehow, but mostly because I don't like my columns to light up blue or pink in SSMS 😀
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 27, 2009 at 5:29 am
For veterans is just a conditioned reflex, you know since the old-n-good COBOL times there is a list of “reserved words” you don’t want to mess with. 😀
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 27, 2009 at 4:04 pm
Heh. Yeah actually that might be it - though it was Commodore 64 BASIC in my case...not COBOL 😀
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply