Repro scripts for dba.se-347554.sql

dba.se-347554.sql

-- Why does SQL Server Full Text Search (FTS) rank go down when match count goes up due to highly rare word
-- https://dba.stackexchange.com/q/347554/68127
use [master];
go
-- exec sp_WhoIsActive @show_system_spids = 1;

drop database if exists fts;
go

create database fts;
go

use fts;
go

create fulltext catalog QuickSearchFullTextCatalog
    as default;
go

create table QuickSearchProducts_Temp (
    RowId                 int           not null
        -- identity(1, 1)
        constraint PK_QSP primary key,
    ObjectId              bigint        not null,
    BrandId               smallint      not null
        default 0,
    OrderNumber           nvarchar(200) not null,
    IsActive              bit           not null,
    SearchableDescription nvarchar(1000)
);
go

create fulltext index
    on QuickSearchProducts_Temp (
        SearchableDescription
    )
    key index PK_QSP
    on QuickSearchFullTextCatalog
    with change_tracking auto
;
go

/*
insert into QuickSearchProducts_Temp (
    RowId, BrandId, ObjectId, IsActive, OrderNumber, SearchableDescription
)
values
    (68348, 4,      3682038,  1,        6403072,     N'15301530 6403072 Deep Face for _ Grooving IC228 Inserts PICCO PICCO015 R_'),
    (68349, 4,      3682039,  1,        6495543,     N'15301520 6495543 Deep Face for _ Grooving IC288 Inserts L_PICCO PICCO015'),
    (71254, 4,      3684944,  1,        6403059,     N'55� 5554815 6403059 for _ IC228 Inserts Int Internal PICCO PICCO55�Thread Profile R_ Thread Threading'),
    (71255, 4,      3684945,  1,        6403060,     N'55� 6403060 6554815 for _ IC228 Inserts Int Internal PICCO PICCO55�Thread Profile R_ Thread Threading'),
    (71256, 4,      3684946,  1,        6403061,     N'55� 6403061 6552415 for _ IC228 Inserts Int Internal PICCO PICCO55�Thread Profile R_ Thread Threading'),
    (71257, 4,      3684947,  1,        6403062,     N'55� 6403062 7552415 for _ IC228 Inserts Int Internal PICCO PICCO55�Thread Profile R_ Thread Threading'),
    (86502, 4,      3700392,  1,        6411154,     N'2007 6020022 6411154 and_ for _ Groove Grooving IC228 Inserts Int Internal PICCO R_ RL Turn Turning')
;
go
*/
go
truncate table QuickSearchProducts_Temp
go
insert into QuickSearchProducts_Temp (
    RowId,
    ObjectId,
    BrandId,
    OrderNumber,
    IsActive,
    SearchableDescription
)
select
    gs.[value] as RowId,
    gs.[value] + 10000 as ObjectId,
    gs.[value] % 5 as BrandId,
    iif(gs.[value] = 0, '6403072', convert(varchar(30), 10000 - gs.[value])) as OrderNumber,
    1 as IsActive,
    case 
        when gs.[value] = 0    then '15301530 Picco Ic228 bing bang boom'
        when gs.[value] <= 410 then 'Picco Ic228 bing bang boom'
        when gs.[value] <= 959 then 'Picco bing bang boom' 
        else 'lotta other rows ' + convert(varchar(10),gs.[value])
        --else convert(varchar(36),newid())
    end as SearchableDescription
from generate_series(0,3500) as gs 
/*
insert into QuickSearchProducts_Temp (
    RowId,
    ObjectId,
    BrandId,
    OrderNumber,
    IsActive,
    SearchableDescription
)
select
    gs.[value] as RowId,
    gs.[value] + 10000 as ObjectId,
    gs.[value] % 5 as BrandId,
    iif(gs.[value] = 0, '6403072', convert(varchar(30), 10000 - gs.[value])) as OrderNumber,
    1 as IsActive,
    'lotta other rows ' + convert(varchar(10),gs.[value]) as SearchableDescription
    --concat(
    --    gs.[value],
    --    '_',
    --    newid()
    --) as SearchableDescription
from generate_series(150001,200000) as gs 
*/
;;
--delete from dbo.QuickSearchProducts_Temp
--where RowId > 100000

select count(*) from QuickSearchProducts_Temp

