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

Question

I have a table in sql server 2008 with a lot of data

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

over 18,000 entries. So I need the row with the smallest value Column_1 , which is a date, but can be any data type (which is not sorted), so I use this sentence

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

But it is very slow. And I think that I do not need to sort the whole table. My question is: how to get the same date without using TOP 1 and ORDER BY

Answer 1

I do not understand why 18,000 lines of information can lead to too much slowdown, but it is obviously not visible what data you are storing.

If you regularly use the Column_1 field, I would suggest you place a non-clustered index on it ... this will speed up your query.

You can do this by designing your table through Sql Server Management Studio or directly through TSQL ...

 CREATE INDEX IX_myTable_Column_1 ON myTable (Column_1 ASC) 

Read more about MSDN about creating indexes here.

---

Update thanks to the @GarethD comments that helped me with this since I didn't know about it.

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

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

As GarethD points out, using this SQLFiddle as evidence , the execution plan is much faster because it avoids the “RID” (or Row Identifier) ​​lookup.

Learn more about MSDN about creating indexes with columns included here.

Thanks @GarethD

Answer 2

Will it work faster? When I read this question, it was the code that came to mind:

 Select top 1 ID, Name from table where Column_1 = (Select min(Column_1) from table)