In generated deployment script sp_refreshview is executed before creating index on an indexed view.
Ante_T
Posts: 2 New member
in SQL Compare
Context:
I have two databases (DB1 and DB2) with only one difference, Table_1 has different id type.
Each database has two identical views:
idx_view - indexed view that selects id and text from Table_1.
view_1 - regular view that selects * from idx_view WITH (NOEXPAND).
When I compare DB2 and DB1 and create a deployment script (with "Deploy all dependencies" option checked) using SQL Compare I get the following script:
------------------------------------------------------------------------------------------------------
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].[idx_view]'
GO
ALTER VIEW [dbo].[idx_view]
AS
SELECT id, text
FROM dbo.Table_1;
GO
PRINT N'Dropping constraints from [dbo].[Table_1]'
GO
ALTER TABLE [dbo].[Table_1] DROP CONSTRAINT [PK__Table_1_id]
GO
PRINT N'Altering [dbo].[Table_1]'
GO
ALTER TABLE [dbo].[Table_1] ALTER COLUMN [id] [tinyint] NOT NULL
GO
PRINT N'Creating primary key [PK__Table_1_id] on [dbo].[Table_1]'
GO
ALTER TABLE [dbo].[Table_1] ADD CONSTRAINT [PK__Table_1_id] PRIMARY KEY CLUSTERED ([id])
GO
PRINT N'Refreshing [dbo].[view_1]'
GO
EXEC sp_refreshview N'[dbo].[view_1]'
GO
PRINT N'Altering [dbo].[idx_view]'
GO
ALTER VIEW [dbo].[idx_view]
WITH SCHEMABINDING
AS
SELECT id, text
FROM dbo.Table_1;
GO
PRINT N'Creating index [idx_idx_view] on [dbo].[idx_view]'
GO
CREATE UNIQUE CLUSTERED INDEX [idx_idx_view] ON [dbo].[idx_view] ([id])
GO
------------------------------------------------------------------------------------------------------
When i run the generated script on DB1 I get an error "Hint 'noexpand' on object 'idx_view' is invalid.".
This error is raised because
"EXEC sp_refreshview N'[dbo].[view_1]'"
is executed before creating the index on idx_view
"CREATE UNIQUE CLUSTERED INDEX [idx_idx_view] ON [dbo].[idx_view] ([id])".
Does anyone know why the deployment script is generated this way or how could I work around it?
Databases and objects used:
-- Create the database DB1
CREATE DATABASE DB1;
GO
-- Use the DB1 database
USE DB1;
GO
-- Create the Table_1 table
CREATE TABLE [dbo].[Table_1](
[id] [int] IDENTITY(1,1) NOT NULL,
[text] [varchar](50) NULL,
CONSTRAINT [PK__Table_1_id] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY];
GO
-- Create the idx_view indexed view
CREATE VIEW idx_view
WITH SCHEMABINDING
AS
SELECT id, text
FROM dbo.Table_1;
GO
CREATE UNIQUE CLUSTERED INDEX idx_idx_view
ON idx_view (id);
GO
-- Create the view_1 view
CREATE VIEW [dbo].[view_1] AS
SELECT *
FROM idx_view WITH (NOEXPAND);
GO
-- Create the database DB2
CREATE DATABASE DB2;
GO
-- Use the DB2 database
USE DB2;
GO
-- Create the Table_1 table
CREATE TABLE [dbo].[Table_1](
[id] [tinyint] IDENTITY(1,1) NOT NULL,
[text] [varchar](50) NULL,
CONSTRAINT [PK__Table_1_id] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY];
GO
-- Create the idx_view indexed view
CREATE VIEW idx_view
WITH SCHEMABINDING
AS
SELECT id, text
FROM dbo.Table_1;
GO
CREATE UNIQUE CLUSTERED INDEX idx_idx_view
ON idx_view (id);
GO
-- Create the view_1 view
CREATE VIEW [dbo].[view_1] AS
SELECT *
FROM idx_view WITH (NOEXPAND);
GO
---------------------------------------------------------
Checked SQL Compare project options:
Auto-map similar columns
Decrypt encrypted objects
Don't add error handling statements to deployment scripts
Don't use transactions in deployment scripts
Enable SQL Monitor integration
Ignore database and server names in synonyms
Ignore extended properties
Ignore filegroups, partition schemes and partition functions
Ignore fill factor and index padding
Ignore user properties
Ignore white space
Ignore WITH element order
Comparing: DB2>DB1
Comparing: DB2>DB1
SQL Compare version:
14.10.9.22680 Standard
Tagged:
Answers
Using your scripts, I've been able to reproduce the issue and I do see that the script is generated in the wrong order. So I will raise the issue with the Dev team and then get back to you with their feedback.