| Author |
Topic |
|
TallOne
Starting Member
49 Posts |
 Posted - 2011-07-14 : 11:36:09
|
| Hi Everyone!I've always been in the habit of placing reserved keywords in brakets when they are used as an object. For a field named Description, I would normally place in brackets [Description]. But my question is why? I've worked at shops that do not do this and I'm not aware of any problems that ever arose from it. Now I'm faced with enforcing this policy at a new shop and I would like to provide the team a good explanation of why. Can anyone clear this up for me?Thanks,Jerry |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2011-07-14 : 11:41:36
|
| It is because most of them will throw errorselect 1 as addthe above throws error whereas the following is notselect 1 as [add]MadhivananFailing to plan is Planning to fail |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-07-14 : 11:59:49
|
Well, tell me if you think this kind of table is sensible:CREATE TABLE [FROM]([SELECT] CHAR(1), [WHERE] VARCHAR(2), [=] VARCHAR(2))INSERT [FROM] VALUES('A','B','C')INSERT [FROM] VALUES('A','B','B')INSERT [FROM] VALUES('A','=','B')That's the kind of headache I don't need as a DBA, and it's harder to argue against if I let people get away with things like GROUP, USER, LOG, TABLE, etc. You also encounter problems depending on your QUOTED_IDENTIFIER setting:SET QUOTED_IDENTIFIER ONSELECT * FROM [FROM] WHERE [WHERE] = [=]SELECT * FROM [FROM] WHERE [WHERE] = '='SELECT * FROM [FROM] WHERE [WHERE] = "="SET QUOTED_IDENTIFIER OFFSELECT * FROM [FROM] WHERE [WHERE] = [=] --same as aboveSELECT * FROM [FROM] WHERE [WHERE] = '=' --same as aboveSELECT * FROM [FROM] WHERE [WHERE] = "=" --different rowSET QUOTED_IDENTIFIER ONDROP TABLE [FROM] If you're positive you can enforce the square brackets then you shouldn't have any problem. But if your developers are not familiar with QUOTED_IDENTIFIER and they still enclose strings in double quotes, you can get the wrong data just because of a system setting.Now, if you're a DBA, and you want to use this technique AGAINST your developers: http://www.sqlsaturday.com/viewsession.aspx?sat=77&sessionid=4262That's perfectly fine.  |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-07-14 : 13:24:19
|
quote:
Originally posted by TallOne
Hi Everyone!I've always been in the habit of placing reserved keywords in brakets when they are used as an object. For a field named Description, I would normally place in brackets [Description]. But my question is why? I've worked at shops that do not do this and I'm not aware of any problems that ever arose from it. Now I'm faced with enforcing this policy at a new shop and I would like to provide the team a good explanation of why. Can anyone clear this up for me?
Since you are asking a general question about reserved words,I don't want to be captain obvious. But, Description is not a reserved word in MS SQL Server. And the, perhaps not so obvious, answer is to not allow reserved words to be used as an object name. That's how we handle that issue. As for the square-brackets, I personally hate them, but if you use Visual Studio you seem to pretty much be stuck with them. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-07-14 : 13:37:55
|
quote:
Originally posted by tkizer
Rob, that is a great idea for a presentation! I wish I could've sat in your session.Tara Kizer
I'm presenting it at SQL Saturday #81 in Birmingham, Alabama and #89 in Atlanta:http://www.sqlsaturday.com/81/eventhome.aspxhttp://www.sqlsaturday.com/89/eventhome.aspxI also submitted it for #85 in Orlando:http://www.sqlsaturday.com/85/schedule.aspx |
|
|
|
TallOne
Starting Member
49 Posts |
Posted - 2011-07-15 : 12:34:49
|
| Thanks for the replies everyone!@Lamprey - You are correct, I should have used a better word example...or maybe I should have included the phrase ReservedKeywords/Propeties Out of habit, I always include words like that in brackets as well. The word "Description" appears in blue in query analyzer just like a reserved word and I'm assuming it must be a property of some library. Do you guys do the same thing and are there any reprecussions if not?@robvolk Nice bag of tricks! :) I was lucky to catch the Charotte SQL Saturday a few months back and appreciate your time and effort.Thanks,Jerry |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-07-15 : 12:56:32
|
| As I mentioned, I just use the Reserved Keyword list (http://msdn.microsoft.com/en-us/library/ms189822.aspx) and if it appears in that list it cannot be used for an object name. That way there is no need to ever use delimited identifiers. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-07-15 : 14:04:48
|
| When I was playing with Policy Based Management I created a rule to prevent reserved-word object and column names. I can't remember the details but I don't think it could prevent, only log, which was kinda useless to my mind. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
|
|
|
Why shouldn't you use Reserved Keywords
Tara Kizer