Way to Sort your table without using ORDER BY clause

There are few way to sort your table without using Order By clause, Lets see the same. For our practical we are creating the following table.

 create table TAB_TestSortingWithoutOrderBy

(col1 varchar(5) primary key,

col2 varchar(5) not null unique);

Now let’s insert few records in it

insert into TAB_TestSortingWithoutOrderBy values('S','AA')

insert into TAB_TestSortingWithoutOrderBy values('D','EE')

insert into TAB_TestSortingWithoutOrderBy values('A','BB')

insert into TAB_TestSortingWithoutOrderBy values('C','XX')

select * from TAB_TestSortingWithoutOrderBy

col1  col2

----- -----

S     AA

A     BB

D     EE

C     XX

(4 row(s) affected)

First Way

Now I am writing a simple query with where predicate on col1 which is checking the condition col1 < ‘zzz’, since we have clustered index set on col1, the clustered index will comes to picture. Now since clustered index comes into picture which stored records in logical sorting order the result will be ordered.

select * from TAB_TestSortingWithoutOrderBy

where col1 < 'zzz'

col1  col2

----- -----

A     BB

C     XX

D     EE

S     AA

Second Way

We can also force our clustered index to come into picture while scanning record. Lets see how we can do,

Here in this query we are finding the index detail i.e the index id

select * from sys.indexes where object_id = OBJECT_ID('TAB_TestSortingWithoutOrderBy')

name                             index_id

--------------------------------------------

PK__TAB_Test__357D0D3EE8D60CB3   1

UQ__TAB_Test__357D0D3C32B8019D   2

Now we can use this index to sort our table data

SELECT * FROM TAB_TestSortingWithoutOrderBy WITH(INDEX(PK__TAB_Test__357D0D3EE8D60CB3));

OR

SELECT * FROM TAB_TestSortingWithoutOrderBy WITH(INDEX(1));

col1  col2

----- -----

A     BB

C     XX

D     EE

S     AA

Conclusion: we can bring index into picture to sort our result.

Take Complete risk with Production; Life with CODE is now happening…

 

Post Reference: Vikram Aristocratic Elfin Share