declare 
    @aboutPredicateOpt nvarchar(4000) = N'IsAbout(
        PICCO weight(0.1),
        IC228 weight(0.1) 
    )'
;

select
    qsp.RowId,
    qsp.BrandId,
    qsp.ObjectId,
    qsp.IsActive,
    qsp.OrderNumber,
    qsp.SearchableDescription,
    ct.[RANK]
from QuickSearchProducts_Temp as qsp
join containstable (
    QuickSearchProducts_Temp, 
    SearchableDescription, 
    @aboutPredicateOpt
) as ct on ct.[KEY] = qsp.RowId
where qsp.IsActive = 1
order by 
    iif(qsp.OrderNumber in ( '6403072' ), 0, 1),
    ct.[RANK] desc
;
go

declare
  @aboutPredicateOpt nvarchar(4000) = 
      N'IsAbout(        
          15301530 weight(0.1)  
      )';  
select
    qsp.RowId,
    qsp.BrandId,
    qsp.ObjectId,
    qsp.IsActive,
    qsp.OrderNumber,
    qsp.SearchableDescription,
    ct.[RANK]
from QuickSearchProducts_Temp as qsp
join containstable (
    QuickSearchProducts_Temp, 
    SearchableDescription, 
    @aboutPredicateOpt
) as ct on ct.[KEY] = qsp.RowId
where qsp.IsActive = 1
order by 
    iif(qsp.OrderNumber in ( '6403072' ), 0, 1),
    ct.[RANK] desc
go


declare 
    @aboutPredicateOpt nvarchar(4000) = N'IsAbout(
        15301530 weight(0.1),
        PICCO weight(0.1),
        IC228 weight(0.1)  
    )'
;

select
    qsp.RowId,
    qsp.BrandId,
    qsp.ObjectId,
    qsp.IsActive,
    qsp.OrderNumber,
    qsp.SearchableDescription,
    ct.[RANK]
from QuickSearchProducts_Temp as qsp
join containstable (
    QuickSearchProducts_Temp, 
    SearchableDescription, 
    @aboutPredicateOpt
) as ct on ct.[KEY] = qsp.RowId
where qsp.IsActive = 1
order by 
    iif(qsp.OrderNumber in ( '6403072' ), 0, 1),
    ct.[RANK] desc
;
go


--select top 10 * from dbo.QuickSearchProducts_Temp order by RowId desc

select 
    ik.keyword,
    ik.display_term,
    ik.column_id,
    ik.document_count 
from sys.dm_fts_index_keywords(
    db_id(),
    object_id(N'QuickSearchProducts_Temp')
) as ik
where --ik.document_count > 1 or 
    ik.display_term in (
        N'15301530',
        N'picco',
        N'ic228',
        'END OF FILE'
    )
;

select 
    (3501./1.  )/3501. as [15301530],
    (3501./411.)/3501. as [ic228],
    (3501./960.)/3501. as [picco]
;

select 
    (5001./1.  )/5001. as [15301530],
    (5001./411.)/5001. as [ic228],
    (5001./960.)/5001. as [picco]
;

--select * from sys.dm_fts_index_keywords(db_id(),object_id('QuickSearchProducts_Temp')) as ikorder by ik.display_term;


declare 
    @aboutPredicateOpt nvarchar(4000) = N'IsAbout(
        15301530 weight(1.0),
        PICCO weight(0.002),
        IC228 weight(0.001)  
    )'
;

/*
Msg 7632, Level 15, State 5, Line 233
The value of the Weight argument must be between 0.0 and 1.0.
*/

select
    qsp.RowId,
    qsp.BrandId,
    qsp.ObjectId,
    qsp.IsActive,
    qsp.OrderNumber,
    qsp.SearchableDescription,
    ct.[RANK]
from QuickSearchProducts_Temp as qsp
join containstable (
    QuickSearchProducts_Temp, 
    SearchableDescription, 
    N'IsAbout(
        15301530 weight(1.0),
        PICCO weight(0.002),
        IC228 weight(0.001)  
    )'
) as ct on ct.[KEY] = qsp.RowId
where qsp.IsActive = 1
order by 
    iif(qsp.OrderNumber in ( '6403072' ), 0, 1),
    ct.[RANK] desc
;
go