Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Sorting Records Going Into New Table. Expand / Collapse
Author
Message
Posted Sunday, May 5, 2013 6:31 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 24, 2013 12:22 PM
Points: 24, Visits: 105
I am having problems ordering records to go into another table. The following is my code:

USE
EURUSD
GO

SELECT *
INTO NEWTABLE
FROM OLDTABLE
ORDER BY RECORD

After processing into another table I still have a wrong starting record and gaps in the continuous records. When I process a SELECT TOP 1000 query I am expecting to see the first record proceeding through the 1000th. record. Instead I am seeing record 8625 forward. How do I create a query to create a new table with the proper sequential ordering?
Post #1449554
Posted Sunday, May 5, 2013 7:03 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
mcertini (5/5/2013)
I am having problems ordering records to go into another table. The following is my code:

USE
EURUSD
GO

SELECT *
INTO NEWTABLE
FROM OLDTABLE
ORDER BY RECORD

After processing into another table I still have a wrong starting record and gaps in the continuous records. When I process a SELECT TOP 1000 query I am expecting to see the first record proceeding through the 1000th. record. Instead I am seeing record 8625 forward. How do I create a query to create a new table with the proper sequential ordering?


This will create a new column using the special system IDENTITY function which works when doing SELECT INTO. Your rows will maintain their original keys but will now have a new sequentially numbered column.


SELECT
IDENTITY(INT,1,1) AS ID,
OLD.*
INTO NEWTABLE AS NEW
FROM OLDTABLE AS OLD
ORDER BY RECORD


Second option...this will also create a sequentially ordered column based on your sort criteria which gives you more flexibility on the sorting.


SELECT
ROW_NUMBER() OVER (ORDER BY OLD.RECORD, OLD.COL1, OLD.COL2 [etc]) AS ID,
OLD.*
INTO NEWTABLE AS NEW
FROM OLDTABLE AS OLD


There's no guarantee that when you insert large amounts of data into a table (especially an existing table) that the rows will be in physical order. If you create a pseudo-key like this for ordering you will still need to apply an ORDER BY to be sure of getting results back in your desired order.

If you really need the rows in physical order in the file you can drop all indexes and re-create a clustered index on the columns to be ordered. The index process will then by definition re-order the data physically based on whatever column definitions you define for the clustered index.

If for some reason your table is using uniqueidentifiers (GUIDs) as a sort key, there is a new SequentialGUID datatype available which can be used to keep your rows in sequential order. Otherwise, trying to sort based on a GUID's value is somewhat pointless.

 
Post #1449556
Posted Sunday, May 5, 2013 7:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 24, 2013 12:22 PM
Points: 24, Visits: 105
Steven,

Thank you for the reply it is much appreciated.

Mike
Post #1449558
Posted Monday, May 6, 2013 7:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 1:44 PM
Points: 28, Visits: 89

Just clarifying the post above, because we need to do this many times in our company...Isn't it true that the option above - an Identity column - only will work for ordering on small sets of data because of paging? I am not sure if this is what you are trying to say above. So, the only way to get a truly ordered data set is to use the clustered index? Will RowNumber() work on large sets also? Thanks.
Post #1449706
Posted Monday, May 6, 2013 7:40 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, July 21, 2014 8:06 AM
Points: 360, Visits: 1,949
I have to ask why order matters for the records stored in the table. SQL Server makes no guarantee about that. The only reliable way I am aware of to present records in a desired sequence is to use an order by clause when you select the records. I would never rely on the storage order of data in a SQL Server table.

This behavior is different in the AS400 world, where you can present data in arrival order, and influence that order with an order by in the table load process.
Post #1449717
Posted Monday, May 6, 2013 7:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 1:44 PM
Points: 28, Visits: 89
Many times we need to rely on records being next to each other to determine next Value ordered by a Date, let's say. So, if the identity column is called "Ref". We need to rely on Ref = Ref-1. It doesn't necessarily have to display in that order, but the Refs have to be in order based on the fields we determine. Here's a quick example of something we would do. The Clustered Index would be on Vendor, Item, and ChangeDate for this example.

Update tmpCost
set cur.NextCost = nxt.ListCost
from tmpCost cur
inner join tmpCost nxt
on cur.Vendor = nxt.Vendor
and cur.Item = nxt.Item
and cur.Ref + 1 = nxt.Ref

Thanks.
Post #1449723
Posted Monday, May 6, 2013 8:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:21 PM
Points: 13,083, Visits: 11,918
Ross McMicken (5/6/2013)
The only reliable way I am aware of to present records in a desired sequence is to use an order by clause when you select the records. I would never rely on the storage order of data in a SQL Server table.


That is absolutely correct 100%. Tables by their nature have no order. They are unordered sets. The only way to ensure order is with an order by clause. Storage order means absolutely nothing.


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1449744
Posted Monday, May 6, 2013 9:12 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:21 PM
Points: 1,210, Visits: 2,512
Kricky (5/6/2013)
Many times we need to rely on records being next to each other to determine next Value ordered by a Date, let's say. So, if the identity column is called "Ref". We need to rely on Ref = Ref-1. It doesn't necessarily have to display in that order, but the Refs have to be in order based on the fields we determine. Here's a quick example of something we would do. The Clustered Index would be on Vendor, Item, and ChangeDate for this example.

Update tmpCost
set cur.NextCost = nxt.ListCost
from tmpCost cur
inner join tmpCost nxt
on cur.Vendor = nxt.Vendor
and cur.Item = nxt.Item
and cur.Ref + 1 = nxt.Ref

Thanks.


This code breaks down if you have ever inserted rows out of sequence of the clustered key, though. For example, if you insert 10 rows for Vendor A/Item 1/ChangeDate 1-10, 10 rows for Vendor A/Item 2/ChangeDate 1-10, 10 rows for Vendor B/Item 1/ChangeDate 1-10, then 10 more rows for Vendor A/Item 1/ChangeDate 11-20, Vendor A/Item 1 will have rows with Ref 1-10 and 31-40 (the value of an IDENTITY column is incremented each time a row is inserted and doesn't depend on the clustered key order). Your update statement will update the rows with Ref 1-9 and Ref 31-39, but not Ref 10 because there is no row that satisfies the join condition cur.Ref + 1 = nxt.Ref.

Even with a clustered index, which imposes the logical order of the clustered key on the page chain holding the data for the table (pages in the page chain and rows on each page are ordered according to the clustered key), SQL Server can and will return rows in any order unless constrained by an ORDER BY clause. Writing DML that depends on row ordering to return the correct result without specifying an ORDER BY clause is a bad idea, period.


Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Post #1449749
Posted Monday, May 6, 2013 12:49 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 7:32 AM
Points: 809, Visits: 1,157
If you create Clustered Index on RECORD column of new table. It sorts and stores data in the clustered index key.
Post #1449843
Posted Monday, May 6, 2013 1:17 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:21 PM
Points: 13,083, Visits: 11,918
Neeraj Dwivedi (5/6/2013)
If you create Clustered Index on RECORD column of new table. It sorts and stores data in the clustered index key.


That is true for storage (well most of the time). But it is absolutely not true for retrieval. If you want rows in a certain order from a select there is ONLY 1 way do ensure the order, use an order by clause.

http://blogs.msdn.com/b/conor_cunningham_msft/archive/2008/08/27/no-seatbelt-expecting-order-without-order-by.aspx


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1449852
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse