Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Mar 2004
    Posts
    36

    Post Unanswered: SELECT result without index sorting

    I want to SELECT the result from table, but i want the result return in record entry order, instead of sort by index or ORDER BY certain field.

  2. #2
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: SELECT result without index sorting

    I hope you had a column recording the datetime when the row got inserted.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'd like to know what index sorting is...

    DBCC DBREINDEX?


    Which doesn't make sense.....

    Got an identity column?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Mar 2004
    Posts
    36
    Originally posted by Brett Kaiser
    I'd like to know what index sorting is...

    DBCC DBREINDEX?


    Which doesn't make sense.....

    Got an identity column?
    Does not have any timestamp column and identity column

  5. #5
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    If you not use any index hint then the result would be based on the record entry only.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by Satya
    If you not use any index hint then the result would be based on the record entry only.
    I think this is only true if there is no clustered index, isn't it?

    -PatP

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Guys...the order of data in a database is irrelevant..no?

    Even with a clustered index, the database is held to the physical fact by what room is available on which page...

    Such that a table with a clustered index on LastName

    And you insert AAron...

    could get put on a page in a galaxy far, far away...

    No?

    His/Her question is more fundamental...and the fact that there is no add_ts or identity, means they can't get what they want....

    What happens if you reorg the table?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    I agree. Either you want a sort by a field (expression), or you won't. In the last case, the order is arbitrary. often, it's indeed the order of record entry, but the database engine has the freedom to reorganize the table, so the order of records isn't determined by anything.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    In theory (Relational Algebra), an entity (table) has no order. Tuples (rows) exist inside the entity, but there isn't any "where" so there is no order.

    In practice, a database engine has to store the stuff somehow. It gets an order as a result of storage. That order might be repeatable, it might not, but it is still there.

    Applications that rely on that order are just accidents waiting to happen. It isn't a question of if they will break, that is a given. The only real question is when and how will they break.

    I think that the original poster was looking for a way out of an ugly situation. They inherited a database without any temporal record, and were hoping to find a way to create one at least sort of equitably.

    In MS-SQL, if there isn't a clustered index and a SELECT statement uses index 0, the rows will appear to be in the same order that they were entered into the table. If there is a clustered index, I don't think there is any way to recreate the order of entry unless there is some kind of temporal column like a data_entered or a sequential id (aka IDENTITY) column which you can use for the order.

    -PatP

  10. #10
    Join Date
    Mar 2004
    Posts
    36
    Originally posted by Brett Kaiser
    Guys...the order of data in a database is irrelevant..no?

    Even with a clustered index, the database is held to the physical fact by what room is available on which page...

    Such that a table with a clustered index on LastName

    And you insert AAron...

    could get put on a page in a galaxy far, far away...

    No?

    His/Her question is more fundamental...and the fact that there is no add_ts or identity, means they can't get what they want....

    What happens if you reorg the table?
    How to reorganized the table in Ms SQL

  11. #11
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    If you use DBCC DBREINDEX it will re-org the table by reindexing.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  12. #12
    Join Date
    Mar 2004
    Posts
    45
    Originally posted by Pat Phelan
    I think this is only true if there is no clustered index, isn't it?

    -PatP
    Not even true for heap.

    use tempdb

    create table o(
    id int identity)
    go

    insert o default values
    insert o default values
    insert o default values

    delete from o
    where id = 2

    insert o default values

    select *
    from o

    drop table o
    Hans.

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Hans managed to shoot himself in the foot while demonstrating a technical point that I hadn't mentioned!

    By using an identity column (which is temporal), Hans implicitly created just the kind of opportunity for ordering that chnoeh seems to be looking for! If the original table had had an identity column, then chneoh could simply order by that column to get the rows in the order that they were inserted (unless somebody used SET IDENTITY_INSERT to mess up even that).

    Hans makes a good point though. If the table isn't a strict log (rows added and possibly updated but never deleted), even the "default order" of the heap goes bad.

    The short answer is that there isn't any foolproof way that I know to retrieve the rows in the order that they were inserted into the table, unless you have a column that will allow you to determine when a row was entered.

    -PatP

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yeah, but they are going in in the correct order, and an order by should solve that problem...


    but i guess the whole point is moot...



    Code:
    create table o(
    id int identity, col2 datetime DEFAULT GetDate())
    go
    
    DECLARE @x int
    SELECT @x = 1
    WHILE @x < 1000
    	SELECT @x = @x + 1
    SELECT @x = 1
    insert o default values
    WHILE @x < 1000
      SELECT @x = @x + 1
    SELECT @x = 1
    insert o default values
    WHILE @x < 1000
    	SELECT @x = @x + 1
    SELECT @x = 1
    insert o default values
    WHILE @x < 1000
    	SELECT @x = @x + 1
    SELECT @x = 1
    delete from o
    where id = 2
    WHILE @x < 1000
    	SELECT @x = @x + 1
    SELECT @x = 1
    insert o default values
    
    select *
    from o
    
    drop table o
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Mar 2004
    Posts
    45
    "Shoot himself in foot" means I make mistake? Where? :-)

    I used IDENTITY because is obvious what order rows were entered, and yes this does make obvious one solution to OP's issue, but now is too late. I prefer datetime column with GETDATE() default as in last Brett Kaiser post, but either identity (with SET IDENTITY INSERT) or datetime (with update) can be ruined. I prefer datetime because is more explicitly temporal then identity and does not give arbitrary result if a set is inserted in one operation.

    Never rely on any implicit order, not even that of clustered index. If query optimizer chooses parallel plan then result set without ORDER BY clause often has not order even of clustered index. This is good because it delineates between theoretical and physical. One should know about the physical of course, but not rely on physical artifacts when writing SQL.
    Hans.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •