March 18, 2005 at 9:15 am
In design table under sql2000, I'm creating several bit columns that correspond to check boxes. The check boxes indicate data such as: starts at dawn, starts at dusk, 24 hours, till close. To fit in with the previous colums, I was going to use one word column names, DAWN, DUSK, CLOSE, 24HOURS. When I entered CLOSE and 24HOURS, tabbing off the column name caused brackets to be inserted around the column name, just like timestamp appears as [timestamp], I ended up with [24hours] and [close].
Can anyone tell me why that happened? or what will happen when I try to insert or update a row using these column names?
Thx
March 18, 2005 at 9:29 am
If you use these columns you will have to enclose them either between [] or " ".
See the rules for identifiers in BOL. The first character must be a letter or an _.
Close is a reserved word. See reserved words in BOL
In your case I would use something like closed (or closetime or whatever fits) and h24.
March 18, 2005 at 9:42 am
I'm wondering if you could use a different desgin in this case. Would it be possible to move that info in another table like OpenHoursType or something like that and use a foreign key for DRI?
It would allow you to use a combobox instead of a series of checkboxes... especially usefull is someone comes in with a new value like noon in the months after release.
March 18, 2005 at 9:52 am
I'm not thrilled with the design either, for that reason, or when they have a starts at dawn ends at dusk event. I was mostly curious as to why the brackets appeared and couldn't find anything that would indicate 24hours or close was special column type and thus the brackets. If they were illegal names I would expect them to be rejected.
March 18, 2005 at 9:58 am
They are accepted to be user friendly, but mostly because they have to be compatible with other Database systems that allow this.
Wouldn't it be even simpler to have a column EventStartDate and EventEndDate (which would also include the time). It would be much simpler to maintain. Also dates make much better indexes than bit columns which gives very poor selectivity and no range capabilities. I think you need to push a little more on this.. You're the one who's gonna have to live with this design.. not them.
I'm forced to work with some things like this and sometimes you just can't recover (at least elegantly or quickly) from those errors.
March 21, 2005 at 1:21 am
If you had used the create table statement the names would be rejected. The enterprise manager "helps" you to do it anyway. userfriendly, indeed!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply