SQL Server Implicit Conversion – Handling Implicit Conversion in FETCH

offset-fetchpagingsql serversql server 2014type conversion

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?

Best Answer

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.

Related Solutions

Sql-server – fast way to change column type without dropping the clustered index

To change datatype, you have to drop and recreate the Index as below (alternatively you can use SSMS, which does similar thing behind the scenes) :

drop any foreign keys in other tables that references this index.
drop the index
change the column datatype
rebuild the index
Put back any foreign keys that you dropped in step 1.

Alternatively, you can wrap below in a transaction with TABLE_LOCK so that no one can insert/update/delete while the change is occurring --

Create another table with Name_staging and columns with correct datatype
create Indexes, FK, etc
Insert the data into the Name_staging table
Rename the Name_staging to Original table.

Note: Its best to perform above task in maintenance window.

SQL Server – How to Avoid Implicit Conversion for Integer Column

How to avoid implicit conversion for an Integer column

It is the parameter that has been implicitly converted, not the column.

The query has been subject to Simple Parameterization by SQL Server. You have no control over the datatypes used in this process. It uses the smallest datatype that can hold the literal value (5 can fit into a tinyint). The implicit cast of a tinyint parameter to an int won't cause any problems.

However to avoid having multiple plans in cache for int, smallint, tinyint and get rid of the implicit cast you could explicitly parameterize the query yourself - with a parameter of datatype int rather than having it be parameterized automatically.

EXEC sys.sp_executesql
  N'SELECT [BusinessEntityID],[NationalIDNumber],[LoginID],[OrganizationNode]
  FROM [AdventureWorks2014].[HumanResources].[Employee]
  where BusinessEntityID = @BusinessEntityID',
  N'@BusinessEntityID INT',
  @BusinessEntityID = 5;

One other alternative would be to block simple parameterisation by adding a redundant AND 1=1 as below. But I don't recommend this as then you will get plans compiled and cached for every different literal value that you pass.

SELECT [BusinessEntityID],
       [NationalIDNumber],
       [LoginID],
       [OrganizationNode]
FROM [AdventureWorks2014].[HumanResources].[Employee]
where BusinessEntityID = 5 
      AND 1=1

Best Answer

Arguments

Related Solutions

Sql-server – fast way to change column type without dropping the clustered index

SQL Server – How to Avoid Implicit Conversion for Integer Column

Related Question