Bug in drop and create with indexed view

EdwinHaddeman

SQL Compare 10.4 (and 10.5 beta) seems to create an invalid changescript in the situation that can be reproduced by the steps below.

To summarize: in the original database there's a table with an indexed view. In the target database there are both changes in the table definition and in the view definition and SQLCompare doesn't seem to handle this situation correctly when using DropAndCreateInsteadofAlter.

---- Simple test database
create database TestSQLCompareBug
go
use TestSQLCompareBug
go

create table TestTabel (number int, code char(3))
go

create view TestView 
with schemabinding 
as 
select number, code 
from dbo.TestTabel where number < 10
go

create unique clustered index TestIndex on TestView (number) with (STATISTICS_NORECOMPUTE=ON)
go


---- Make some changes in another version of the database
create database TestSQLCompareBug_WithChanges
go
use TestSQLCompareBug_WithChanges
go

-- Make a change to the table definition: add a column
create table TestTabel (number int, code char(3), description varchar(100))
go

create view TestView 
with schemabinding 
as 
select number, code 
from dbo.TestTabel where number < 10
go

-- Also make a change to the view definition
create unique clustered index TestIndex on TestView (number)
go

Now compare the two databases using:

SQLCompare.exe /s1:dbserver /s2:dbserver /db2:TestSQLCompareBug /db1:TestSQLCompareBug_WithChanges /sf:testsqlcomparebugchangescript.sql /force /options np,ie,ip,iq,iw,if,icm,incd,oec,dacia /include:ddltrigger /include:table /include:storedprocedure /include:view

In the resulting changescript the view is dropped and later recreated, but inbetween the script tries to recreate the index on the - by then dropped - view.

/*
Run this script on:

        dbserver.TestSQLCompareBug    -  This database will be modified

to synchronize it with:

        dbserver.TestSQLCompareBug_WithChanges

You are recommended to back up your database before running this script

Script created by SQL Compare version 10.4.8 from Red Gate Software Ltd at 24-03-2014 14:35:18

*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
PRINT N'Removing schema binding from [dbo].[TestView]'
GO
IF OBJECT_ID(N'[dbo].[TestView]', 'V') IS NOT NULL
EXEC sp_executesql N'
ALTER view [dbo].[TestView] 
as 
select number, code 
from dbo.TestTabel where number < 10
'
GO
PRINT N'Dropping [dbo].[TestView]'
GO
IF OBJECT_ID(N'[dbo].[TestView]', 'V') IS NOT NULL
DROP VIEW [dbo].[TestView]
GO
PRINT N'Altering [dbo].[TestTabel]'
GO
IF COL_LENGTH(N'[dbo].[TestTabel]', N'description') IS NULL
ALTER TABLE [dbo].[TestTabel] ADD[description] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
PRINT N'Altering [dbo].[TestView]'
GO
IF OBJECT_ID(N'[dbo].[TestView]', 'V') IS NOT NULL
EXEC sp_executesql N'
ALTER view [dbo].[TestView] 
with schemabinding 
as 
select number, code 
from dbo.TestTabel where number < 10
'
GO
PRINT N'Creating index [TestIndex] on [dbo].[TestView]'
GO
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'TestIndex' AND object_id = OBJECT_ID(N'[dbo].[TestView]'))
CREATE UNIQUE CLUSTERED INDEX [TestIndex] ON [dbo].[TestView] ([number]) ON [PRIMARY]
GO
PRINT N'Creating [dbo].[TestView]'
GO
IF OBJECT_ID(N'[dbo].[TestView]', 'V') IS NULL
EXEC sp_executesql N'
create view [dbo].[TestView] 
with schemabinding 
as 
select number, code 
from dbo.TestTabel where number < 10
'
GO

Output from running this script:

Removing schema binding from [dbo].[TestView]
Dropping [dbo].[TestView]
Altering [dbo].[TestTabel]
Altering [dbo].[TestView]
Creating index [TestIndex] on [dbo].[TestView]
Msg 1088, Level 16, State 12, Line 2
Cannot find the object "dbo.TestView" because it does not exist or you do not have permissions.
Creating [dbo].[TestView]

Anu D

Thanks for your post.

We have logged a support ticket for you and we will reply you shortly.

samhanes

I am experiencing this issue, can you confirm that it has not been resolved? Is there a workaround?