SQL Server Performance

Order of physical data

Discussion in 'General Developer Questions' started by asvforce, Jul 21, 2005.

  1. asvforce New Member

    If a new table is created from a select statement, than would the physical order of the data in new table remain the same of select statement.

    for eg. if the statement is :

    Select Col1, Col2, Col3 into NewTable From OldTable Order By Col1, Col2, Col3

    Then would the rows in newtable would get entered in the same order of select statement? And would this order remain the same when i query "Select * from NewTable"?




    Ashish Patel
    (Programmer)
    Vadodara - India
  2. ranjitjain New Member

    I think when you insert rows into table then the order of entering data will be the same as you have in select statement but when you fire select on that table you have to include order by clause again to make sure that you get ordered data else sometimes SQL sends data from buffer itself and you can not be 100% sure for the ordering of data without order by clause.
  3. FrankKalis Moderator

    Even if that would be the case, there is no guarantee that a SELECT * FROM table will return the rows in the order in which they were inserted. The only reliable way to get the resultset you expect is to use an ORDER BY.

    What are you really after?

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  4. FrankKalis Moderator

  5. asvforce New Member

    But what happens if i do a select into like,

    Select Col1, Col2, Col3 into NewTable From OldTable Order By Col1, Col2, Col3

    And THEN ADD an identity column to the table like,

    Alter Table NewTable Add RecPos int identity (1,1)

    and now if i select with order by RecPos

    Select * from NewTable Order by RecPos


    Now would it give me in the same order of data which was selected from OldTable?





    Ashish Patel
    (Programmer)
    Vadodara - India
  6. ranjitjain New Member

    Yeah.....<br />Won by 6 Seconds [<img src='/community/emoticons/emotion-2.gif' alt=':D' />][8D][<img src='/community/emoticons/emotion-4.gif' alt=':p' />][<img src='/community/emoticons/emotion-5.gif' alt=';)' />][:I]<br /><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />Beaten by 6 seconds. [:0]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterst?ASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">
  7. ranjitjain New Member

    I think yes adding identity column can be solution
  8. ranjitjain New Member

    Its better to use create table with identity column and then fire
    insert into table1 select columnnames from table2
    this will always give you records added in the order mentioned
  9. FrankKalis Moderator

  10. asvforce New Member

    Actually, I had a master table and from which i use to create another table consisting with some few records out from that IN the order selected by the user. And as Jatin said, i use to create a table with identity column and then insert the records with Insert Into Statement. But for performance and time saving i saw that the number of records being inserted with Insert Into statement is slow than the Select * Into statement.

    Note: And the order should be maintained in the same way is important as this is my users requested order.



    Ashish Patel
    (Programmer)
    Vadodara - India
  11. FrankKalis Moderator

  12. asvforce New Member

    Yes, i also agree with you frank that the physical order is not my priority now, as select statement never would give the results depending on how the records are physically written.

    But then, would you suggest me staying with my current Creating a table with Identity Field and then Inserting the records into this table from master and later use a select * from newtable order by recpos (identity column).

    Is there no way, which could be more faster than this above method?


    Ashish Patel
    (Programmer)
    Vadodara - India
  13. ranjitjain New Member

    Have you overlooked the links given by frank in last posts.<br />It shows you why not to use select into.<br />Come on Ashish,<br />You urself try both methods, in create and insert ofcourse code is more but its the faster then select into.[<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by asvforce</i><br /><br />Yes, i also agree with you frank that the physical order is not my priority now, as select statement never would give the results depending on how the records are physically written. <br /><br />But then, would you suggest me staying with my current Creating a table with Identity Field and then Inserting the records into this table from master and later use a select * from newtable order by recpos (identity column).<br /><br />Is there no way, which could be more faster than this above method?<br /><br /><br />Ashish Patel<br />(Programmer)<br />Vadodara - India<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">
  14. FrankKalis Moderator

    quote:
    Is there no way, which could be more faster than this above method?
    Faster is relative.
    Probalye you should post your table structures, the indexes on it and what your queries typically look like. Without these informations almost any advise will be like a shot in the dark. Here's a link how to grab these informationshttp://www.aspfaq.com/etiquette.asp?id=5006

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  15. asvforce New Member

    Sorry Frank and Jatin, i was bit away due to work, but ofcourse i found those link very usefull and they thou made my concepts more clear on the same.

    You both are right, giving a try with create table first is more effecient with my architechture.


    Ashish Patel
    (Programmer)
    Vadodara - India
  16. asvforce New Member

Share This Page