SQL Server Full-Text Index with Overridable Data

I am trying to work with SQL Server full-text indexes, but have a very unique scenario and am hitting a snag. For the system we are building, we have a main table called Equipment that has some general columns: DisplayName, ModelNumber, Overview, Description, etc. and creating a full-text index on this is pretty basic. The full structure of this table is:

CREATE TABLE [dbo].[Equipment](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,       
    [CategoryId] [bigint] NOT NULL,
    [DistributorId] [bigint] NULL,
    [DistrbutorLocationId] [bigint] NULL,
    [ManufacturerId] [bigint] NOT NULL,
    [PrimaryImageId]  AS ([dbo].[fn_GetPrimaryImage]([Id])),
    [Condition] [tinyint] NOT NULL,
    [DisplayName] [nvarchar](255) NOT NULL,
    [ModelNumber] [nvarchar](75) NOT NULL,
    [Overview] [nvarchar](1024) NULL,
    [Description] [nvarchar](max) NOT NULL,
    [CurrentPrice]  AS (coalesce([SalePrice],[SuggestedPrice])),
    [SalePrice] [money] NULL,
    [SuggestedPrice] [money] NOT NULL,
    [Warranty] [nvarchar](max) NULL,
    [IsActive] [bit] NOT NULL,
    [IsPublished] [bit] NOT NULL,
 CONSTRAINT [PK_Equipment] 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]
)

The problem I have is users can override certain fields - solely for their "version" of an Equipment entity - with overrides stored in a separate table. For example, they can override DisplayName, but that's the only property/field they override - everything else would be the original values. They can then toggle these overrides on/off in case they temporarily override it, but then want to fall back to the default/original value. The structure of the overrides table is:

CREATE TABLE [dbo].[EquipmentOverride](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,       
    [DistributorId] [bigint] NOT NULL,
    [EquipmentId] [bigint] NOT NULL,
    [EquipmentKey] [tinyint] NOT NULL,
    [Value] [nvarchar](max) NOT NULL,
    [IsActive] [bit] NOT NULL,
 CONSTRAINT [PK_EquipmentOverride] 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]
)

So, individual equipment records can be added to the main table, but if a distributor wanted to override the DisplayName property, they would ultimately add a record to the EquipmentOverride table with a corresponding EquipmentId, EquipmentKey of 0 (correlates to DisplayName - these are all enums within a C# app) and their DistributorId. When pulling individual equipment records, I do a COALESCE(dbo.fn_GetDistributorEquipmentValue(@DistributorId, Equipment.Id, 0), Equipment.DisplayName) which gets me either the overridden value or falls back to the default value for an individual distributor. The structure of dbo.fn_GetDistributorEquipmentValue just looks at the EquipmentOverride table to see if one exists:

ALTER FUNCTION [dbo].[fn_GetDistributorEquipmentValue] 
(
    @DistributorId bigint,
    @EquipmentId bigint,
    @Key tinyint
)
RETURNS nvarchar(max)
AS
BEGIN
    
    RETURN (SELECT [Value]
            FROM EquipmentOverride
            WHERE DistributorId = @DistributorId AND
                  EquipmentId = @EquipmentId AND
                  EquipmentKey = @Key AND
                  IsActive = 1);

END

My main problem with this is trying to create a full-text index that is distributor-specific, since querying needs to include any overrides they may have done. The best solution I can come up with is a full-text catalog per distributor that is then queryable, but that feels very wrong and seems like a bad idea from a performance perspective - especially since the values are so dynamic that any queries would have to rebuild it for that distributor each time.

Has anyone run into a scenario like this where you need a dynamic full-text index? I did try utilizing LIKE operators, but if the description in a record says something like 'STIHL RMA 460' and someone searches for stihl 460, the results are null. Using CONTAINS here would work, but it doesn't include the override values so I'm back at square one. I tried to create a view that includes everything, but since that can't have a unique index (since overrides correlate back to individual Equipment records), SQL wouldn't let me create it.

Solution

You can simply add the EquipmentOverride table to the full-text index, then filter it by DistributorID when you query it.

This is going to be somewhat easier to do without functions. Just left-join it and filter it in the ON

SELECT
  e.*,
  ISNULL(eo.DisplayName, e.DisplayName) AS OverridenDisplayName
FROM Equipment e
LEFT JOIN EquipmentOverride eo
  ON eo.DistributorId = @DistributorId
 AND eo.EquipmentId = e.Id
 AND eo.EquipmentKey = 0
 AND eo.IsActive = 1
WHERE eo.DisplayName IS NULL AND CONTAINS(e.Value, @search)
   OR CONTAINS(eo.Value, @search);

In some instances it may be faster to query CONTAINSTABLE instead.

If you want to query across many fields and you want all the overrides then you could search inside a subquery.

SELECT
  e.*,
  ISNULL(eo.DisplayName, e.DisplayName) AS OverridenDisplayName,
  ISNULL(eo.ModelNumber, e.ModelNumber) AS OverridenModelNumber
FROM Equipment e
OUTER APPLY (
    SELECT
      MIN(CASE WHEN eo.EquipmentKey = 0 THEN e.Value END) AS DisplayName,
      MIN(CASE WHEN eo.EquipmentKey = 1 THEN e.Value END) AS ModelNumber,
      MIN(CASE WHEN CONTAINS(e.Value, @search) THEN 1 END) DoesContain
    FROM EquipmentOverride eo
    WHERE eo.DistributorId = @DistributorId
      AND eo.EquipmentId = e.Id
      AND eo.EquipmentKey in (0, 1)
      AND eo.IsActive = 1
) eo
WHERE eo.DisplayName IS NULL AND CONTAINS(e.Value, @search)
   OR eo.DoesContain = 1;