Order of physical data

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

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.

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

Yeah.....<br />Won by 6 Seconds [<img src='/community/emoticons/emotion-2.gif' alt='' />][8D][<img src='/community/emoticons/emotion-4.gif' alt='' />][<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">

I think yes adding identity column can be solution

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

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

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

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">

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

Even frank thanks a lot for the last linkhttp://www.aspfaq.com/etiquette.asp?id=5006 which is for sure very usefull.

Ashish Patel
(Programmer)
Vadodara - India