How do I make ALTER COLUMN idempotent?

Question

I have a migration script with the following statement:

ALTER TABLE [Tasks] ALTER COLUMN [SortOrder] int NOT NULL

1. What will happen if I run that twice? Will it change anything the second time? MS SQL Management Studio just reports "Command(s) completed successfully", but with no details on whether they actually did anything.

2. If it's not already idempotent, how do I make it so?

Answer 1

I would say that second time, SQL Server checks metadata and do nothing because nothing has changed.

But if you don't like possibility of multiple execution you can add simple condition to your script:

CREATE TABLE Tasks(SortOrder VARCHAR(100));

IF NOT EXISTS (SELECT 1
               FROM INFORMATION_SCHEMA.COLUMNS
               WHERE [TABLE_NAME] = 'Tasks'
                 AND [COLUMN_NAME] = 'SortOrder'
                 AND IS_NULLABLE = 'NO'
                 AND DATA_TYPE = 'INT')
BEGIN 
  ALTER TABLE [Tasks] ALTER COLUMN [SortOrder] INT NOT NULL
END

SqlFiddleDemo

Answer 2

1. When you execute it the second time, the query gets executed but since the table is already altered, there is no effect. So it makes no effect on the table.

2. No change is there when the script executes twice.

Here is a good MSDN read about: Inside ALTER TABLE

Let's look at what SQL Server does internally when performing an ALTER TABLE command. SQL Server can carry out an ALTER TABLE command in any of three ways:

1. SQL Server might need to change only metadata.
2. SQL Server might need to examine all the existing data to make sure it's compatible with the change but then change only metadata.
3. SQL Server might need to physically change every row.