4

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:

Implicit_Conversion

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?

  • I don't think you will notice 'slow performance' by a single scalar conversion, but of course if you can use the correct type, why not do it. – eckes Aug 25 '17 at 2:20
8

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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.