sql server - SQL not properly recognizing and honoring the WHERE statement -

i have 2 tables.

declare @definitions table ( fdefid varchar(8)   ,   dtype  varchar(max) ,   fname  varchar(max) )  insert @definitions values('8c7eab0e','string','custpartno') insert @definitions values('8c7eab02','int'   ,'itemno'    )  declare @fields table ( rowid varchar(8)   ,   fkfid varchar(8)   ,   adj   varchar(max) )  insert @fields values('83ede211','8c7eab0e','89319971151801') insert @fields values('83ede211','8c7eab02','1'             ) 

i trying find records in @fields value not int , related record in @definitions table has dtype value of 'int'.

when try list of results (if any), tried these statements:

select f.rowid ,        f.fkfid ,        f.adj   ,        d.fname   @fields      f   join @definitions d on  f.fkfid = d.fdefid                       , d.dtype = 'int'   isnumeric( adj        ) <> 1      or cast(      adj int ) <> adj  select * ( select f.rowid ,               f.fkfid ,               f.adj   ,               d.fname        @fields      f        join @definitions d on  f.fkfid = d.fdefid                            , d.dtype='int'      ) isnumeric( adj        ) <> 1    or cast(      adj int ) <> adj 

and got error both:

the conversion of varchar value '89319971151801' overflowed int column. 

however, when first store values in table variable this:

declare @temp table ( rowid    varchar(8),   fdefid   varchar(8),   adjusted varchar(max) )  insert @temp select f.rowid ,        f.fkfid ,        f.adj @fields      f join @definitions d on  f.fkfid = d.fdefid                     , d.dtype = 'int'  select * @temp isnumeric( adjusted        ) <> 1    or cast(      adjusted int ) <> adjusted 

i expected results (no records in example).

if remove where clause these results:

the row large field isn't there, why cause error after adding where?

i can avoid issue casting bigint instead of int, why matter since join , where clauses remove value not int beginning?

you know sql standard has no requirements mandating specific order of expression evaluation or short-circuiting of expression evaluation? query optimizer free rearrange pretty entire query long semantic meaning (not intent) maintained.

your second stab @ things, pre-filtering on datetype = 'int' , loading filtered results works because forces order of operation on query processor.

your single-query tries fail because expression

cast( adj int ) <> adj 

which

• takes varchar
• converts int
• converts int varchar, , finally...
• compars varchar (containing normalized/canonical string representation of int) original varchar value.

fails. fails because query processor must evaluate expression every candidate row, regardless of whether or not column datatype contains int or not.

the second single-query attempt, derived table in from clause fails because optimizer smart enough see query can refactored not use derived table.

the real problem, of course, have denormalized database design , you're overloading meaning (and data type) of column adj, trying make single table multiple things.