Implicit conversion in fetch

Question

While studying different query plans to improve performance I noticed that FETCH is doing an implicit convert to bigint.

Example table and query:

CREATE TABLE checkPagintion
(
    Id INT NOT NULL PRIMARY KEY CLUSTERED,
    Name NVARCHAR(100)
 )

 DECLARE @paramPageNumber AS INT,
         @paramPageSize AS INT;

 SELECT *
 FROM checkPagintion
 ORDER BY Id
 OFFSET @paramPageNumber ROWS
 FETCH NEXT @paramPageSize ROWS ONLY

Execution plan for this query:

My question: should I use bigint for all pagination queries? If I use int will it a be a problem since implict_conversion is known for slow performance?

Answer 1

It's converting your variables, because bigint is what OFFSET ... FETCH is expecting for those values. It probably makes sense to

DECLARE @paramPageNumber AS BIGINT,
         @paramPageSize AS BIGINT;

to avoid any possible performance issues this may cause.

Your id column in your table, however, should still be fine as an int.

---

From the documentation for TOP (Transact-SQL):

-- Syntax for SQL Server and Azure SQL Database  

[   
    TOP (expression) [PERCENT]  
    [ WITH TIES ]  
]  

Arguments

expression
Is the numeric expression that specifies the number of rows to be returned. expression is implicitly converted to a float value if PERCENT is specified; otherwise, it is converted to bigint.

Answer 2

My question: should I use bigint for all pagination queries? If I use int will it a be a problem since implict_conversion is known for slow performance?

There is no need to use bigint for pagination.

And your example query has no need to any convert (but the plan is NOT for your posted query)

Implicit conversion can affect cardinality estimation and that can cause performance issues, but in your case (OFFSET..FETCH) it makes no difference since this particular conversion in TOP operator does not affect cardinality estimation