Altering a View does re-create full-text index properly

drewmann73

I have several views on which i create a primary key index and a full txt index on a column (my specified full-text search column). When this view changes the resulting script fails to consider the full text index on the 'search column'. Below is the script created by SQL Compare. Below this I have provided MY EQUIVALENT script used to setup the view and full text indexes in the first place. *Note that SQL compare detects the index in its compare, it does not crete the code to re-create it after view is modified/altered.

/*
Run this script on:

(local)DREWSQL2014.AS_Branch_ben (Rev. 2918) - This database will be modified

to synchronize it with:

(local)DREWSQL2014.AS_Master (Rev. 2918)

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

Script created by SQL Compare version 11.5.2 from Red Gate Software Ltd at 22/07/2016 1:23:43 PM

*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [Common].[SEARCH_Inventory]'
GO








ALTER VIEW [Common].[SEARCH_Inventory]
WITH SCHEMABINDING
AS
SELECT InventoryID, TenantID,
I.InventoryCode,IsActive,I.IsInCatalogue
,REPLACE(I.InventoryCode,'&','') + ' ' + ISNULL(REPLACE(ShortDescription,'&',''), '') + ' ' + ISNULL(I.Description, '')
+ ' ' + ISNULL(I.Barcode,'') + ' ' + ISNULL(I.SKU,'') + ' ' + ISNULL(I.EAN,'')
+ ' ' + ISNULL(I.ModelNumber,'') + ' ' + ISNULL(I.VariantColour,'')
+ ' ' + ISNULL(I.VariantSize,'') + ' ' + ISNULL(Variation,'')
+ ' ' + REVERSE(REPLACE(I.InventoryCode,'&','')) + ' ' + REVERSE(ISNULL(I.SKU,'')) + ' ' + REVERSE(ISNULL(I.Barcode,''))
AS SearchContent
FROM Common.Inventory AS I
GO
@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating index [SEARCH_Inventory_InventoryID] on [Common].[SEARCH_Inventory]'
GO
CREATE UNIQUE CLUSTERED INDEX [SEARCH_Inventory_InventoryID] ON [Common].[SEARCH_Inventory] ([InventoryID])
GO
@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating index [SEARCH_Inventory_TenantID_InventoryID] on [Common].[SEARCH_Inventory]'
GO
CREATE NONCLUSTERED INDEX [SEARCH_Inventory_TenantID_InventoryID] ON [Common].[SEARCH_Inventory] ([TenantID], [InventoryID])
GO
@ERROR <> 0 SET NOEXEC ON
GO
/*
Start of RedGate SQL Source Control versioning database-level extended properties.
*/
DECLARE @RG_SC_VERSION BIGINT
SET @RG_SC_VERSION = 2918
IF EXISTS (SELECT 1 FROM fn_listextendedproperty(N'SQLSourceControl Database Revision', NULL, NULL, NULL, NULL, NULL, NULL))
EXEC sp_dropextendedproperty N'SQLSourceControl Database Revision', NULL, NULL, NULL, NULL, NULL, NULL
EXEC sp_addextendedproperty N'SQLSourceControl Database Revision', @RG_SC_VERSION, NULL, NULL, NULL, NULL, NULL, NULL
GO
IF EXISTS (SELECT 1 FROM fn_listextendedproperty(N'SQLSourceControl Scripts Location', NULL, NULL, NULL, NULL, NULL, NULL))
EXEC sp_dropextendedproperty N'SQLSourceControl Scripts Location', NULL, NULL, NULL, NULL, NULL, NULL
EXEC sp_addextendedproperty N'SQLSourceControl Scripts Location', N'<?xml version="1.0" encoding="utf-16" standalone="yes"?>
<ISOCCompareLocation version="1" type="SvnLocation">
<RepositoryUrl>https://code.anthemsoftware.net/svn/AS/branches/Bali/Database/AS/</RepositoryUrl&gt;
</ISOCCompareLocation>', NULL, NULL, NULL, NULL, NULL, NULL
GO
IF EXISTS (SELECT 1 FROM fn_listextendedproperty(N'SQLSourceControl Migration Scripts Location', NULL, NULL, NULL, NULL, NULL, NULL))
EXEC sp_dropextendedproperty N'SQLSourceControl Migration Scripts Location', NULL, NULL, NULL, NULL, NULL, NULL
EXEC sp_addextendedproperty N'SQLSourceControl Migration Scripts Location', N'<?xml version="1.0" encoding="utf-16" standalone="yes"?>
<ISOCCompareLocation version="1" type="SvnLocation">
<RepositoryUrl>https://code.anthemsoftware.net/svn/AS/branches/Bali/Database/MigrationScripts/</RepositoryUrl&gt;
</ISOCCompareLocation>', NULL, NULL, NULL, NULL, NULL, NULL
GO
/*
End of RedGate SQL Source Control versioning database-level extended properties.
*/
GO
COMMIT TRANSACTION
GO
DECLARE @Success AS BIT
SET @Success = 1
SET NOEXEC OFF
IF (@Success = 1) PRINT 'The database update succeeded'
ELSE BEGIN
@TRANCOUNT > 0 ROLLBACK TRANSACTION
PRINT 'The database update failed'
END
GO
PRINT N'Adding full text indexing to tables'
GO
CREATE FULLTEXT INDEX ON [Common].[SEARCH_Inventory] KEY INDEX [SEARCH_Inventory_InventoryID] ON [Common] WITH STOPLIST OFF
GO
@ERROR <> 0 SET NOEXEC ON
GO
ALTER FULLTEXT INDEX ON [Common].[SEARCH_Inventory] ENABLE
GO
@ERROR <> 0 SET NOEXEC ON
GO

