---
title: "Top with Index and Order of Result set in SQL Server"  
description: "Here we used TOP keyword for selecting records from table in SQL server using Index and Order By statement.   First we create a table and insert some"  
author: "AVADHESH PATEL"  
published: 2012-09-06  
updated: 2014-09-18  
canonical: https://www.mindstick.com/blog/357/top-with-index-and-order-of-result-set-in-sql-server  
category: "database"  
tags: ["database"]  
reading_time: 2 minutes  

---

# Top with Index and Order of Result set in SQL Server

Here we used **TOP** [keyword](https://www.mindstick.com/forum/33572/sql-inner-join-keyword) for selecting [records](https://www.mindstick.com/forum/34640/how-to-create-a-stored-procedure-for-display-all-records) from [table in SQL](https://www.mindstick.com/forum/205/find-the-all-column-with-schema-for-any-table-in-sql-server) [server](https://www.mindstick.com/articles/43769/what-is-serverless-architecture-is-it-worth-switching-over) using **Index** and **Order By** statement.

First we create a table and [insert](https://www.mindstick.com/blog/173/executing-insert-delete-or-update-query-in-sqlserver-using-ado-dot-net) some [values](https://www.mindstick.com/forum/327/sum-textbox-values).

```
--USE DATABASEUSE AVI -- CREATE TABLECREATE TABLE EMPLOYEE([ID] INT PRIMARY KEY,[NAME] VARCHAR(20) NOT NULL,[ADDRESS] VARCHAR(20) NOT NULL) -- INSERT VALUESINSERT INTO EMPLOYEE ([ID],[NAME],[ADDRESS])SELECT 1,'AVADHESH','ALLAHABAD'UNION ALLSELECT 2,'VIKASH','PRATAPGARH'UNION ALLSELECT 3,'ROHIT','DELHI'UNION ALLSELECT 4,'ASHISH','GREATER NOIDA'UNION ALLSELECT 5,'VIANY','LUCKNOW'
```

Let us [select](https://www.mindstick.com/forum/160534/orderby-then-select-vs-select-then-orderby-performance) records and observe the [result](https://www.mindstick.com/blog/12011/advantages-of-getting-result-oriented-seo-from-an-agency) set.

```
-- SELECT RECORD WITH TOPSELECT TOP 3 * FROM EMPLOYEE
```

##### Screen Shot

![Top with Index and Order of Result set in SQL Server](https://www.mindstick.com/blogs/306c6bf9-5da8-4f18-977e-4a4ad51b61fd/images/6474adbd-fc7b-4b8f-8788-ee4b07d2a386.jpg)

Now let us create index over one of the columns and retrieve records. You will find the result is inconsistent than before or different than what we have received. (Note that I did not use word INCORRECT as it is not incorrect result, but different for sure)

```
-- CREATE INDEX ON TABLECREATE NONCLUSTERED INDEX[NCLS_INDEX] ON EMPLOYEE ([NAME] ASC)-- SELECT RECORD WITH NONCLUSTERED INDEX USING TOP

SELECT TOP 3 [NAME] FROM EMPLOYEE
```

##### Screen Shot

![Top with Index and Order of Result set in SQL Server](https://www.mindstick.com/blogs/306c6bf9-5da8-4f18-977e-4a4ad51b61fd/images/33c7763f-2e06-4f18-9509-ba97b2cc9b89.jpg)

Now let us use ORDER BY [clause](https://www.mindstick.com/forum/33937/difference-between-throw-exception-and-throw-clause) and you will see that result is as per expected.

```
-- SELECT RECORD WITH ORDER BYSELECT TOP 3 [NAME] FROM EMPLOYEE ORDER BY ADDRESS
```

##### Screen Shot

![Top with Index and Order of Result set in SQL Server](https://www.mindstick.com/blogs/306c6bf9-5da8-4f18-977e-4a4ad51b61fd/images/9724c501-f04f-48d3-b76b-af1ec228bba8.jpg)

---

Original Source: https://www.mindstick.com/blog/357/top-with-index-and-order-of-result-set-in-sql-server

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.