When running a query and specifically calling for a PaymentId, I see it, and when running it generally I cannot find it

Question

When running a query and specifically calling for a PaymentId, I see it, and when running it generally I cannot find it

Asaf Oren 1

Hi,
This is a very weird scenario.
When I'm running this, I see the PaymentId:
SELECT a.PaymentId,a.PaymentDate FROM DW_Staging..Payments_Gen2_Payoneer_iACHPayments_Full AS a
where a.FundsOriginType = 1
AND a.PaymentId = '322765709'
AND a.PaymentDate > '2021-08-30 16:03:00.017'
AND a.PaymentDate < '2021-08-30 16:03:20.017'
ORDER BY a.PaymentId

And when I'm running this I cannot:
SELECT a.PaymentId,a.PaymentDate FROM DW_Staging..Payments_Gen2_Payoneer_iACHPayments_Full AS a
where a.FundsOriginType = 1
--AND a.PaymentId = '322765709'
AND a.PaymentDate > '2021-08-30 16:03:00.017'
AND a.PaymentDate < '2021-08-30 16:03:20.017'
ORDER BY a.PaymentId

What can it be?

Ronen Ariely 15,216 Reputation points

2021-10-31T15:19:07.647+00:00

Hi Asaf,

We cannot read minds or work on your server in order to execute your queries.

Please provide queries to create the relevant table(s) and to insert some sample data.

In addition please provide the expected result according to that specific sample data (try to have rows which cover all cases you might have in your database)
Viorel 125.7K Reputation points

2021-10-31T16:19:45.297+00:00

Last time, the hardware was one of suspicions: https://social.msdn.microsoft.com/Forums/en-US/9c2a7883-f385-4961-a6c5-cf958faf8d7f.
EchoLiu-MSFT 14,626 Reputation points

2021-11-01T03:01:33.037+00:00

Could you please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)? So that we’ll get a right direction and make some test.

2 answers

Your answer

Ronen Ariely 15,216 Reputation points

2021-10-31T15:19:07.647+00:00

Hi Asaf,

We cannot read minds or work on your server in order to execute your queries.

Please provide queries to create the relevant table(s) and to insert some sample data.

In addition please provide the expected result according to that specific sample data (try to have rows which cover all cases you might have in your database)
Viorel 125.7K Reputation points

2021-10-31T16:19:45.297+00:00

Last time, the hardware was one of suspicions: https://social.msdn.microsoft.com/Forums/en-US/9c2a7883-f385-4961-a6c5-cf958faf8d7f.
EchoLiu-MSFT 14,626 Reputation points

2021-11-01T03:01:33.037+00:00

Could you please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)? So that we’ll get a right direction and make some test.

Answer 1

Erland Sommarskog 128.9K MVP Volunteer Moderator

A couple of possible explanations come to mind. The most likely is maybe corruption. That is, a value is present in one index, but not in another. Another possibility is a bug in SQL Server, so that the query plan for the latter query missing the the ID for some reason. I can think of a few more reasons, but they are more esoteric.

I would recommend that you start with running DBCC CHECKDB(DW_staging) to see what it says. Share the output from this command. Please also share the output of "SELECT @@version".

Answer 2

Tom Phillips 17,781

As Erland said, this is almost always due to a bad index. Refresh your indexes, and it will likely fix itself.

Also, make sure you have the most current patches installed. This problem is extremely rare, and is likely an issue with the version you have installed.

Share via

When running a query and specifically calling for a PaymentId, I see it, and when running it generally I cannot find it

2 answers

Your answer