Issue With returned data linked between local database and a Linked Server Database

Question

Hello Forum

We have a bit of a weird issue. Essentially we are attempting to link data; one set is compiled as a Table Variable and the other is data on a linked Server.

On both Servers, we are linking the data with a simple inner join. Both columns are the same data type (int); both databases are using the same collation, both Servers are using the same collation.

The query is expected to return five rows BUT

SELECT ash.ApplicationStatusHistoryId,
ash.ApplicationID,
ash.IsCurrent,
ash.PrimaryStatusId,
ash.ApplicationStatusId,
ash.SetDate
FROM [SERVER].[DATABASE].[dbo].[ApplicationStatusHistory] ash
INNER JOIN @Karim152 a
ON
ash.ApplicationID = a.ApplicationID

Returns one row where as

SELECT ash.ApplicationStatusHistoryId,
ash.ApplicationID,
ash.IsCurrent,
ash.PrimaryStatusId,
ash.ApplicationStatusId,
ash.SetDate
FROM [SERVER].[DATABASE].[dbo].[ApplicationStatusHistory] ash
INNER JOIN @Karim152 a
ON
CAST(ash.ApplicationID AS INT) = a.ApplicationID--INT
returns the expected five rows.

When we pull the data across into a new table and run
SELECT ash.ApplicationStatusHistoryId,
ash.ApplicationID,
ash.IsCurrent,
ash.PrimaryStatusId,
ash.ApplicationStatusId,
ash.SetDate
FROM DBAScratchPad.[dbo].[ApplicationStatusHistory] ash

INNER JOIN @Karim152 a
ON
ash.ApplicationID = a.ApplicationID

This also returns five rows.

Answer 1

Both Servers are SQL Server with the same collation.

Main Server is 2017 Enterprise
Linked Server IS 2012 Enterprise SP4

Answer 2

My guess is ash.ApplicationID is a varchar with leading spaces. ie " 1" <> "1". Casting it to INT is doing a proper compare.