Home > DeveloperSection > Blogs > Top with Index and Order of Result set in SQL Server

Top with Index and Order of Result set in SQL Server


Database Database 
Ratings:
0 Comment(s)
 1620  View(s)
Rate this:

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 values.

--USE DATABASE

USE AVI

 -- CREATE TABLE

CREATE TABLE EMPLOYEE

(

[ID] INT PRIMARY KEY,

[NAME] VARCHAR(20) NOT NULL,

[ADDRESS] VARCHAR(20) NOT NULL

)

 -- INSERT VALUES

INSERT INTO EMPLOYEE ([ID],[NAME],[ADDRESS])

SELECT 1,'AVADHESH','ALLAHABAD'

UNION ALL

SELECT 2,'VIKASH','PRATAPGARH'

UNION ALL

SELECT 3,'ROHIT','DELHI'

UNION ALL

SELECT 4,'ASHISH','GREATER NOIDA'

UNION ALL

SELECT 5,'VIANY','LUCKNOW'

Let us select records and observe the result set.

-- SELECT RECORD WITH TOP

SELECT TOP 3 * FROM EMPLOYEE

Screen Shot

 

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 TABLE

CREATE NONCLUSTERED INDEX[NCLS_INDEX] ON EMPLOYEE ([NAME] ASC)

-- SELECT RECORD WITH NONCLUSTERED INDEX USING TOP

SELECT TOP 3 [NAME] FROM EMPLOYEE

Screen Shot

Now let us use ORDER BY clause and you will see that result is as per expected.

-- SELECT RECORD WITH ORDER BY

SELECT TOP 3 [NAME] FROM EMPLOYEE ORDER BY ADDRESS

Screen Shot


Don't want to miss updates? Please click the below button!

Follow MindStick