0

I am dropping a column 'aColumn' from very large SQL Server table and then re-creating that column with a constraint in the same table to populate this column with a default value. SQL server (2014) created that column at the same ordinal position in the table i.e between the same two columns it was there before it was dropped. I am using the following code. Can I supply this code to client who have to apply this code in production.

How can I be sure that this column will be created at the same ordinal position in production, else there will be a big problem in production.

ALTER TABLE aTable  DROP COLUMN aColumn;
GO

ALTER TABLE aTable ADD aColumn [smallint]  NOT NULL CONSTRAINT DF_aTable_aColumn  DEFAULT (0)
GO

ALTER TABLE aTable DROP CONSTRAINT DF_aTable_aColumn
GO

ALTER TABLE aTable ALTER COLUMN aColumn [small int] NULL
GO
Dale K
  • 25,246
  • 15
  • 42
  • 71
Viv Pat
  • 1
  • 3
    why not simply alter rather than drop/add the column? Secondly, do not depend upon ordinal positions of columns in a relational table. Think in terms of sets and ALWAYS specify the columns as you need them and never use `*` like `SELECT * FROM BIGTABLETHING` – Mark Schultheiss Jun 02 '20 at 11:03
  • 1
    Does this answer your question? [Add a new table column to specific ordinal position in Microsoft SQL Server](https://stackoverflow.com/questions/769828/add-a-new-table-column-to-specific-ordinal-position-in-microsoft-sql-server) – Mark Schultheiss Jun 02 '20 at 11:05
  • this appears to be a syntax challenge? `aColumn [small int] NULL` – Mark Schultheiss Jun 02 '20 at 11:06
  • *"else there will be a big problem in production"* Sounds like the problem is production; you should never rely on the ordinal position of columns in a table. Much like you should never rely on an `IDENTITY` having no gaps. – Thom A Jun 02 '20 at 11:42
  • If you want to be sure, you test it. You develop a test plan to verify that the system works correctly once the change is complete. There is no other way. – SMor Jun 02 '20 at 11:43

1 Answers1

0

I could not get solution for this, could not have dropped the table and recreated it as the script had to go to the client for implementation, decided to update the column for default 0 value using batch statements creating a helping index, dropped the index later on after the update statement, update statement took 30 mins for 34 million records after the index was created and used by SQL server (could check from the execution plan).

Viv Pat
  • 1