hakan.winther (7/22/2013)

---

kapil_kk (7/22/2013)

---

PRR.DB (7/22/2013)

---

If table doesn't have clustered index , there is no guarantee for order , if table having clustered index then result will display clustered index column order.

Is my answer correct or not? Can any one explain on it?

--Example query.......

USE tempdb

GO

create table Orderby(ID int)

go

insert into Orderby

select 1

union all

select 2

union all

select 5

union all

select 4

union all

select 3

union all

select 7

union all

select 6

go

select * from Orderby

go

CREATE CLUSTERED INDEX [ClusteredIndex_Order1_ID] ON [dbo].[Orderby]

(

[ID] ASC

)

go

select * FROM Orderby

GO

DROP TABLE Orderby

GO

Yes, if a table has clustered index then it will cause reordering of the physical order of the data...

For more information you can refer an article posted today:

http://www.sqlservercentral.com/articles/clustered+index/100477/

There are no guarantees that the clustered index order is used. If there are any nonclustered index or someone later adds a non clustered index, the order of the nonclusrered index may be used instead. If you can't add a nonclustered index because a lazy developer assumes the data is returned in the clustered index order, then you can't optimize the database without breaking the application.