Predict output

Question

Post reply

Predict output

arup chakraborty

Hall of Fame

Points: 3640
More actions
October 13, 2008 at 9:04 pm

#199758

Comments posted to this topic are about the item Predict output

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

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

premindthomas SSC Rookie Points: 28 More actions · Answer 1

Hi Dudes

The question is really amazing.Nice ideas.

Waiting for amazing questions.

Carlo Romagnano SSC-Insane Points: 22597 More actions · Answer 2

Without the ORDER BY clause, you can not predict the order of rows.

NULL, 1 and 1, NULL are both possible.

In the future release of sqlserver or in another DBMS, the order may be different.

Robert-378556 SSCertifiable Points: 5917 More actions · Answer 3

UNION ALL simply concats the results, UNION removes the duplicates, the best way to do it is to sort the result, so NULLs come first.

Try this:

SELECT 3 UNION SELECT '2' UNION SELECT NULL

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 4

Hi Carlo,

I have tested it several times in SQL Server 2000 and 2005. Every time, it gives NULL first. I guess that it shows the NULL first because NULL has no values. Then it shows the values.

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

Null comes first because SQL has to do a sort behind the scenes to ensure uniqueness (required by union). Hence there's an implicit Order by 1 ASC on the statement. When you do an order by, null comes before values.

There's no guarantee that the behaviour will be the same in future versions though.

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

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 6

arup_kc (10/14/2008)
Hi Carlo,
I have tested it several times in SQL Server 2000 and 2005. Every time, it gives NULL first. I guess that it shows the NULL first because NULL has no values. Then it shows the values.

Hi Arup_kc,

It's great that testing shows consistent results, but that doesn't make this the "only" correct answer. A query without ORDER BY returns a relational set, which (by the very definitions of the relational model) has no inherent order. Limitations of the clients force some order upon that set, but unless an ORDER BY clause is specified, that order is indetermined and prone to change at SQL Server's whim.

Undocumented behaviour, even if confirmed by repeated testing, should never be relied on. I thought that after the "GROUP BY no longer implies sorting" change back in 6.5 and the "TOP 100 PERCENT ... ORDER BY in views no longer orders the view" change in 2005, that should be clear by now!

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Carlo Romagnano SSC-Insane Points: 22597 More actions · Answer 7

GilaMonster (10/14/2008)
Hence there's an implicit Order by 1 ASC on the statement.

Also the DISTINCT clause and the GROUP BY perform an implicit ORDER BY for all cols, but "IMPLICIT" is not the rule nor is written in the BOL.

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

Carlo Romagnano (10/14/2008)
Also the DISTINCT clause and the GROUP BY perform an implicit ORDER BY for all cols, but "IMPLICIT" is not the rule nor is written in the BOL.

I'm fully aware of that. I was trying to explain why, in this case, on the current versions of SQL, the null always comes first.

Oh, and if you look at the exec plan for this query, SQL's actually not doing a sort at all.

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

Anipaul SSC-Insane Points: 24681 More actions · Answer 9

Nice and wonderful questions ...:)

Arup great effort

Tom Moreau SSCrazy Points: 2125 More actions · Answer 10

As others have stated, only an ORDER BY guarantees the sort order of the results. Therefore, there are 2 correct answers.

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 11

Hi Anirban,

Many thanks. I will try to dig more questions from the SQL mine.

Jack Corbett SSC Guru Points: 184393 More actions · Answer 12

Tom Moreau (10/14/2008)
As others have stated, only an ORDER BY guarantees the sort order of the results. Therefore, there are 2 correct answers.

As Gail explained, in the current version(s) of SQL Server available there is an implicit sort being done, so the NULL will come first. I got it wrong, and realized it just as I clicked submit. Oh well, too late, no points.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

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

The question is do you want the theoretical result (where NULL, 1 and 1, NULL are both right) or the actual result that the current versions of SQL give for that specific query.

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

prem_ignou_2003 Grasshopper Points: 20 More actions · Answer 14

In this quiry using Union - The union don't allow the duplicate.

The Null have low weight