-2

There is a column in my table, where all the values in a particular column are null. Now when I sort by this column ASC and DESC, I should get the Same result as per my assumption. But I'm getting different results. I can't understand the reason behind this strange behavior. If I remove a clustered index which I have made on this table, it gives expected result, but by preserving that index it doesn't. The index has nothing to do with this column.

The column will have values in future, meanwhile this behavior is confusing while sorting.

Can anyone tell why it happens? and how to get rid of this?

closed as unclear what you're asking by Erik Darling, Max Vernon, mustaccio, hot2use, MDCCL May 24 '18 at 14:58

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 4
    Maybe you could better define what behavior you want in this case. Show the table structure, some sample data, and explain how you want the data ordered (and why it should be ordered that way). – Aaron Bertrand May 24 '18 at 11:46
  • You didn't state what your assumption is._"Now when I sort by this column ASC and DESC, I should get the Same result as per my assumption."_ – hot2use May 25 '18 at 5:35
8

If you only order by that column, then SQL Server has nothing to order, so it's as if you didn't add an ORDER BY clause at all. A table has no inherent order if you don't explicitly ask for one (or if you ask for one that is ineffective), so it's unclear what behavior you expect when you say "get rid of this." If SQL Server can't sort the data because there's nothing to sort, it's going to return the data to you in the most efficient way possible. This may change over time due to a whole slew of factors.

Think about what you're asking SQL Server to do. You're saying sort by these values, but those values are all the same. What order do you expect if all of the values are 1, or 'Bob', or 2018-05-24? Same thing, SQL Server lines all the values up, tries to rearrange them, when it finds that they're all the same, it has nothing to do. So it returns the rows potentially without bothering to sort at all, or it may arbitrarily be in the order of an index that could have been used if one exists, or by the clustered index. Like future changes to the data might make the ORDER BY function differently, changes to indexes might make the current arbitrary and undefined order behave differently.

If I have a closet full of plain, navy blue T-shirts, and I asked you to rearrange my closet according to color, how much work are you willing to do? Do you think you need to do any work at all? You're just going to shrug and say "done." Maybe there are numbers I wrote on the tags with a Sharpie that dictate the rank based on who knows what, but if I don't tell you that those numbers are there, I shouldn't expect you to know about them, never mind sort by them.

If you expect a certain order for any other column when the first column in the ORDER BY is always the same value, add the other column(s) to the ORDER BY. Then they can be used to break ties.

If you expect a specific order that affects how the rows are sorted based on other columns, you need to ask for it by including those other columns in your ORDER BY.

5

SQL Server, at a high level, has the following methods of accessing indexes:

  • Allocation Order
  • Index Order (clustered, covering nonclustered)
  • Index Unordered (clustered, covering nonclustered)

I'm leaving non-covering nonclustered indexes that may require lookups out.

With a Heap

Which is a table with no clustered index, the most common to see is an Allocation Order scan. That is, a scan that reads pages and values in the order they were inserted to the table.

CREATE TABLE #el_heapo (id INT, yourmom INT)

INSERT #el_heapo ( id )
VALUES ( 1 )
INSERT #el_heapo ( id )
VALUES ( 2 )
INSERT #el_heapo ( id )
VALUES ( 3 )
INSERT #el_heapo ( id )
VALUES ( 4 )
INSERT #el_heapo ( id )
VALUES ( 5 )

If I query this table, I'll get five rows back "in order", but only because of the way the data was scanned.

SELECT *
FROM #el_heapo AS eh

+----+---------+
| id | yourmom |
+----+---------+
|  1 | NULL    |
|  2 | NULL    |
|  3 | NULL    |
|  4 | NULL    |
|  5 | NULL    |
+----+---------+

I can't rely on this ordering at all. If I insert five more rows, the results will change.

INSERT #el_heapo ( id )
VALUES ( 1 )
INSERT #el_heapo ( id )
VALUES ( 2 )
INSERT #el_heapo ( id )
VALUES ( 3 )
INSERT #el_heapo ( id )
VALUES ( 4 )
INSERT #el_heapo ( id )
VALUES ( 5 )

If I run that same query, I'll get back this:

+----+---------+
| id | yourmom |
+----+---------+
|  1 | NULL    |
|  2 | NULL    |
|  3 | NULL    |
|  4 | NULL    |
|  5 | NULL    |
|  1 | NULL    |
|  2 | NULL    |
|  3 | NULL    |
|  4 | NULL    |
|  5 | NULL    |
+----+---------+

Note that adding ordering by yourmom will give us the same results. I can order by id and get results back any way I want.

Why am I talking about Heaps?

Because you said that the clustered index has nothing to do with the NULL column. You're wrong about that. The clustered index (in SQL Server) is all of your table data, ordered by the key column(s) you chose.

Your NULL column will be sitting at the leaf level of your clustered index. If I create a clustered index on the table, my results will change again.

CREATE CLUSTERED INDEX cx_id ON #el_heapo (id);

+----+---------+
| id | yourmom |
+----+---------+
|  1 | NULL    |
|  1 | NULL    |
|  2 | NULL    |
|  2 | NULL    |
|  3 | NULL    |
|  3 | NULL    |
|  4 | NULL    |
|  4 | NULL    |
|  5 | NULL    |
|  5 | NULL    |
+----+---------+

The scan isn't ordered, but it's a single thread that starts at one end of the index and reads every page. There's no sense in skipping around.

Things get sloppy with a lot more rows, and parallel plans. If we add a million repetitive rows in here, and then try to order by yourmom

INSERT #el_heapo ( id )
SELECT TOP 1000000
       x.n % 5 + 1
FROM   (   
        SELECT     ROW_NUMBER() OVER ( ORDER BY @@SPID )
        FROM       sys.messages AS m
        CROSS JOIN sys.messages AS m2 
       ) AS x(n);

SELECT eh.id
FROM #el_heapo AS eh
ORDER BY eh.yourmom

The rows don't all return in order of yourmom and then id. The id column, if you scroll down a bit, ends up being ordered a lot like the allocation order scan after we dumped in the second set of 1-5 rows. 1 will repeat, 2 will repeat, 3 will repeat, 4 will repeat, 5 will repeat, and then the whole sequence will repeat.

Aaron's answer is fine, aside from him claiming to have navy blue polos. The only polos he owns are black and red and have company logos on them, but I thought it could use some illustration.

Hope this helps!

  • 1
    "CREATE TABLE #el_heapo (id INT, yourmom INT)" Oh, it's Erik. – James May 24 '18 at 14:03
  • 1
    @James that's what your mom says. – Erik Darling May 24 '18 at 14:06
  • I would have upvoted your mum's answer, if Aaron had actually claimed he had any polos ;) – ypercubeᵀᴹ May 24 '18 at 20:59

Not the answer you're looking for? Browse other questions tagged or ask your own question.