T-SQL

  • 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/

  • 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]

  • 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

  • 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.

  • Thanks Pramod for the question.....

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

  • 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

  • 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

  • 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/

  • 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.

  • 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.

  • 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

  • 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.

  • 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/

  • 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/

  • 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