performing a SELECT TOP query with an ORDER BY - should I use an index or a 'sorted view' to increase performance

Question

This query (part of a sproc) will be executed quite a lot:

SELECT TOP (@numRecords)  BlogPost.postId,BlogPost.creationDate,
        BlogPost.header,BlogPost.markedupContentAbstract
     FROM dbo.BlogPost ORDER BY BlogPost.creationDate DESC

Should I put an index on the 'creationDate' field in the BlogPost table? Should I have a view where BlogPost records are ordered and then just SELECT TOP from that?

Details: using SQL Server 2008.

Answer 1

An index on CreationDate (Make sute it's Descending Order) is the best route.

For clarity:

CREATE NONCLUSTERED INDEX [IX_BlogPost_CreationDate] 
ON BlogPost
( CreationDate DESC )

Answer 2

Simply creating a view won't provide any benefit. You could look into indexed views but I don't remember if you can order by on a view and then put an index on it. I would not recommend this approach since your only pulling from a single table.

You could add an index and that should avoid the sort operation that will be done.

Answer 3

You cannot create a view with ORDER BY unless you add a TOP into that view.

You can create an index on creationDate and cover all other columns you use in the SELECT list:

CREATE INDEX ix_blogpost_creationdate__other ON BlogPost (creationDate) INCLUDE (postId, header, markedupContentAbstract)