Avoid "SELECT TOP 1" and "ORDER BY" in Queries

Question

I have the very table in sql server 2008 with lot of data

|ID|Name|Column_1|Column_2|
|..|....|........|........|

more than 18,000 records. So i need to the the row with the lowest value of Column_1 that is date but could by any data type (that is unsorted) so I use these sentence

SELECT TOP 1 ID, Name from table ORDER BY Column_1 ASC

But this is very very slow. And i think that i don't need to to sort the whole table. My question es how to get the same date with out using TOP 1 and ORDER BY

Answer 1

I cannot see why 18,000 rows of information would cause too much of a slow down, but that is obviously without seeing what the data is you are storing.

If you are regularly going to be using the Column_1 field, then I would suggest you place a non-clustered index on it... that will speed up your query.

You can do it by "designing" your table via Sql Server Management Studio, or directly via TSQL...

CREATE INDEX IX_myTable_Column_1 ON myTable (Column_1 ASC)

More information on MSDN about creating indexes here

---

Update thanks to comments by @GarethD who helped me with this, as I wasn't actually aware of it.

As an extra part of the above TSQL statement, it will increase the speed of your queries if you include the names of the other columns that will be used within the index....

CREATE INDEX IX_myTable_Column_1 ON myTable (Column_1 ASC) INCLUDE (ID, Name)

As GarethD points out, using this SQLFiddle as proof, the execution plan is much quicker as it avoids a "RID" (or Row Identifier) lookup.

More information on MSDN about creating indexes with include columns here

Thank you @GarethD