What are the challenges you face when working across database platforms? Take the survey
Options

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

chuckchuck Posts: 29 Bronze 2
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:

Answers

  • Options
    chuckchuck Posts: 29 Bronze 2
    By the way, the version of sql compare is 13.7 .4 and the version of sql source control is 7.0.11

  • Options
    Jessica RJessica R Posts: 1,319 Rose Gold 4
    Thanks for your post, @chuck!

    I am able to reproduce this and am looking into it- please bear with me.

    Jessica Ramos | Product Support Engineer | Redgate Software

    Have you visited our Help Center?


  • Options
    Jessica RJessica R Posts: 1,319 Rose Gold 4
    Hi @chuck,

    I've logged a bug for this with reference SC-10449 and will let you know once we have an update.

    Jessica Ramos | Product Support Engineer | Redgate Software

    Have you visited our Help Center?


  • Options
    Jessica RJessica R Posts: 1,319 Rose Gold 4

    Hi @chuck!

    I'm happy to report that this issue has been resolved as of today's frequent update release, v13.7.8. Can you please update from Help>Check for updates (you need to make sure that you have frequent updates enabled) and give this a try?

    I hope that helps but please let us know if you run into any other issues!

    Jessica Ramos | Product Support Engineer | Redgate Software

    Have you visited our Help Center?


  • Options
    chuckchuck Posts: 29 Bronze 2
          I have upgrade the sql compare to 13.7.8, and test it with previous test case.
         The generated scripts works now, the index was re-created.
          Thanks.
  • Options
    Jessica RJessica R Posts: 1,319 Rose Gold 4
    Thanks for confirming, @chuck - glad to hear it helped! :)

    Jessica Ramos | Product Support Engineer | Redgate Software

    Have you visited our Help Center?


Sign In or Register to comment.