Why does this seek on BIGINT col have extra constant scan, compute scalar, and nested loops operators?

sql-server sql-server-2014 add tag

eckes (imported from SE)

When I look at the actual exection plan of some of my queries I notice that literal constants used in a `WHERE` clause show up as a nested chain of *compute scalar* and *constant scan*.

[![sql studio screenshot][1]][1]

To reproduce this, I use the following table

    CREATE TABLE Table1 (
	    [col1] [bigint] NOT NULL,
	    [col2] [varchar](50) NULL,
	    [col3] [char](200) NULL
    )
    CREATE NONCLUSTERED INDEX IX_Table1 ON Table1 (col1 ASC)

With some data in it:

    INSERT INTO Table1(col1) VALUES (1),(2),(3),
                                   (-9223372036854775808),
                                   (9223372036854775807),
                                   (2147483647),(-2147483648)

When I run the following (nonsense) query:

    SELECT a.col1, a.col2
      FROM Table1 a, Table1 b
      WHERE b.col1 > 2147483648

I see that it will do a Nested Loop drawing in the result of Index Seek and a scalar calculation (from a constant).

Note that the literal is larger than maxint. It does help to write `CAST(2147483648 as BIGINT)`. Any idea why SQL Server is defrering that to the execution plan and is there a shorter way to avoid it than using the cast? Does it affect bound parameters to prepared statements (from jtds JDBC) as well?

The scalar calculation is not always done (seems to be *index seek* specific). And sometimes the query analyser does not show it graphically but as `col1 < scalar(expr1000)` in the predicate properties.

*I have seen this with SSMS 2016 (13.0.16100.1) and SQL Server 2014 Express Edition 64bit on Windows 7, but I guess it is a general behavior.*

  [1]: https://i.stack.imgur.com/0uaGH.png

Top Answer

Martin Smith (imported from SE)

    SELECT thing, 
           sql_variant_property(thing,'basetype') AS basetype,
           sql_variant_property(thing,'precision') AS precision, 
           sql_variant_property(thing,'scale') AS scale
    FROM (VALUES (2147483648)) V(thing)

Shows you that the literal `2147483648` is interpreted as `numeric(10,0)`. This behaviour pre-dates the introduction of the `bigint` in SQL Server (2000).

There is no syntax to indicate that a literal should be treated as `bigint` - adding an explicit `CAST` is the best solution. The article [Dynamic Seeks and Hidden Implicit Conversions][3] discusses the rest of the apparatus in the plan.

The plan itself shows that the nested loops has a seek predicate on

```none
Seek Keys[1]:
    Start: [tempdb].[dbo].[Table1].col1 > Scalar Operator([Expr1005]), 
    End: [tempdb].[dbo].[Table1].col1 < Scalar Operator([Expr1006])
```

You can use an extended events  session on [`query_trace_column_values`][1] to see that these are as follows.

[![enter image description here][2]][2]

The XML in the plan also shows this

```xml
<DefinedValue>
    <ValueVector>
        <ColumnReference Column="Expr1005" />
        <ColumnReference Column="Expr1006" />
        <ColumnReference Column="Expr1004" />
    </ValueVector>
    <ScalarOperator ScalarString="GetRangeWithMismatchedTypes((2147483648.),NULL,(6))">
        <Intrinsic FunctionName="GetRangeWithMismatchedTypes">
        <ScalarOperator>
            <Const ConstValue="(2147483648.)" />
        </ScalarOperator>
        <ScalarOperator>
            <Const ConstValue="NULL" />
        </ScalarOperator>
        <ScalarOperator>
            <Const ConstValue="(6)" />
        </ScalarOperator>
        </Intrinsic>
    </ScalarOperator>
</DefinedValue>
```

This does not mean that it is literally doing a comparison `< null` rather 

>  The range boundary expressions use NULL to represent 'unbounded' at either end. ([Source][4])

So the net effect is that your query predicate of `b.col1 > CAST(2147483648 AS NUMERIC(10, 0))` still ends up with a seek against `b.col1 > CAST(2147483648 AS BIGINT)`

>  Does it affect bound parameters to prepared statements (from jtds JDBC) as well?

I haven't used jtds JDBC but I presume it allows you to define parameter datatypes? If so just make sure the parameters are the correct datatype that match the column (`bigint`) so there's no need for SQL Server to deal with mismatched datatypes.

  [1]: http://www.queryprocessor.com/query-trace-column-values/
  [2]: https://i.stack.imgur.com/UJd9B.png
  [3]: https://sql.kiwi/2012/01/dynamic-seeks-and-hidden-implicit-conversions.html
  [4]: https://topanswers.xyz/databases?q=437#a446

Answer #2

eckes (imported from SE)

In relation to my question about JDBC prepared statements. [jTDS][1] uses `sp_prepare`/`sp_execute` (in default `prepareSQL=3` mode).

With the following query ([source][2]):

    use database
    select
        cp.objtype, st.text
    from sys.dm_exec_cached_plans cp
    cross apply sys.dm_exec_sql_text(cp.plan_handle) st
    where cp.objtype = 'prepared' and st.text like '%TABLE%'

I can see the prepared statement as issued by JTDS, and it does declare the variable as `(@P0 bigint)...` as expected.

So this is all good and I need to remember that when trying out the execution plans that it is better to actually define local typed variables instead of replacing them with literals (and/or using `sp_execute` to tap into the cached execution plan).

  [1]: http://jtds.sourceforge.net/faq.html
  [2]: https://dba.stackexchange.com/questions/21385/list-prepared-statements

2 Answers