Different result for one query

Question

Different result for one query

Virendra Yaduvanshi

SSC Rookie

Points: 44
More actions
August 7, 2014 at 7:29 am

#297629

Hi,
Today i got a very surprising issue with SQL 2008, my one team member inform that a single query is resulting two different data set from two diff-diff SSMS's query windows.
Query is like this,
Select * from table1 where trandate between '2014-08-02 00:00:00.000' and '2014-08-03 23:59:59.999'
in one query window its showing 295 records while on same desktop in second query window its showing 602 records.
Please let me know where is problem. 🙂
Thanking you.
Virendra Yaduvanshi
Principal DBA,
MCTS, MCITP
http://wikidba.wordpress.com

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

what have you checked already?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Is it repeatable

Are they connecting to the same instance, same database

Are they using nolock/read uncommitted?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Virendra Yaduvanshi SSC Rookie Points: 44 More actions · Answer 3

Hi Sir,

Thanks a lot for ur reply,

query is used with nolock, but same query if execute from 2 qry windows, its counts r different.

Thanking you,

Virendra

Ant-Green SSC Guru Points: 113507 More actions · Answer 4

And if you remove the "NOLOCK" do you get consistent result?

Virendra Yaduvanshi SSC Rookie Points: 44 More actions · Answer 5

Yes,

in both case, with nolock and without nolock, problem is still same

Ant-Green SSC Guru Points: 113507 More actions · Answer 6

So with nolock and without nolock, you can get still get different results depending when they where executed.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

GilaMonster (8/7/2014)
Is it repeatable
Are they connecting to the same instance, same database
Are they using nolock/read uncommitted?

Repeating Gail's question:

Are they connecting to the same instance, same database?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Virendra Yaduvanshi (8/7/2014)
query is used with nolock

Then start by removing nolock, as it causes exactly this (duplicated rows). Same with read uncommitted isolation level.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ScottPletcher SSC Guru Points: 101103 More actions · Answer 9

A longshot, but, in the interests of being thorough ...

Are there possibly different "table1" tables under different schemas? Could one session be reading from a different schema.table1 than the other session?

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Virendra Yaduvanshi SSC Rookie Points: 44 More actions · Answer 10

No sir. its a single instance,single db,single schema.... as default.. no changes..

higgim SSCarpal Tunnel Points: 4090 More actions · Answer 11

By chance in one of the query windows are there any open transactions which have not yet been committed? (Just a guess which could be completely wrong)

Ignore the above 🙂