none
SSMS 18 temp table select statement does not return data in order RRS feed

  • Question

  • Hi,
    I am not getting the ordered data which used to work in older version.

    for eg: Student table:
    Id Name
    1 xyz
    2 abc
    3 yax
    select Id, Name into #temp from student
    select *from #temp

    will return data

    Id Name
    2 abc
    1 xyz
    3 yax

    Friday, July 12, 2019 2:40 AM

Answers

  • I am not getting the ordered data

    As Long as you don't apply a ORDER BY Clause (Transact-SQL) you get the data in a somehow random order.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Friday, July 12, 2019 6:02 AM
    Moderator
  • Hi chandansj,

    The order of a query can be forced by using an 'Order by' Clause in the statement. A SQL Database does not actually understand what order you put things in, or store the data in a given order. This means that you need to tell SQL what order you want the items in.

    Think about it like handing some stuff to your friend to hold - she will have all of it for you later, but she stores it someplace in the mean time. She may move it around while you are not looking to make room for something else, or may hand it back in the same order you gave it to her, but you didn't tell her to keep it in order, so she doesn't.

    Databases need to be able to move things around in the background, so the way they are built does not intrinsically know about any order - you need to know the order when you give it to the database, so that you can put it back in the order you want later. The order clause allows SQL to impose an order on the data, but it doesn't remember or have one on its own.

    Important point: Even when SQL returned the items in the correct order without an order by statement the last 1 million times, it does not guarantee that it will do so. Even if a clustered index exists on the table, the results are not guaranteed to be returned in the order you expect. Especially when SQL versions change, not explicitly using an order by clause can break programs that assume the query will be in the order they want!

    Best regards,
    Cathy Ji

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, July 15, 2019 6:41 AM

All replies

  • I am not getting the ordered data

    As Long as you don't apply a ORDER BY Clause (Transact-SQL) you get the data in a somehow random order.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Friday, July 12, 2019 6:02 AM
    Moderator
  • Hi chandansj,

    The order of a query can be forced by using an 'Order by' Clause in the statement. A SQL Database does not actually understand what order you put things in, or store the data in a given order. This means that you need to tell SQL what order you want the items in.

    Think about it like handing some stuff to your friend to hold - she will have all of it for you later, but she stores it someplace in the mean time. She may move it around while you are not looking to make room for something else, or may hand it back in the same order you gave it to her, but you didn't tell her to keep it in order, so she doesn't.

    Databases need to be able to move things around in the background, so the way they are built does not intrinsically know about any order - you need to know the order when you give it to the database, so that you can put it back in the order you want later. The order clause allows SQL to impose an order on the data, but it doesn't remember or have one on its own.

    Important point: Even when SQL returned the items in the correct order without an order by statement the last 1 million times, it does not guarantee that it will do so. Even if a clustered index exists on the table, the results are not guaranteed to be returned in the order you expect. Especially when SQL versions change, not explicitly using an order by clause can break programs that assume the query will be in the order they want!

    Best regards,
    Cathy Ji

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, July 15, 2019 6:41 AM