Create/Drop MS SQL FullText Catalog & Index (Transact-SQL)

-- Change default full-text language to Neutral!
-- It can be skipped, if specific language code will be provided when creating index.

USE Master
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'default full-text language', '0';
RECONFIGURE WITH OVERRIDE;
GO

USE [MyFulltextDatabase]
GO

-- Drop catalog if was created
-- Drop indexes first

IF  EXISTS (SELECT * FROM sys.fulltext_indexes fti WHERE fti.object_id = OBJECT_ID(N'[dbo].[MyTable]'))
   ALTER FULLTEXT INDEX ON [dbo].[MyTable] DISABLE
GO

IF  EXISTS (SELECT * FROM sys.fulltext_indexes fti WHERE fti.object_id = OBJECT_ID(N'[dbo].[MyTable]'))
   DROP FULLTEXT INDEX ON [dbo].[MyTable]
GO

IF  EXISTS (SELECT * FROM sysfulltextcatalogs ftc WHERE ftc.name = N'FullTextCatalog')
   DROP FULLTEXT CATALOG [FullTextCatalog]
GO

-- Create new catalog in current database

CREATE FULLTEXT CATALOG [FullTextCatalog]
ON FILEGROUP [PRIMARY]
WITH ACCENT_SENSITIVITY = OFF
AS DEFAULT
AUTHORIZATION [dbo]
GO

-- Create a new index on the specified table

CREATE FULLTEXT INDEX
	ON [dbo].[MyTable]
	KEY INDEX [PrimaryKeyName]
	ON [MyFulltextDatabase]
	WITH CHANGE_TRACKING AUTO
GO
ALTER FULLTEXT INDEX ON [dbo].[MyTable] ADD ([MyColumn1] LANGUAGE 0x0)
GO
ALTER FULLTEXT INDEX ON [dbo].[MyTable] ADD ([MyColumn2] LANGUAGE 0x0)
GO

More info about FullText indexes: http://msdn.microsoft.com/en-us/library/ms187317.aspx

Advertisements

SharePoint search & crawling links

ALTER VIEW drops index!

Recently I got error:

View ‘dbo.MyMaterializedView’ is not an indexed view. Full-text index is not allowed to be created on it.

All database scripts are being in Visual Studio using Database project type. Upgrades also are being managed by making separate scripts folder in same project and including those scripts in predefined sql file: “Scripts/Post-Deployment/Script.PostDeployment”. In this way we can include upgrade to the project output – big sql file for deploy.

We are using fulltext index in our DB. To use fulltext predicates (CONTAINS, FREETEXT) on view we created materialized view (WITH SCHEMABINDING):

CREATE VIEW [dbo].[MyMaterializedView]
with schemabinding
AS
SELECT
dbo.TabelA.PKCol as [Id],
dbo.TableA.Col1,
dbo.TableB.Col2
FROM dbo.TableA INNER JOIN dbo.TableB
ON dbo.TableA.PKCol = dbo.TableB.FKTableA
GO
-- Create unique clustered key for use in FullText index
CREATE UNIQUE CLUSTERED INDEX [PK_MyMaterializedView]
ON [dbo].[MyMaterializedView] ([Id]) ON [PRIMARY]
GO
-- Create FullText index
CREATE FULLTEXT INDEX
ON [dbo].[MyMaterializedView]
KEY INDEX [PK_MyMaterializedView]
ON [MyFullTextCatalog]
WITH CHANGE_TRACKING AUTO
GO
-- Specify columns to FullText index (neutral language is used)
ALTER FULLTEXT INDEX ON [dbo].[MyMaterializedView] ADD ([Col1] LANGUAGE 0x0)
GO

After a while developer created new upgrade that changes view by adding a new column to it:

ALTER VIEW [dbo].[MyMaterializedView]
with schemabinding
AS
SELECT
dbo.TabelA.PKCol as [Id],
dbo.TableA.Col1,
dbo.TableB.Col2,
dbo.TableA.Col2
FROM dbo.TableA INNER JOIN dbo.TableB
ON dbo.TableA.PKCol = dbo.TableB.FKTableA
GO

As result search query that uses this view fails after DB have been upgraded!
And most strange thing is that deploy script didn’t show any error message while executing ALTER VIEW statement 😦
The root of the problem is trivial: ALTER VIEW statement drops all indexes that were applied on view – also FullText index!
Conclusion: indexes must be recreated after altering view:

ALTER VIEW [dbo].[MyMaterializedView]
with schemabinding
AS
SELECT
dbo.TabelA.PKCol as [Id],
dbo.TableA.Col1,
dbo.TableB.Col2,
dbo.TableA.Col2
FROM dbo.TableA INNER JOIN dbo.TableB
ON dbo.TableA.PKCol = dbo.TableB.FKTableA
GO
-- Alter view command drops view index!
CREATE UNIQUE CLUSTERED INDEX [PK_MyMaterializedView]
ON [dbo].[MyMaterializedView] ([Id]) ON [PRIMARY]
GO
-- Also Full Text index must be recreated!
CREATE FULLTEXT INDEX
ON [dbo].[MyMaterializedView]
KEY INDEX [PK_MyMaterializedView]
ON [MyFullTextCatalog]
WITH CHANGE_TRACKING AUTO
GO
ALTER FULLTEXT INDEX ON [dbo].[MyMaterializedView] ADD ([Col1] LANGUAGE 0x0)
GO