Full Text Index on VIEW to pull in JOIN data (T-SQL)

full-text-view.sql

---------------------------------------------------
-- Users table
---------------------------------------------------
CREATE TABLE [Accounts].[Users]
(
	[UserId] INT IDENTITY(1,1) NOT NULL,
	[Username] VARCHAR(50) NOT NULL,
	[Email] VARCHAR(255) NOT NULL,
	[EmailIsVerified] BIT NOT NULL CONSTRAINT [DF_Users_EmailIsVerified] DEFAULT 0,
    [CreationDate] DATETIME NOT NULL CONSTRAINT [DF_Users_CreationDate] DEFAULT GETDATE(), 
	[LastLoginDate] DATETIME NOT NULL CONSTRAINT [DF_Users_LastLoginDate] DEFAULT GETDATE(),
    [IsDeleted] BIT NOT NULL DEFAULT 0, 
    CONSTRAINT [PK_Users_UserId] PRIMARY KEY ([UserId])
)

GO
CREATE INDEX [IX_Users_Username] ON [Accounts].[Users] ([Username])
GO
CREATE INDEX [IX_Users_Email] ON [Accounts].[Users] ([Email])
GO

---------------------------------------------------
-- Agents table
---------------------------------------------------
CREATE TABLE [Accounts].[Agents]
(
	[AgentId] INT IDENTITY(1,1) NOT NULL, 
	[UserId] INT NOT NULL,
	[Name] NVARCHAR(255) NOT NULL,
    [IsDeleted] BIT NOT NULL DEFAULT 0, 
    CONSTRAINT [PK_Agents_AgentId] PRIMARY KEY ([AgentId]), 
    CONSTRAINT [UQ_Agents_UserId] UNIQUE ([UserId]), 
    CONSTRAINT [FK_Agents_ToUsers] FOREIGN KEY ([UserId]) REFERENCES [Accounts].[Users]([UserId])
)

GO
CREATE INDEX [IX_Agents_Name] ON [Accounts].[Agents] ([Name])
GO
CREATE FULLTEXT INDEX ON [Accounts].[Agents] ([Name]) KEY INDEX [PK_Agents_AgentId] WITH CHANGE_TRACKING AUTO
GO

---------------------------------------------------
-- Templates table
---------------------------------------------------
CREATE TABLE [Inspections].[Templates]
(
	[TemplateId] INT IDENTITY(1,1) NOT NULL,
	[Name] NVARCHAR(100) NOT NULL,
	[Tagline] NVARCHAR(100) NOT NULL,
	[AuthorUserId] INT NOT NULL,
	[IsDeleted] BIT NOT NULL DEFAULT 0, 
    CONSTRAINT [PK_Templates_TemplateId] PRIMARY KEY ([TemplateId]), 
    CONSTRAINT [FK_Templates_ToUsers] FOREIGN KEY ([AuthorUserId]) REFERENCES [Accounts].[Users]([UserId])
)

GO
CREATE INDEX [IX_Templates_Name] ON [Inspections].[Templates] ([Name])
GO
CREATE INDEX [IX_Templates_AuthorUserId] ON [Inspections].[Templates] ([AuthorUserId])
GO

---------------------------------------------------
-- Awesome SearchableTemplates view
---------------------------------------------------
CREATE VIEW [Inspections].[SearchableTemplates] 
	WITH SCHEMABINDING
AS 
SELECT 
	TemplateId,
	T.Name AS Name,
	Tagline,
	AuthorUserId,
	U.Username AS AuthorUsername,
	A.Name AS AuthorName
FROM [Inspections].[Templates] T
INNER JOIN [Accounts].[Users] U ON T.AuthorUserId = U.UserId AND U.IsDeleted = 0
INNER JOIN [Accounts].[Agents] A ON T.AuthorUserId = A.UserId AND A.IsDeleted = 0
WHERE T.IsDeleted = 0
GO

CREATE UNIQUE CLUSTERED INDEX UQC_SearchableTemplates_TemplateId 
ON [Inspections].[SearchableTemplates](TemplateId)
GO

CREATE FULLTEXT INDEX ON [Inspections].[SearchableTemplates](
	Name,
	Tagline,
	AuthorUsername,
	AuthorName
) KEY INDEX [UQC_SearchableTemplates_TemplateId]
WITH CHANGE_TRACKING AUTO