Sql-server – the appropriate index architecture when forced to implement IsDeleted (soft deletes)

Key lookup operations can be avoided by the use of a covering index. Full-text-indexes cannot be "included" in a covering index, however, including the TitleTable in the covering index is still useful since SQL Server can find all the details it needs for the query, aside from the full-text-query results, by seeking the covering index.

I've created a simple test-bed to show this in action.

First, we'll create an empty database for our test, since we cannot create full-text catalogs in tempdb:

USE master;
IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = N'FullTextTest') 
BEGIN
    ALTER DATABASE FullTextTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE FullTextTest;
END
CREATE DATABASE FullTextTest;
ALTER DATABASE FullTextTest SET RECOVERY SIMPLE;
BACKUP DATABASE FullTextTest TO DISK = 'NUL:';
GO

Here, we'll create a mock-up of your table. You say in the question that all mentioned columns have an associated non-clustered index, so we'll define those too:

USE FullTextTest;
GO

CREATE FULLTEXT CATALOG ftc
WITH ACCENT_SENSITIVITY = OFF
AS DEFAULT
AUTHORIZATION dbo;

CREATE TABLE dbo.TitleTable
(
    PK_ID int NOT NULL
        CONSTRAINT PK_TitleTable
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , FKID1 int NOT NULL
    , FKID2 int NOT NULL
    , Title nvarchar(100) NOT NULL
);

CREATE NONCLUSTERED INDEX IX_TitleTable_FKID1
ON dbo.TitleTable (FKID1);

CREATE NONCLUSTERED INDEX IX_TitleTable_FKID2
ON dbo.TitleTable (FKID2);

CREATE NONCLUSTERED INDEX IX_TitleTable_Title
ON dbo.TitleTable (Title);

Here's the full-text index:

CREATE FULLTEXT INDEX ON dbo.TitleTable(Title) 
KEY INDEX PK_TitleTable 
ON ftc 
WITH (CHANGE_TRACKING = AUTO, STOPLIST SYSTEM);

I've got a database with around 47,000 words in it which I'll use to fill the dbo.TitleTable table:

INSERT INTO dbo.TitleTable (FKID1, FKID2, Title)
SELECT wl.WordRow, wl.WordRow, wl.Word
FROM WordsDB.dbo.WordList wl;

Here's the query from your question:

DECLARE @ID_PARAM1 int;
DECLARE @ID_PARAM2 int;
DECLARE @SINGLE_WORD_PARAM nvarchar(100);
SET @ID_PARAM1 = 46777;
SET @ID_PARAM2 = 46777
SET @SINGLE_WORD_PARAM = N'"' + 'it' + N'"';
SELECT T2.Title 
FROM TitleTable T1
    INNER JOIN TitleTable T2 ON T2.FKID1 = T1.FKID1
WHERE T1.FKID2 = @ID_PARAM1 
    AND T2.FKID2 = @ID_PARAM2 
    AND CONTAINS(T1.Title, @SINGLE_WORD_PARAM);

At this point, if we run the query, we see the following plan:

As expected, there is an index seek on the IX_TitleTable_FKID2 non-clustered index, with an associated key-lookup against the table itself for the Title column.

If we add a compound index on both FKID2 and FKID1, we'd expect a different plan, which is what we get:

CREATE INDEX IX_TitleTable_FTS_Cover_NoInclude
ON dbo.TitleTable (FKID2, FKID1);

However, the key lookup for the Title column is still there. What if we add an INCLUDE clause to our index above?

CREATE INDEX IX_TitleTable_FTS_Cover
ON dbo.TitleTable (FKID2, FKID1)
INCLUDE (Title);

Success! No key-lookup operation required. The cost of the query has also dropped from 0.016 to 0.013, so that's a win.

Primary Key and Unique constraints are enforced by a supporting index for performance and to enforce uniqueness. Just because the index is not used for searching does not mean it isn't being used to guarantee uniqueness of the key values.

Whether or not a table has a primary key constraint or any unique constraints is not a performance decision - it is a data integrity decision. The primary key gets a primary key constraint and the candidate keys get unique constraints.

Whether or not the primary key index is clustered or nonclustered, on the other hand, is a performance question. Doing a Binoogle search on that will find many hits, including https://stackoverflow.com/questions/2206541/index-view-how-to-choose-the-clustered-index.