CREATE INDEX DisplayName ON dbo.Users(DisplayName DESC);

-- Not ordered by DisplayName ASC
SELECT *
FROM dbo.Users
WHERE DisplayName IN (N'Brent Ozar', N'Jon Skeet')
ORDER BY DisplayName ASC;

DECLARE @T table (i integer NOT NULL INDEX ii (i DESC));
INSERT @T (i) VALUES (1), (2), (3);

-- Ordered descending!
SELECT Ascending = T.i
FROM @T AS T
WHERE T.i IN (1, 2, 3)
ORDER BY T.i ASC; -- We asked for ascending

-- Ordered ascending!
SELECT Descending = T.i
FROM @T AS T
WHERE T.i IN (1, 2, 3)
ORDER BY T.i DESC; -- We asked for descending