Add a column with a specific name.. | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Add a column with a specific name..

Why do I get an errormessage when I run: EXEC (‘ALTER TABLE master.dbo.DataBaseSizeFollowup ADD TEST-1_DATA [INT] NULL’) But when I run: EXEC (‘ALTER TABLE master.dbo.DataBaseSizeFollowup ADD [TEST-1_DATA] [INT] NULL’)
Everything works fine… Exept that the name of the column is [TEST-1_DATA] instead of TEST-1_DATA as I want… I’m using a MS SQL 2000 and doing my tests in SQL query Analyser
"data" is a future reserved word for SQL Server, and can’t be used as part of an object name. You will have to change the column name so that it does not include "data". —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com
No, I don’t think it has anything to do with ‘DATA’ word as such. I created a table with the following command: CREATE TABLE junk(data INT) and it created the table successfully. In the above case, even if I replace DATA with some other value, the first command will always fail. I think the problem is with "-" sign (hyphen, not underscore). If you have a hyphen in your object name you have to enclose them in []. If you don’t have them, everything should be just fine. Correct me, if I am wrong. HTH
-Rajeev Lahoty
hmm. It looks like rlahoty is right on this..<br />I can do it like this and it works:<br /><pre>EXEC (‘ALTER TABLE master.dbo.DataBaseSizeFollowup ADD "TEST-1_DATA" [INT] NULL’)</pre><br /><br />An interesting thing is if I do like this:<br /><pre>EXEC (‘ALTER TABLE master.dbo.DataBaseSizeFollowup ADD [TEST-1_DATA] [INT] NULL’)<br />EXEC (‘ALTER TABLE master.dbo.DataBaseSizeFollowup ADD [admin_DATA] [INT] NULL’)</pre><br /><br />and look in Enterprise MAnager in "Design Table"-mode. Then only the first column has the [] around the name. The second doesn’t…<br /><br />Oh well..I can still reference the column with the name TEST-1_DATA without the [] if I want so I think I let this one go by… [<img src=’/community/emoticons/emotion-2.gif’ alt=’:D‘ />]<br /><br />But it should be interesting to know why… [<img src=’/community/emoticons/emotion-2.gif’ alt=’:D‘ />][<img src=’/community/emoticons/emotion-2.gif’ alt=’:D‘ />]<br /><br />
The reason for the ‘Design Table’ behaviour is, hyphen (-) is a reserved symbol but underscore (_) is not. And I don’t think you will be able to reference the first column (TEST-1_DATA) without using delimiters.
You can’t. It’s just like having a space in the column name. This works: CREATE TABLE derrick([what in the world] INT) This does not: CREATE TABLE derrick(what in the world INT) I wouldn’t recommend ever naming a column such that it would have to use [] though. It can cause you all kinds of problems down the road as it introduces a "this has a .1% possibility of something" factor that you can eliminate just by doing a little better design. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
]]>