2

I'm trying to add a column to the end of a table in SQL like so:

  1. Open Design view
  2. Add column
  3. Generate and copy script
  4. Run, then save script

For some reason, when my colleagues do this, it generates the usual ALTER TABLE script.

When I attempt this, it generates a script that Creates a temporary table, then drops the existing table, then replaces the original table with the temp table, like so:

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.Table_Name
    DROP CONSTRAINT DF_Table_Name_NewColumnName
GO
CREATE TABLE dbo.Tmp_Table_Name
    (
    Id int NOT NULL IDENTITY (1, 1),
    Column1 varchar(16) NOT NULL,
    NewColumnName int NOT NULL,
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_Table_Name SET (LOCK_ESCALATION = TABLE)
GO
ALTER TABLE dbo.Tmp_Table_Name ADD CONSTRAINT
    DF_Table_Name_NewColumnName DEFAULT ((1)) FOR NewColumnName
GO
SET IDENTITY_INSERT dbo.Tmp_Table_Name ON
GO
IF EXISTS(SELECT * FROM dbo.Table_Name)
     EXEC('INSERT INTO dbo.Tmp_Table_Name (Id, Column1, NewColumnName)
        SELECT Id, Column1, NewColumnName FROM dbo.Table_Name WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_Table_Name OFF
GO
DROP TABLE dbo.Table_Name
GO
EXECUTE sp_rename N'dbo.Tmp_Table_Name', N'Table_Name', 'OBJECT' 
GO
ALTER TABLE dbo.Table_Name ADD CONSTRAINT
    PK_Table_Name PRIMARY KEY CLUSTERED 
    (
    Id
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
COMMIT

I can understand why it may do this if I'm dropping columns, or adding columns in the middle of the table, but I'm not, I'm simply adding a column to the end of the table.

This problem has actually caused SQL Server to delete all data in the table, if there is anything wrong with the design (I originally caught this by forgetting to add a default value to a required column, it wiped all data in the table.. Thankfully only in a development environment).

Jessica
  • 1,621
  • 2
  • 18
  • 34
  • Check this https://stackoverflow.com/a/9870989/9695286 hopefully it will help. – Karan Dec 12 '18 at 11:16
  • I'm permitted to make changes, it's not that it's preventing me from doing so, it's that it is making changes in a weird way. Also, towards the end of that answer, it suggests turning that option on to prevent data loss, is that just a precaution so you don't accidentally drop a table? Or is it an actual constraint to stop data loss, where you can still make changes? – Jessica Dec 12 '18 at 11:18
  • 1
    Please check Tools->Options..->Designers->Prevent saving changes that require table re-creation should be `unchecked`. – Karan Dec 12 '18 at 11:23
  • It is unchecked, and was when I ran that script that deleted data. – Jessica Dec 12 '18 at 11:25
  • 1
    My usual recommendation is to stop using the designers. Sooner or later they'll irritate you enough (by doing things like this). The only question is how long you'll put up with them. – Damien_The_Unbeliever Dec 12 '18 at 11:35
  • What would you recommend for somebody with mostly limited SQL skills? – Jessica Dec 12 '18 at 11:40
  • 2
    Practice, practice, practice. So you're making a change to a table, that's going to be `ALTER TABLE`. Open a query window and a browser window and navigate to the SQL Server documentation for [`ALTER TABLE`](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-2017). The documentation can be *daunting* but it is *thorough*. And with fits and starts you'll write `ALTER TABLE ADD [NOT] NULL`. Which are all details you'd have had to know/type in using the designer anyway. – Damien_The_Unbeliever Dec 12 '18 at 11:51

0 Answers0