February 21, 2012 at 7:58 am
I have a staging table (MergedDataStage1) that was populated by inserts from 2 tables. I now need to be able to insert the rows from this staging table into another table in ascending order by a datetime field. I tried to do the following:
INSERT INTO MergedDataOrdered
SELECT * FROM MergedDataStage1
ORDER BY TIME_STAMP --TIME_STAMP is a column with a datetime data type
It appears that the data in the MergedDataOrdered table is not stored in ascending order.
Do you have any suggestions or thoughts on this?
February 21, 2012 at 8:14 am
You probably don't need to do this since creating a non-clustered index on your final table, on the datetime column (and keeping the stats regularly updated) will allow efficient execution of any SELECTs anyway. Are you trying to refer to rows in the table by an absolute (positional) value or something? If so maybe consider changing your calling query, something simple like:
CREATE PROCEDURE findTheRows(@mydate)
AS BEGIN
SELECT this, that, thisdate
FROM mytable
WHERE thisdate > @mydate
ORDER BY thisdate ASC
END
This will order it on the fly, per query.
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
February 21, 2012 at 8:18 am
gary.morey (2/21/2012)
I have a staging table (MergedDataStage1) that was populated by inserts from 2 tables. I now need to be able to insert the rows from this staging table into another table in ascending order by a datetime field. I tried to do the following:INSERT INTO MergedDataOrdered
SELECT * FROM MergedDataStage1
ORDER BY TIME_STAMP --TIME_STAMP is a column with a datetime data type
It appears that the data in the MergedDataOrdered table is not stored in ascending order.
Do you have any suggestions or thoughts on this?
Why would you want the data to be stored in a particular order? Data ordering is a function of data-retrieving not a function of data-storing.
Having said that, I agree with previous poster in the sense that a clustered index on the ordering column may do it.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 21, 2012 at 8:20 am
gary.morey (2/21/2012)
I have a staging table (MergedDataStage1) that was populated by inserts from 2 tables. I now need to be able to insert the rows from this staging table into another table in ascending order by a datetime field. I tried to do the following:INSERT INTO MergedDataOrdered
SELECT * FROM MergedDataStage1
ORDER BY TIME_STAMP --TIME_STAMP is a column with a datetime data type
It appears that the data in the MergedDataOrdered table is not stored in ascending order.
Do you have any suggestions or thoughts on this?
Why do you say it not stored in ascending order? Do you actually care what order the file system stores the data? If you want your data in a particular there is one and only way to do it, use an order by clause.
You can do some stuff like you did ordering the insert but there is no guarantee it will be in that order in the table. By definition a table has no order.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 21, 2012 at 8:26 am
There actually is a clustered index on the TIME_STAMP column, and as you saw in the insert statement in my post I am using an ORDER_BY. So what you are actually saying is that there is no way to guarantee that the data will be stored in TIME_STAMP order; the queries run against it would need to use an ORDER BY to sort the data. Is what I am saying correct?
February 21, 2012 at 8:36 am
gary.morey (2/21/2012)
There actually is a clustered index on the TIME_STAMP column, and as you saw in the insert statement in my post I am using an ORDER_BY. So what you are actually saying is that there is no way to guarantee that the data will be stored in TIME_STAMP order; the queries run against it would need to use an ORDER BY to sort the data. Is what I am saying correct?
Not only can you not be certain of the order the rows are stored (which really means nothing other than physical location on disc) you absolutely cannot be certain of the order of query unless you use an ORDER BY. Often, data will be returned in the order of the clustered index and on small datasets you don't notice it. Plain and simple, if you want your results in a certain order you have to define that order with an ORDER BY.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 21, 2012 at 8:40 am
Thank you for your responses. Your help is greatly appreciated.
February 21, 2012 at 8:42 am
Yes. ORDER BY clause must be specified on SELECT statements if a particular order is needed.
Adding to it, here is the beauty of it... even if a clustered index ensures your data is phisically ordered by the clustered index key, if you query the table with no ORDER BY clause there is no warrantie the result set will come back in the same order.
Bottom line is: if you want your result set ordered, add ORDER BY clause on SELECT statement.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 21, 2012 at 8:42 am
gary.morey (2/21/2012)
There actually is a clustered index on the TIME_STAMP column, and as you saw in the insert statement in my post I am using an ORDER_BY. So what you are actually saying is that there is no way to guarantee that the data will be stored in TIME_STAMP order; the queries run against it would need to use an ORDER BY to sort the data. Is what I am saying correct?
What people are trying to say is that it does not matter in which order the data is stored. You will never ever know exactly how it is stored, and you don't really care. All you care about is how it is retrieved. Some people like to try to avoid the ORDER BY clause and assume that their data is stored in some order. It is not. Let's "assume" that it was stored exactly in the order of your TIME_STAMP column. You still are not guaranteed that when you do a SELECT ... FROM table that it will be returned in the order in which it is stored. SQL Server retrieves results in which ever order it deems fastest at execution time. Without an ORDER BY you are never guaranteed the order of results.
Now, after knowing that... Do you really care about the order it is stored?
Jared
CE - Microsoft
February 21, 2012 at 8:49 am
No. Now that I understand this I don't care how the data is stored.
Again, thank you all for your responses. You may consider this closed.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply