-2

I have database tables which has custom index. When I run query for this table, I realised my table result doesn't returns values as indexed.

Here is my User table - I have an index for CreateDate in the User table:

1

When I run this query

SELECT [Id], [IsDeleted], [Login], [CreateDate], [DeleteDate] 
FROM [Users]

I'm getting output like this :

2

As you see result don't sorted with createdate.

But however when i try to use this query SELECT [Id],[CreateDate] FROM [Users]

I'm getting sorted output:

3

Is it normal to give query output like this (didn't sorted with index)? Is it effects performance in any case ?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Muhammet İkbal
  • 49
  • 1
  • 8
  • 3
    Why would you expect your data to be sorted? Your query doesn't have an `ORDER BY`. If you need the data in a specific order then use an `ORDER BY` to *define* that order. Otherwise the data is returned in an arbitrary order. – Thom A Aug 22 '23 at 08:18
  • 1
    The order of the output you see is not guaranteed without `ORDER BY` but this is an artefact of the index not being used anyway as it doesn't cover the query when you include the additional columns in the select list. `SELECT [Id],[CreateDate] FROM [Users]` is covered by the index so you get a scan of the index which in this case appears to be in key order rather than allocation order but not guaranteed as no `ORDER BY` – Martin Smith Aug 22 '23 at 08:19
  • 1
    An index by itself does not guarantee any order. It may used by the query optimizer to speed up queries. Also, you can have many indexes per table. Which one of them should be used if you don't specify an ORDER BY? – Olivier Jacot-Descombes Aug 22 '23 at 08:31

1 Answers1

0

You should never assume anything about the order of the results of a query unless you explicitly specify an order by clause.

Without it, the RDBMS is free to return rows in whatever way it "sees" fit, and it may change if something falls out of the cache, the order on the disk changes due to rebuilding the index or any number of other external circumstances.

Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • Understood. Is there any other reason for the sort them via index when I choose only 2 fields and not sort when I choose all of them ? – Muhammet İkbal Aug 22 '23 at 08:30
  • And is it keeping datas indexed in table even if doesn't show me on query result ? – Muhammet İkbal Aug 22 '23 at 08:31
  • 1
    @MuhammetİkbalYakupoğlu if you query only those two columns, the RDBMS probably queries them from the index (since its smaller), and since the index is stored in a sorted way, your result is sorted. If you add more columns the RBDMS probably performs a table scan and returns the rows in whatever arbitrary order they're stored there. – Mureinik Aug 22 '23 at 08:31
  • As [Martin Smith](https://stackoverflow.com/users/73226/martin-smith) [mentioned](https://stackoverflow.com/questions/76951349/mssql-db-index-doesnt-indexing-my-table-with-given-index-rule#comment135654997_76951349), @MuhammetİkbalYakupoğlu , the index isn't covering, so it's unlikely its even being used. But, as Mureinik mentions, there's *plenty* of other things that can be at play too (even if it were covering). – Thom A Aug 22 '23 at 08:31
  • And as for why the index on `CreateDate` also covers `Id` I presume that is your clustered index key - this is included in all non clustered indexes – Martin Smith Aug 22 '23 at 08:34
  • Probably . When i run this stored procedure getting this result `EXEC sys.sp_helpindex @objname = N'Users';` """ IX_Users_CreateDate nonclustered located on PRIMARY CreateDate PK_Users clustered, unique, primary key located on PRIMARY Id – Muhammet İkbal Aug 22 '23 at 08:37
  • As others have stated, if you need your output sorted, use an `ORDER BY` clause. IF you have a clustered index on your table, that will define how the data is stored `physically` on the storage, but that might still not be used when you run your query. The engine will essentially use internal statistics to try and guess the optimal order to return your data (among others based on cardinality). If that cardinality changes over time, this could lead to a better path, and thus the data being returned in a different order than before. – SchmitzIT Aug 22 '23 at 08:55
  • Thanks @SchmitzIT. I was trying to learn how is it working. Thats good explaination for me. – Muhammet İkbal Aug 22 '23 at 10:08