SQL Server Performance Forum – Threads Archive
Basic ALTER TABLE question
I need to add a column to an existing table, make the column the Primary Key. BUT, I need to make sure this new column appears as the first field. QUESTION: Will the ALTER TABLE statement automatically insert the new field first? If not, do I have to include all of the other columns in the ALTER TABLE statement as well? Thanks.No, BTW why do you want to have so. Anyway you can display the data as you wanted using SELECT statement. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
I was asked to put the field first by a developer. Apparently, it has something to do with the way Access reads Primary Keys. Not sure though. I was just going to create an ALTER TABLE state with an ADD for the new column specifying it with a PK then adding ALTER COLUMN for the rest of the remaining columns and doing this in the proper field order. Thoughts would be appreciated. Thanks.
quote:Originally posted by satya
No, BTW why do you want to have so. Anyway you can display the data as you wanted using SELECT statement.
Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
I would suggest to copy the data to a new table, drop thsi table and create the schema with the required order. (shortest way) Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
What would happen if I just altered the columns in the Alter Table statement with data in the table. This is a production table that’s why I’m asking so many questions. Thanks.
quote:Originally posted by satya
I would suggest to copy the data to a new table, drop thsi table and create the schema with the required order. (shortest way)
Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
BOL refers
The changes specified in ALTER TABLE are implemented immediately. If the changes require modifications of the rows in the table, ALTER TABLE updates the rows. ALTER TABLE acquires a schema modify lock on the table to ensure no other connections reference even the meta data for the table during the change. The modifications made to the table are logged and fully recoverable. Changes that affect all the rows in very large tables, such as dropping a column or adding a NOT NULL column with a default, can take a long time to complete and generate many log records. These ALTER TABLE statements should be executed with the same care as any INSERT, UPDATE, or DELETE statement that affects a large number of rows.
.. further information obtain from BOL. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Access indeed has a very specific problem: the PK constraint of a SQL Server table must have a name that comes first in alphabetical order among the constraints and indexes on this table. IOW, choose a name like a_PK_tblWhatever for the PK, and idx_tblWhatever or cnstr_tblWhatever for the rest. Like I said: very specific problem with Access front-ends. AFAIK, it’s only when Access is accessing SQL Server tables over ODBC.
Adriaan – do you have any documentation / links on that? I’ve never heard of such a thing, but it seems like that’d be important if its true…
The simpliest way was to just use Enterprise Manager, right click the table, select Design, insert a new column at the top and set it as Indentity and Primary Key.<br /><br /><img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ /><br /><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by satya</i><br /><br />I would suggest to copy the data to a new table, drop thsi table and create the schema with the required order. (shortest way)<br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Contributing Editor & Forums Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided “AS IS†with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">
The Ordinal position of the column doesnt matter as long as you put it in correct place in the select statement. Also in front end application use Rs("ColumnName") istead of rs(0), rs(1), etc Madhivanan Failing to plan is Planning to fail
quote:Adriaan – do you have any documentation / links on that? I’ve never heard of such a thing, but it seems like that’d be important if its true…
To be honest, it is just common knowledge in my organization – not sure about the original source for the information. We must have had issues with it, and we do a lot of Access/SQL work. Sometimes we get Microsoft involved, and this might be one of those cases.
The problem appears to be that Jet assumes the first index it finds on an ODBC table will be the PK, and somehow the indexes are being presented in alphabetical order.
]]>