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

  Modified On Sep-18-2014 01:24:04 PM

Leave Comment