Strored procedure returns different row counts

Question

Strored procedure returns different row counts

Alex koranteng

SSC Veteran

Points: 228
More actions
January 27, 2013 at 6:21 am

#149686

I have a Select statement based on few joins. When I run it on my development server and test development server, I am getting different rowcounts. I have looked at each table from the two environments in the join clause and they all have records in them. Some of the columns in the table have null values. Any clues/suggestions why this is occurring will be great
Thanks

Viewing 9 posts - 1 through 8 (of 8 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

Alex koranteng (1/27/2013)
I have a Select statement based on few joins. When I run it on my development server and test development server, I am getting different rowcounts. I have looked at each table from the two environments in the join clause and they all have records in them. Some of the columns in the table have null values. Any clues/suggestions why this is occurring will be great
Thanks

its a bit difficult to help you....I cant see what you can see....perhaps if you gave us the code for your proc and some example data and expected results...we may be able to help

if you are unsure how to provide this...please post back...I assume you are using SQL 2005 ?

regards

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

Alex koranteng SSC Veteran Points: 228 More actions · Answer 2

Thanks for the prompt response. It will hard for me to send you my script and data because of privacy concerns of my company. I just need some high level reasons why I am getting this lkind of behaviour.

The sproc is giving different row counts between the development and test servers.

Thanks

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

my first thought is that the data in the tables is different between servers...but I would have thought you had already checked this?

________________________________________________________________
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 4

Alex koranteng (1/27/2013)
The sproc is giving different row counts between the development and test servers.

The data is different or the code is different. Can't really say much more without more details

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

Alex koranteng SSC Veteran Points: 228 More actions · Answer 5

Thanks for the pointer. The code is the same. The data for the joins in the two environments are slightly different, because the test server data is a restore of production data, but most of the tables in the two environments have similar record count. Could it be due to the environment settings between the two environments.

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

Alex koranteng (1/27/2013)
Thanks for the pointer. The code is the same. The data for the joins in the two environments are slightly different, because the test server data is a restore of production data, but most of the tables in the two environments have similar record count. Could it be due to the environment settings between the two environments.

ok..so the code is the same but the data is "slightly different"......in which case I would expect to get different results....why are you expecting the same results?

without more details sample code and sample data that demonstrates your problem, I dont see how I can help.

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

Jason Wolfkill SSCrazy Eights Points: 9832 More actions · Answer 7

Alex koranteng (1/27/2013)
Thanks for the pointer. The code is the same. The data for the joins in the two environments are slightly different, because the test server data is a restore of production data, but most of the tables in the two environments have similar record count. Could it be due to the environment settings between the two environments.

You mentioned in your OP that some of the columns in the tables involved have NULL values in them. If you are joining or filtering on any of those columns, different ANSI_NULLS settings could return different results.

Of course, as others have mentioned, trying to diagnose this problem without ensuring that the data is IDENTICAL (not "similar") in both environments will open up many rabbit holes that have nothing to do with the actual cause of the different results. For your own sake, don't waste time like that. If you can't make the data identical in each environment, first spend your time breaking down the query and examining the underlying data to be sure that differences in the data aren't leading to different (but correct for each environment) query results.

Jason Wolfkill

Alex koranteng SSC Veteran Points: 228 More actions · Answer 8

Alex koranteng

SSC Veteran

Points: 228

January 28, 2013 at 8:26 pm

#1581872

Thanks for the pointer