sql-server - 使用 row_number 时全文索引查询变慢？

Question

table1有很大的行并且是field1文本，所以我创建了一个全文索引field1。

我运行下面的sql，速度很慢，CPU一直到100%。

select * from (
    select *, ROW_NUMBER() OVER(Order by [createtime] DESC) AS RowId 
    from table1
    where CONTAINS(field1, 'sometext')
) AS t1
where t1.RowId between 1 and 10

我删除RowId查询，它变得很快，不到 1 秒。

select * from (
    select *, ROW_NUMBER() OVER(Order by [createtime] DESC) AS RowId 
    from table1
    where CONTAINS(field1, 'sometext')
) AS t1

然后我认为这是关于 SQLServer 优化，我尝试添加zero到RowId查询字段。

它走得很快！但为什么？

select * from (
    select *, ROW_NUMBER() OVER(Order by [createtime] DESC) AS RowId 
    from table1
    where CONTAINS(field1, 'sometext')
) AS t1
where t1.RowId + 0 between 1 and 10

标签： sql-serverfull-text-searchrow-numbersql-optimization