---

Script by Andrew Mann. NOte missing index at end of this script.

/****** Object: View [Common].[InventorySearch] Script Date: 29/02/2016 5:32:27 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO








ALTER VIEW [Common].[SEARCH_Inventory]
WITH SCHEMABINDING
AS
SELECT InventoryID, TenantID,
I.InventoryCode,IsActive,I.IsInCatalogue
,REPLACE(I.InventoryCode,'&','') + ' ' + ISNULL(REPLACE(ShortDescription,'&',''), '') + ' ' + ISNULL(I.Description, '')
+ ' ' + ISNULL(I.Barcode,'') + ' ' + ISNULL(I.SKU,'') + ' ' + ISNULL(I.EAN,'')
+ ' ' + ISNULL(I.ModelNumber,'') + ' ' + ISNULL(I.VariantColour,'')
+ ' ' + ISNULL(I.VariantSize,'') + ' ' + ISNULL(Variation,'')
+ ' ' + REVERSE(REPLACE(I.InventoryCode,'&','')) + ' ' + REVERSE(ISNULL(I.SKU,'')) + ' ' + REVERSE(ISNULL(I.Barcode,''))
AS SearchContent
FROM Common.Inventory AS I




GO


-- SEARCH_Inventory
-- Indexes
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name ='SEARCH_Inventory_InventoryID')
BEGIN

CREATE UNIQUE CLUSTERED INDEX [SEARCH_Inventory_InventoryID] ON [Common].[SEARCH_Inventory]
(
[InventoryID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

END

GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name ='SEARCH_Inventory_TenantID_InventoryID')
BEGIN

/****** Object: Index [WebPageID] Script Date: 03/04/2012 11:41:11 ******/
CREATE NONCLUSTERED INDEX [SEARCH_Inventory_TenantID_InventoryID] ON [Common].[SEARCH_Inventory]
(
[TenantID],[InventoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

END
GO


CREATE FULLTEXT INDEX ON [Common].[SEARCH_Inventory] KEY INDEX [SEARCH_Inventory_InventoryID] ON [Common] WITH STOPLIST OFF
GO

-- THis section is missing when SQL Compare creates the script.

ALTER FULLTEXT INDEX ON [Common].[SEARCH_Inventory] ADD ([SearchContent] LANGUAGE 1033)
GO
ALTER FULLTEXT INDEX ON [Common].[SEARCH_Inventory] ENABLE
GO

Rob C

Hi Drew,

Sorry that you're experiencing this problem. I suspect this might be a bug that we've fixed since 11.5.2, so it would be good if you could try the latest version (11.6.11) to see if the problem still occurs. Here's a download link if you need it - ftp://support.red-gate.com/patches/SQLC ... 1.2463.exe

Does that help?