[MSSQL] sortering op basis van Index

[PdeBie]

Heren/Dames ontwikkelaars,

Ik zit met het volgende probleem waarbij de sortering van een tabel niet goed gaat.

De tabel:

SQL:

CREATE TABLE [dbo].[OrderTrack](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [OrderId] [int] NOT NULL,
    [OrderStatus] [int] NOT NULL,
    [DateChanged] [datetime] NOT NULL,
    [ChangedBy] [varchar](25) NOT NULL,
    [PublishToMember] [bit] NOT NULL,
    [IPAddress] [varchar](15) NOT NULL,
    [Remark] [varchar](250) NULL,
    [DeliveryDate] [datetime] NOT NULL,
    [Sender] [int] NOT NULL,
    [Receiver] [int] NOT NULL,
 CONSTRAINT [PK_OrderTrack] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

Primary Key op 'ID' en een Index op 'OrderID - Asc' en 'DateChanged - Desc'.

Als ik daar een simpele query op los laat, krijg ik het volgende resultaat.
De cijfers in de laatste kolom komen niet uit de query, maar geven de volgorde aan waarin de records zijn aangemaakt in de database (iets duidelijker misschien dan het ID)

SQL:

SELECT ID, OrderId, DateChanged, OrderStatus
FROM [dbo].[OrderTrack]
WHERE OrderId = 376637

ID	OrderId	DateChanged	OrderStatus	Volgorde
1390039	376637	2012-10-29 08:56:42.250	20	4
1390036	376637	2012-10-29 08:56:25.357	100	3
1389954	376637	2012-10-29 08:51:00.000	1	1
1390029	376637	2012-10-29 08:55:59.867	2	2

Gevolg is dat de records in de verkeerde volgorde op het scherm getoond worden. 4, 3, 1, 2 in plaats van het gewenste 4, 3, 2, 1

Als ik de query als volgt draai, krijg ik wel de juiste volgorde:

SQL:

SELECT ID, OrderId, DateChanged, OrderStatus
FROM [dbo].[OrderTrack]
WHERE OrderId = 376637
ORDER BY OrderID ASC, DateChanged DESC

Mijn kennis van Indexen is nihil te noemen, maar het lijkt mij dat deze roet in het eten gooit. Maar ik tast wel even in het duister waarom de sortering dan zo raar is als ik geen ORDER BY mee geef aan de query.

[ Voor 6% gewijzigd door PdeBie op 29-10-2012 11:00 ]

[P_de_B]

Als je geen sortering meegeeft in de ORDER BY is er geen garantie dat er een bepaalde volgorde aangehouden wordt. Een RDBMS heeft geen concept 'juiste volgorde' of 'eerste record' of 'rare volgorde'. Er is een gerede kans dat de volgorde van een (meestal geclusterde) index aangehouden wordt, maar dit is niet gegarandeerd. In dit geval gebeurt dat dus.

Je zult dus altijd zelf de volgorde op moeten geven middels de ORDER BY als de volgorde belangrijk is.

[TallManNL]

pdebie schreef op maandag 29 oktober 2012 @ 10:30:
...
De cijfers in de laatste kolom komen niet uit de query, maar geven de volgorde aan waarin de records zijn aangemaakt in de database (iets duidelijker misschien dan het ID)

ID	OrderId	DateChanged	Volgorde
1390039	376637	2012-10-29 08:56:42.250	4
1390036	376637	2012-10-29 08:56:25.357	3
1390029	376637	2012-10-29 08:55:59.867	1
1389954	376637	2012-10-29 08:51:00.000	2

...

Sinds wanneer gaat een IDENTITY column 1390029 aanmaken voor 1389954? Dat zal deze niet doen (behalve als je zelf met de identity seed hebt zitten klooien tussendoor) en ik vermoed dan ook dat je volgorde kolom niet op orde is.

Overigens eens met andere poster dat je als je volgorde wilt een ORDER BY in je SELECT opneemt

[Orion84]

pdebie schreef op maandag 29 oktober 2012 @ 10:30:
Gevolg is dat de records in de verkeerde volgorde op het scherm getoond worden. 4, 3, 1, 2 in plaats van het gewenste 4, 3, 2, 1

Ik zie in dat tabelletje wat je laat zien toch echt gewoon de items op datechanged desc. gesorteerd staan? De oudste onder, de nieuwste bovenaan. Ook de ID's lopen netjes af.

Hoe kom jij op die 4, 3, 1, 2 volgorde?

[PdeBie]

@Orion: je hebt gelijk. Valt me nu pas op. Denk Copy-Paste foutje.
Ben voor de zekerheid met deze gegevens terug gegaan naar MS SQL manager en de query opnieuw gedaan. Wat valt me op. Als ik de kolom OrderStatus toevoeg aan de query gaat het fout.

(heb de query in post 1 even gewijzigd om verwarring te voorkomen bij nieuwe lezers)

SQL:

SELECT ID, OrderId, DateChanged, OrderStatus
FROM [dbo].[OrderTrack]
WHERE OrderId = 376637

ID	OrderId	DateChanged	OrderStatus	Volgorde
1390039	376637	2012-10-29 08:56:42.250	20	4
1390036	376637	2012-10-29 08:56:25.357	100	3
1389954	376637	2012-10-29 08:51:00.000	1	1
1390029	376637	2012-10-29 08:55:59.867	2	2

@TallManNL
ik rommel niet met de identity seed. Die staat gewoon op +1 op het moment dat er een nieuw record aangemaakt wordt.

Ik ben het eens met P_de_B dat er een ORDER BY aan het SELECT statement toegevoegd moet worden, maar ik ben nieuwsgierig naar de reden waarom de query '4312' terug geeft als je geen ORDER BY opgeeft.

Als je de volgende query draait, krijg je alles vanaf record 1 t/m einde in de volgorde zoals ingeschoten.

SQL:

select * from dbo.ordertrack

Voeg je de 'WHERE OrderID = xxxx' toe, gaat de sortering mis.

[Zoijar]

Belangrijkste boodschap hier is dat een database met record sets werkt. Sets hebben per definitie geen concept van volgorde. Alleen als je order by gebruikt kan je een volgorde afdwingen. Dat er soms een bepaalde volgorde uit lijkt te komen mag en kan je niet vanuit gaan.

Het is dus volstrekt legaal om uit de eerste query 3,2,1,4 te laten rollen en meteen daarna op de volgende identieke query 2,4,3,1 en de volgende 1,2,3,4.

[ Voor 22% gewijzigd door Zoijar op 29-10-2012 11:01 ]

[RobIII]

Geen Order by = _{(in general)} geen garantie op de sortering van de resultset. Simple as that. Rest de vraag: waarom geef je niet gewoon een order mee i.p.v. te vertrouwen op 't feit dat de order die je nu (toevallig) krijgt altijd zo is?

[ Voor 6% gewijzigd door RobIII op 29-10-2012 11:15 ]

[PdeBie]

Ik liep tegen deze code aan doordat een eindgebruiker melde dat de volgorde niet correct was.
Ik heb de order by dan ook direct toegevoegd.

Mijn vraag hier kwam voort uit mijn nieuwsgierigheid.

[Woy]

Het is inderdaad maar net hoe het je RDBMS uitkomt. Als jij het niet specificeert kijkt hij gewoon hoe hij het zo snel mogelijk terug kan geven. Als hij wat data uit zijn cache kan halen is de kans dat die data eerst komt groot, als hij het van disk moet lezen zal het waarschijnlijk in de volgorde komen dat hij het tegen komt op de disk ( Dus waarschijnlijk gerelateerd aan de clustered index ).

Maar garantie heb je niet, aangezien je door het weglaten van de ORDER BY impliciet aan het RDBMS aangeeft dat je niet geïnteresseerd bent in de volgorde van het resultaat.

[PdeBie]

Ik ben even gaan lezen wat zoijar precies met record sets bedoelde, maar na het lezen van een klein artikel werd er me een hoop duidelijk.

Dus inderdaad basis regel: Sortering belangrijk? Zelf aan query toevoegen.