I have a table that stores transactions for accounts, and I want to select out the latest transaction for a specific account. But the DB (Azure SQL) seems to fetch all transactions for the account, and then do top 1 when I order the result on two columns, and I dont understand why.
My query looks something like this:
SELECT TOP 1 * FROM [dbo].[Transaction] T WHERE T.AccountId = 4 ORDER BY T.[Timestamp] DESC, T.[Created] DESC
If I instead just order on Timestamp it does it quicker:
SELECT TOP 1 * FROM [dbo].[Transaction] T WHERE T.AccountId = 4 ORDER BY T.[Timestamp] DESC
The table and index:
CREATE TABLE [dbo].[Transaction]( [TransactionId] [UNIQUEIDENTIFIER] NOT NULL PRIMARY KEY, [Created] [DATETIME] NOT NULL DEFAULT GETUTCDATE(), [Timestamp] [DATETIME] NOT NULL, [AccountId] int NULL) GO CREATE NONCLUSTERED INDEX [IX_Transaction_AccountId_Timestamp] ON [dbo].[Transaction]([AccountId], [Timestamp] DESC) WITH (ONLINE = ON) GO
I have simplified the query and table a bit for testing purposes. In my real table I have 10s of millions of rows, with quite a lot of columns not included here, but the behavior regarding the query plan usage is the same.