Here's a script I whipped up that illustrates basically what I'm saying
use master
CREATE DATABASE FT_TEST
GO
use FT_TEST
GO
CREATE TABLE [dbo].[EmployeeFTS](
[EmployeeID] [int] NOT NULL,
[EmployerID] [int] NOT NULL,
[Names] [nvarchar](150) NOT NULL,
[PhoneNumbers] [nvarchar](max) NOT NULL,
[Address] [nvarchar](250) NOT NULL,
[SearchText] AS ((((([Names]+'; '))+([PhoneNumbers]+'; '))+([Address]))+((('|EmployerId:'+CONVERT([varchar](100),[EmployerId],0))))),
CONSTRAINT [PK_EmployeeFTS] PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC
)
)
GO
CREATE NONCLUSTERED INDEX [EmployeeFTS_EmployerId]
ON [dbo].[EmployeeFTS] ([EmployerID])
INCLUDE ([EmployeeID])
GO
CREATE FULLTEXT CATALOG [FT_TEST_EmployeeFT] WITH ACCENT_SENSITIVITY = ON
GO
CREATE FULLTEXT STOPLIST [StopList_EmployeeFTS]
;
CREATE FULLTEXT INDEX ON [dbo].[EmployeeFTS] KEY INDEX [PK_EmployeeFTS] ON ([FT_TEST_EmployeeFT], FILEGROUP [PRIMARY]) WITH (CHANGE_TRACKING AUTO, STOPLIST [StopList_EmployeeFTS])
GO
ALTER FULLTEXT INDEX ON [dbo].[EmployeeFTS] ADD ([SearchText] LANGUAGE [English])
GO
ALTER FULLTEXT INDEX ON [dbo].[EmployeeFTS] ENABLE
GO
truncate table [EmployeeFTS]
GO
declare @i as int = 0
declare @iStr as varchar(10)
WHILE @i < 100000
BEGIN
set @iStr = cast(@i as varchar(10))
insert into [EmployeeFTS] (EmployeeId, EmployerId, Names, PhoneNumbers, [Address])
VALUES(@i, @i %100, 'NAme ' + @iStr, 'PhoneNumber ' + @iStr, 'Address ' + @iStr)
set @i = @i + 1
END
GO
SET STATISTICS IO ON
GO
declare @EmployerId as int = 2
declare @query as varchar(4000)
set @query = '(FORMSOF(INFLECTIONAL, NAme)) AND (FORMSOF(INFLECTIONAL, address))'
print @query
SELECT TOP 200 c.EmployerId, c.EmployeeId
FROM dbo.EmployeeFTS c
INNER JOIN CONTAINSTABLE
(
dbo.EmployeeFTS
,SearchText
,@query
) results
ON c.EmployeeId = results.[Key]
WHERE
c.EmployerId= @EmployerId
Order by results.Rank DESC
GO
declare @EmployerId as int = 2
declare @query as varchar(4000)
set @query = '(FORMSOF(INFLECTIONAL, NAme)) AND (FORMSOF(INFLECTIONAL, address))'
set @query = '\|EmployerId:' + cast(@EmployerId as varchar(10)) + ' AND ' + @query
print @query
SELECT c.EmployerId, c.EmployeeId
FROM dbo.EmployeeFTS c
INNER JOIN CONTAINSTABLE
(
dbo.EmployeeFTS
,SearchText
,@query
,200
) results
ON c.EmployeeId= results.[Key]
WHERE
c.EmployerId= @EmployerId