March 20, 2009 at 4:38 am
Hi!
I have a table "EFTFileData". Its default filegroup was "Secondary". Whenever i was inserting rows in this table, records were inserting randomly. first record was on 2nd line and 3rd might be on 1st line. If i have inserted ID=1 first and then 2,3,4 it should be in the same order in the table. but its not like that. i have no index or key on this table. Just a table with single column "FileData as Char(1000)".
when i have changed filegroup to "Primary" it was working fine. please tell me whats wrong with "Secondary" or is there any option i can manage this problem.
Kindest Regards,
Atif Saeed Khan
March 20, 2009 at 5:04 am
If the table has a clustered index the data would have been ordered .Yours is a Heap and so the data is not ordered.
when i have changed filegroup to "Primary" it was working fine
When you move a table between filegroups it has to re-create the table in the datafile and so it will sort the data.
March 20, 2009 at 5:09 am
There is nothing wrong with Secondary.
I really doubt you know in which physical order rows are stored in a table, what makes you think you know how they got stored?
If you want to ensure physical row order just create a clustered index on the appropriate column.
_____________________________________
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.March 20, 2009 at 6:32 am
There is no guarantee with a SELECT from a table that it will show data in any particular order. The only guaranteed way is to put an ORDER BY in. Adding a sequence number or time stamp will enable you to work out the order they were inserted in and adding a clustered index on one of these will store the data in the correct order and reduce the time it takes for the ORDER BY.
March 20, 2009 at 7:07 am
The order of insertion has no deterministic relationship to the order that records are physically stored. The only functional order in SQL is that imposed by a Top-Level SELECT with an ORDER BY (except FOR XML with ORDER BY).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply