Use drop and create did not work with index on both normal column and computed column

chuckchuck Posts: 29 Bronze 2
in SQL Compare
You can reproduce this bug with the following steps:
1. create a new database
2. create a new table with four columns
id int
a int
b int
c is a computed column(c=a+b)
3. create a new clustered index on table
    and the index key columns is column id and column c
4. import the datbase with table and index into sql source control
5. modify the column type of column b to bigint
6. commit the change of column b
7. use sql compare to genrerate sql scripts about step 5

You will get the scripts like this :
The index was dropped without re-created
SET NUMERIC_ROUNDABORT OFF<br>GO<br>SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON<br>GO<br>SET XACT_ABORT ON<br>GO<br>SET TRANSACTION ISOLATION LEVEL Serializable<br>GO<br>BEGIN TRANSACTION<br>GO<br>IF @@ERROR <> 0 SET NOEXEC ON<br>GO<br>PRINT N'Dropping index [ClusteredIndex-20190312-131056] from [dbo].[Table_1]'<br>GO<br>IF EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'ClusteredIndex-20190312-131056' AND object_id = OBJECT_ID(N'[dbo].[Table_1]'))<br>DROP INDEX [ClusteredIndex-20190312-131056] ON [dbo].[Table_1]<br>GO<br>IF @@ERROR <> 0 SET NOEXEC ON<br>GO<br>PRINT N'Altering [dbo].[Table_1]'<br>GO<br>IF @@ERROR <> 0 SET NOEXEC ON<br>GO<br>IF COL_LENGTH(N'[dbo].[Table_1]', N'c') IS NOT NULL<br>ALTER TABLE [dbo].[Table_1] DROP COLUMN [c]<br>GO<br>IF @@ERROR <> 0 SET NOEXEC ON<br>GO<br>ALTER TABLE [dbo].[Table_1] ALTER COLUMN [b] [bigint] NULL<br>GO<br>IF @@ERROR <> 0 SET NOEXEC ON<br>GO<br>IF COL_LENGTH(N'[dbo].[Table_1]', N'c') IS NULL<br>ALTER TABLE [dbo].[Table_1] ADD[c] AS ([a]+[b])<br>GO<br>IF @@ERROR <> 0 SET NOEXEC ON<br>GO<br>COMMIT TRANSACTION<br>GO<br>IF @@ERROR <> 0 SET NOEXEC ON<br>GO<br>-- This statement writes to the SQL Server Log so SQL Monitor can show this deployment.<br>IF HAS_PERMS_BY_NAME(N'sys.xp_logevent', N'OBJECT', N'EXECUTE') = 1<br>BEGIN<br>    DECLARE @databaseName AS nvarchar(2048), @eventMessage AS nvarchar(2048)<br>    SET @databaseName = REPLACE(REPLACE(DB_NAME(), N'\', N'\\'), N'"', N'\"')<br>    SET @eventMessage = N'Redgate SQL Compare: { "deployment": { "description": "Redgate SQL Compare deployed to ' + @databaseName + N'", "database": "' + @databaseName + N'" }}'<br>    EXECUTE sys.xp_logevent 55000, @eventMessage<br>END<br>GO<br>DECLARE @Success AS BIT<br>SET @Success = 1<br>SET NOEXEC OFF<br>IF (@Success = 1) PRINT 'The database update succeeded'<br>ELSE BEGIN<br>	IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION<br>	PRINT 'The database update failed'<br>END<br>GO<br><br>


Tagged:
· Share on Twitter

Answers

Sign In or Register to comment.