Hugo Kornelis (7/22/2013)
kapil_kk (7/22/2013)
John Mitchell-245523 (7/22/2013)
kapil_kk (7/22/2013)
Yes you are right but user PRR asked a scenario only for clustered index and if a clustered index is used then it will reorder of the data in physical order...You're confusing the way the data is stored with the way it's returned. As Hugo explained, they're not guaranteed to be the same.
John
Edit - corrected typo.
When we declare a clustered index on a column then it will gives us the data in a sorted manner or not?
As per the BOl and I read articles its said that after declaring a clustered index it will do reordering of data in a physical order, does this not mean that data will come in sorted manner?
My previous reply was interrupted by a long phone conversation, so I did not see the new messages.
The answer to your questions is: no. Without ORDER BY, there is no guarantee that the rows will be returned in clustered index order. Or in any other particular order. The only thing in SQL Server that guarantees that rows from a query are returned to the client in a particular order is an explicit ORDER BY clause, on the outermost level of the query. All other situations where you happen to see a particular order are side effects of how stuff is implemented, undocumented, unguaranteed, and prone to change.
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
In this example after putting clustered index we will data in ascending sorted order.... while before adding clustered index data was not coming in an order...
I am confused now :doze:
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/