HoneyHoney

USE AdventureWorks2012;

DECLARE @personId int = 1

CREATE TABLE #tmp_person(
 BusinessEntityID int,
 PersonType nchar(2),
 NameStyle bit,
 Title nvarchar(8),
 FirstName nvarchar(50),
 MiddleName nvarchar(50),
 LastName nvarchar(50),
 Suffix nvarchar(10),
 EmailPromotion int,
 AdditionalContactInfo xml,
 Demographics xml,
 rowguid uniqueidentifier,
 ModifiedDate datetime);

CREATE TABLE #tmp_address(
 AddressID int,
 AddressLine1 nvarchar(60),
 AddressLine2 nvarchar(60),
 City nvarchar(30),
 StateProvinceID int,
 PostalCode nvarchar(15),
 SpatialLocation geography,
 rowguid uniqueidentifier,
 ModifiedDate datetime);


INSERT INTO #tmp_person
SELECT TOP 1 *
FROM Person.Person
WHERE BusinessEntityID = @personId

--delete data
--DELETE FROM Person.Person WHERE BusinessEntityID = @personId


PRINT 'ROLLBACK SCRIPT'
PRINT '----------------------------------------'

SET NOCOUNT ON

CREATE TABLE #tables (id int identity PRIMARY KEY, table_name varchar(50), tmp_table_name varchar(50))

--tables to insert the data back
INSERT INTO #tables(table_name, tmp_table_name) VALUES ('Person.Person', '#tmp_person')
INSERT INTO #tables(table_name, tmp_table_name) VALUES ('Person.Address', '#tmp_address')

DECLARE @columns NVARCHAR(MAX) = N'';
DECLARE @sql NVARCHAR(MAX) = N'';
DECLARE @tablename VARCHAR(50),
  @tmptablename VARCHAR(50),
  @rowcount int

------------------- AdventureWorks2012 -------------------
PRINT 'USE AdventureWorks2012;'

DECLARE table_cursor CURSOR FAST_FORWARD LOCAL
FOR
SELECT table_name, tmp_table_name
FROM #tables
ORDER BY id

OPEN table_cursor
FETCH NEXT FROM table_cursor
INTO @tablename, @tmptablename

WHILE @@FETCH_STATUS = 0
 BEGIN

 --Check if data exists in temp table. Skip if no data
 SET @sql = N'SELECT 1 FROM ' + @tmptablename
 EXEC(@sql)

 SET @rowcount = @@rowcount

 IF @rowcount <> 0
 BEGIN
  -- get columns names to be inserted
  SELECT @columns = STUFF(
  (SELECT '+'''''', '''''' + ISNULL(CAST(' + name + ' AS varchar(2000)), ''NULL'')' FROM sys.columns c
   WHERE object_id = object_id(@tablename)
   ORDER BY c.column_id
   FOR XML PATH('')
  ), 1, 12, '')


  -- format INSERT script
  SET @sql = N'SELECT @sqlout = STUFF(
          (SELECT N''INSERT INTO ' + @tablename + ' VALUES ('''''' + ' + @columns + N' + N''''''); ''
          FROM ' + @tmptablename +'
          FOR XML PATH('''')
          ), 1, 0, '''')'

  EXEC sys.sp_executesql @sql, N'@sqlout NVARCHAR(MAX) OUTPUT',  @sqlout = @sql OUTPUT

  SET @sql = REPLACE(@sql, '''NULL''', 'NULL')
  SET @sql = REPLACE(@sql, 'INSERT INTO', CHAR(10)+'INSERT INTO')

  PRINT @sql

 END

 FETCH NEXT FROM table_cursor
 INTO @tablename, @tmptablename

 END

CLOSE table_cursor
DEALLOCATE table_cursor

Adding NOT NULL Columns as an Online Operation

Starting with SQL Server 2012 Enterprise Edition, adding a NOT NULL column with a default value is an online operation when the default value is a runtime constant. This means that the operation is completed almost instantaneously regardless of the number of rows in the table. This is because the existing rows in the table are not updated during the operation; instead, the default value is stored only in the metadata of the table and the value is looked up as needed in queries that access these rows. This behavior is automatic; no additional syntax is required to implement the online operation beyond the ADD COLUMN syntax. A runtime constant is an expression that produces the same value at runtime for each row in the table regardless of its determinism. For example, the constant expression "My temporary data", or the system function GETUTCDATETIME() are runtime constants. In contrast, the functions NEWID() or NEWSEQUENTIALID() are not runtime constants because a unique value is produced for each row in the table. Adding a NOT NULL column with a default value that is not a runtime constant is always performed offline and an exclusive (SCH-M) lock is acquired for the duration of the operation.

1. 创建一个新的column
2. 为新的column赋值
3. 将前一个column标记dropped