T-SQL

  • psingla

    Hall of Fame

    Points: 3840

    Comments posted to this topic are about the item T-SQL

    Pramod
    SQL Server DBA | MCSE SQL Server 2012/2014

    in.linkedin.com/in/pramodsingla/
    http://pramodsingla.wordpress.com/

  • Ron McCullough

    SSC Guru

    Points: 63877

    If any one misses this basic question ... woe to the place where they work

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • SQLWinther

    SSCertifiable

    Points: 5946

    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

  • happycat59

    One Orange Chip

    Points: 29308

    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.

  • kapil_kk

    SSC-Insane

    Points: 21316

    Thanks Pramod for the question.....

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • PRAMANA.DBA

    SSCertifiable

    Points: 5507

    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

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    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

  • kapil_kk

    SSC-Insane

    Points: 21316

    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/

  • danielfountain

    SSCarpal Tunnel

    Points: 4229

    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.

  • Toreador

    SSChampion

    Points: 11259

    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.

  • SQLWinther

    SSCertifiable

    Points: 5946

    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

  • paul s-306273

    SSChampion

    Points: 10615

    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.

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • kapil_kk

    SSC-Insane

    Points: 21316

    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/

  • John Mitchell-245523

    SSC Guru

    Points: 148771

    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 43 total)

You must be logged in to reply to this topic. Login to reply