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 calculate scalar and constant scan.
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 MSSQL 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 MS SSMS 2016 (13.0.16100.1) and SQL Server 2014 Expres Edition 64bit on Windows 7, but I guess it is a general behavior.
Best Answer
Shows you that the literal
2147483648
is interpreted asnumeric(10,0)
. This behaviour pre-dates the introduction of thebigint
in SQL Server (2000).There is no syntax to indicate that a literal should be treated as
bigint
- adding an explicitCAST
is the best solution. The article Dynamic Seeks and Hidden Implicit Conversions discusses the rest of the apparatus in the plan.The plan itself shows that the nested loops has a seek predicate on
You can use an extended events session on
query_trace_column_values
to see that these are as follows.The XML in the plan also shows this
This does not mean that it is literally doing a comparison
< null
ratherSo the net effect is that your query predicate of
b.col1 > CAST(2147483648 AS NUMERIC(10, 0))
still ends up with a seek againstb.col1 > CAST(2147483648 AS BIGINT)
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.