July 20, 2013 at 5:19 pm
July 21, 2013 at 5:42 pm
This was an easy question, but in reallity too many "developers" have missed this fact.
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
July 21, 2013 at 10:15 pm
Worse still, many developers have their queries return data in a particular order without specifying "ORDER BY" and then get upset when told that SQL Server does not guarantee the order unless you direct it to order the results of their query.
July 22, 2013 at 12:19 am
Thanks Pramod for the question.....
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 22, 2013 at 12:42 am
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
July 22, 2013 at 12:58 am
Didn't even have to think for this one (which makes it an excellent Monday question)! 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 22, 2013 at 1:15 am
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/
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 22, 2013 at 1:45 am
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
I think i am correct in saying that as this is a very simple query the order will be that of the clustered index.... but in reality its however the execution plan spits out the rows. For this example the execution plan would just scan the table so it would be in CI order.
July 22, 2013 at 2:27 am
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?
It may well display in this order. But there is no guarantee that it always will, or that the behaviour will be the same in the next release. There is nothing in the standard or in the documentation that states what the sequence will be, therefore you cannot rely on it.
July 22, 2013 at 2:49 am
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.
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
July 22, 2013 at 3:33 am
bitbucket-25253 (7/20/2013)
If any one misses this basic question ... woe to the place where they work
Yes, well at this moment we have 40% incorrect.
That's not a great surprise to me.
I've spoken to lots of people in the past about this.
July 22, 2013 at 3:59 am
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?
A very popular misconception. For simple, small queries on small tables, tests will suggest that it's true. But it isn't.
First - order is not guaranteed. This is documented (as far as I know). That alone makes the answer to this question unambiguous.
Second, on large tables, you can have other things influencing the order. For instance, parallel execution. I have seen results that were obviously caused by this - e.g. first rows 6001-8000 (in order), then 10001-12000 (in order), then 1-6000 (in order), etc - obviously, several parallel tasks each processing 2000 rows but finishing out of order. If you add ORDER BY, SQL Server will ensure that they are returned in order. Without ORDER BY, SQL Server won't bother - why include the overhead if you don;t care about order?
Third, on enterprise edition SQL Server can perform an "advanced scan" (see http://msdn.microsoft.com/en-us/library/ms191475%28v=sql.105%29.aspx), where a scan first starts fetching rows from another already in-progress, then restarts the scan from the start to retrieve the rows it missed.
And fourth - Google "sqlblog beatles vs stones" for a blog post I wrote many years ago that shows another very common cause of order being different from clustered index in a (hopefully) funny way.
July 22, 2013 at 4:35 am
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.
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...
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 22, 2013 at 4:49 am
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.
Viewing 15 posts - 1 through 15 (of 42 total)
You must be logged in to reply to this topic. Login to reply