the clustered pk index not being used with pk search

have update statement like:

declare
@p1 bigint,
@p2 varchar(8000),
...
@p30 bigint


update mytable
set
col1=@p1,
col2=@p2 ,
..
table_id=@p30

(this query extracted trace file)

table_id pk of table. however, execution plan not using index seek on clustered pk index. instead, it's doing "range scan" on separate index on table "p30" variable.

if change bind variable "p30" type "int", "numeric(18,0)", or "decimal(18,0)", worked fine pk index, except "bigint". table_id column type "numeric(18,0)". if change column type "bigint", worked. it's problem of bigint bind variable vs. numeric(18,0) column type.

strange thing tried same statement on different sqlserver instance , pk index used , not able see difference between 2 db instance in terms of data or setup. must of db instance caused difference.

has ideas of caused problem?

in advance.
j

i able reproduce behavior on sql server 2000, not on sql server 2005, maybe has been improved in sql server 2005.  in case, should avoid implicit data type conversions, , choose data types insert values match data types of table columns.  "cause" of problem mismatched data types, say.

steve kass
drew university

SQL Server  >  SQL Server Database Engine