GROUP BY in SQL Server 2014

Question

Post reply

GROUP BY in SQL Server 2014

zouzou

SSCrazy

Points: 2292
More actions
December 13, 2016 at 5:07 am

#319743

Hello all,
The same query with a group by clause I try to run on SQL Server 2005 and on SQl Server 2014 but it seems that every time i run the same thing on SQL server 2014 gives me a different output different results when i press F5. In SQL server 2005 its consistent with the results
Any ideas why this it ?
thank you so much in advance

Viewing 15 posts - 1 through 15 (of 41 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Unless you post the query, I'm just guessing. I imagine that you have different data in the tables on the two servers.

John

Phil Parkin SSC Guru Points: 246723 More actions · Answer 2

zouzou (12/13/2016)
Hello all,
The same query with a group by clause I try to run on SQL Server 2005 and on SQl Server 2014 but it seems that every time i run the same thing on SQL server 2014 gives me a different output different results when i press F5. In SQL server 2005 its consistent with the results
Any ideas why this it ?
thank you so much in advance

Please do what you can to give us some proof. There is no known 'random results mode' for the GROUP BY statement.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 3

zouzou (12/13/2016)
Hello all,
The same query with a group by clause I try to run on SQL Server 2005 and on SQl Server 2014 but it seems that every time i run the same thing on SQL server 2014 gives me a different output different results when i press F5. In SQL server 2005 its consistent with the results
Any ideas why this it ?
thank you so much in advance

Does the query have an order by clause? Given that the data is the same, if it doesn't have an order by clause, then the difference would most likely be the new cardinality estimator choosing a different plan than the earlier versions and since no order for the output is set then the results will be "in order of appearance"

😎

Can you post the actual execution plans for those queries?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

zouzou (12/13/2016)
Hello all,
The same query with a group by clause I try to run on SQL Server 2005 and on SQl Server 2014 but it seems that every time i run the same thing on SQL server 2014 gives me a different output different results when i press F5. In SQL server 2005 its consistent with the results
Any ideas why this it ?
thank you so much in advance

I have numerous queries which are designed specifically to generate different results every time they are executed, primarily for generating test data. None of them contain a GROUP BY. I'd be interested to see your query.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

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

Gut feel: there's a TOP without an ORDER BY.

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

GilaMonster (12/13/2016)
Gut feel: there's a TOP without an ORDER BY.

Good choice. There are other reasons, but I would go with this one as well.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

zouzou SSCrazy Points: 2292 More actions · Answer 7

SELECT

RTRIM(Ax) AS A,

RTRIM(Bx) AS B,

RTRIM(Zx) AS Z

FROM TEMP1 A

LEFT OUTER JOIN TEMP2 B on A.Zx = B.Zx

GROUP BY A,B,Z

Luis Cazares SSC Guru Points: 183698 More actions · Answer 8

zouzou (12/13/2016)
SELECT
RTRIM(X) AS X,
RTRIM(Y) AS Y,
RTRIM(Z) AS Z
FROM TABLE A
LEFT OUTER JOIN B on A.X= Y.Z
GROUP BY X,Y,Z

That query won't run.

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

zouzou SSCrazy Points: 2292 More actions · Answer 9

Well I know I made the mistake but this the query please forginve me if I put a wrong letter somewhere will send you in a moment the plans

Luis Cazares SSC Guru Points: 183698 More actions · Answer 10

zouzou (12/13/2016)
Well I know I made the mistake but this the query please forginve me if I put a wrong letter somewhere will send you in a moment the plans

There's nothing to forgive. You just need to post helpful information if you want helpful feedback.

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

zouzou SSCrazy Points: 2292 More actions · Answer 11

zouzou

SSCrazy

Points: 2292

December 13, 2016 at 8:28 am

#1917629

attached the estimated plans

Luis Cazares SSC Guru Points: 183698 More actions · Answer 12

You need to post the plan files, not just images. If you need to anonymize the names for privacy issues, you could use SentryOne Plan Explorer.

The pictures just don't reveal enough information.

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

zouzou SSCrazy Points: 2292 More actions · Answer 13

Was in a rush my mistake check now. Any ideas or help much appreciated

Luis Cazares SSC Guru Points: 183698 More actions · Answer 14

zouzou (12/13/2016)
Was in a rush my mistake check now. Any ideas or help much appreciated

If it's as simple as that, I would say that you're not getting different results, you're just getting your data set ordered in a different way due to the lack of an ORDER BY and the presence of parallelism only on one version.

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