Does NEWID() disturb the ROW ORDER of RECORDS?

Question

Hi, This question is just for a sake of knowledge. I don't have any practical implementation of this.

I have fired a query like

SELECT RAND() RANDOMCOLUMNS,COL1

FROM tblY 

ORDER BY RANDOMCOLUMNS

Output:

RANDOMCOLUMNS        COL1
================================
0.567773034904758   1

0.567773034904758   2

0.567773034904758   3

0.567773034904758   4

When I fire the same query with NEWID(), I got the following output

RANDOMCOLUMNS                           COL1
===========================================
B953F9A9-BE22-4651-8669-5CC6A5269911    4

50293D75-4139-4A75-883F-81DAA91ED4E6    1

0A9B1D56-68FD-4060-AA6B-89791DAF8924    3

4C3A2765-4EA2-493B-8AA9-EBA414D7AAC2    2

As you can see that, COL1's values order has changed.

Question is why and how it happened?

As far asI know, NEWID() generates unique GUID. Does it have to do anything with Column ordering?

Thanks in advance

Answer 1

In the first example, you were sorting by RANDOMCOLUMNS. But because the same value was in each row, the order was not modified. In the second example, you're sorting by a column that has different values for each row. Sorting by GUID is strange in SQL Server, but it is still sorting by that column nonetheless, since it's in your ORDER BY clause.

Answer 2

• In query 1 you are sorting on the constant (per SELECT) RAND() value
• In query 2 you are sorting on a dynamic (per row) NEWID() value

In both queries, Col1 output order is irrelevant because you have not mentioned it. It is simply sheer coincidence that you get Col1 out in order in query 1 because of how the optimiser decided to read the data from the table.

There is no guaranteed output order unless an ORDER BY clause is used. You have not specified any sort on Col1, so this is standard behaviour.