| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-04-08 : 09:50:38
|
| Allan writes "I am pretty sure the answer to this question is yes, but I need to be absolutely certain this is always true. Whenever an insert query is performed on MSSQL Server 7.0 using a select statement with an order by clause, are records inserted into the table in same order that they are selected via the order by clause?example:INSERT #RowNumber (emp_id)SELECT emp_idFROM employeeWHERE job_id = 10ORDER BY lname" |
|
|
smccreadie
Aged Yak Warrior
505 Posts |
Posted - 2002-04-08 : 09:52:53
|
| I think this is true but in SQL server there's no guarentee that data will stay in a particular order. You can use a clustered index or a sort by query to return rows in a certain order. |
 |
|
|
techranger
Starting Member
2 Posts |
Posted - 2002-04-08 : 10:23:26
|
quote:
I think this is true but in SQL server there's no guarentee that data will stay in a particular order. You can use a clustered index or a sort by query to return rows in a certain order.
I also need to re-order some existing data and store it using the new order; however, saving the field the data was sorted by is not really an option. I was going to use a technique similiar to the one shown in the article "Returning a row number in a query".http://www.sqlteam.com/item.asp?ItemID=1491You seem to be implying this method is unreliable. If that is the case, then that article on this site,http://www.sqlteam.com/item.asp?ItemID=1491, must be using an unreliable technique.Anyone else have a more definitive answer?Thanks, |
|
|
|
yakoo
Constraint Violating Yak Guru
312 Posts |
Posted - 2002-04-08 : 10:47:59
|
quote:
You can use a clustered index or a sort by query to return rows in a certain order.
Take a look in BOL under indexes. You should be able to re-index your table using a clustered index based on your sort criteria. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-04-08 : 10:56:41
|
quote:
I also need to re-order some existing data and store it using the new order...I was going to use a technique similiar to the one shown in the article "Returning a row number in a query"...You seem to be implying this method is unreliable. If that is the case, then that article on this site,http://www.sqlteam.com/item.asp?ItemID=1491, must be using an unreliable technique...Anyone else have a more definitive answer?
The definitive answer is that if you need to return rows in a particular order, you MUST use an ORDER BY clause in a SELECT statement. Always. Even if you use a clustered index, it does NOT mean you will SELECT in the order of the clustered index UNLESS you use an ORDER BY clause. It makes absolutely no difference what order the data is STORED in, the only way to GUARANTEE the order you want is to use ORDER BY. |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-04-08 : 19:19:04
|
You might also want to check this out [url]http://www.sqlteam.com/item.asp?ItemID=765[/url]It shows how to generate a "row number" for each record. However, you'd need to re-do this every time a record is added or deleted. In any case you've got me really confused quote:
saving the field the data was sorted by is not really an option
Why not?--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
techranger
Starting Member
2 Posts |
Posted - 2002-04-16 : 13:44:09
|
quote:
In any case you've got me really confused saving the field the data was sorted by is not really an optionWhy not?
Because sometimes the field is numeric, varchar, bit, etc.The other reason is that the fielded data may change, before the report is actually printed. What we wanted is for the report to be sorted in the order that the data was originallygathered and sorted; however, we did not want to create an exact copy of all of the data. We only wanted to store the ID values, so we had to include a sort order field which was basically a list of numbers (1 to N) for each record.My question was whenever you do an insert query with an "Order By" clause in the select statement are the records inserted into the table in the order specified by the "order by" clause. Because, I have an identity column in the "insert" table that autonumbers the records based on the sort order designated by the "order by" clause.I could not find any documentation to verify that records do in fact get inserted in the order designated by the "order by" clause. I evenchecked the ANSI SQL-92 standard and could not find any definitive statement to verify this behavior. Logically you would assume this behavior to be inherit with the wording of the SQL statement, but I really needed some documentation to confirm my assumptions. I ran several tests and got the desired results, but I would sure hate for someone to print out a 20,000 page report with everything sorted incorrectly. That could be somewhat costly.Thanks, |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-04-16 : 15:17:23
|
| In a relational database there is no concept of row order. you must specify an ORDER BY clause to tell SQL Server what order you want. If you insert into a table w/ an identity column, you're fine, since the identity value of each row is immutable. The overall message from the previous posters is: don't rely on the internal mechanism of page ordering in SQL Server - use an explicit ORDER BY.setBasedIsTheTruepath<O> |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-04-16 : 19:54:13
|
quote:
I would sure hate for someone to print out a 20,000 page report with everything sorted incorrectly. That could be somewhat costly.
so would I. So I would insert your report records into your "input" table, and do your ordering when selecting those records for your report....(and don't select the field you'll be ordering by if you don't want it). I know you said you couldn't save the "order by" field quote:
Because sometimes the field is numeric, varchar, bit, etc.
I guess that's where I'd use the "case" statement... Cheers--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
|
Insert Query using Select and Order By