May 31, 2012 at 3:27 pm
Hello,
I have an issue that just started, and I do not know why. I have created a set of tables that I populate with data that gets sent to other systems (outside the organization). These files need to have a first row that contains the column name (as defined by the external organization). The process that I have been using has worked for several months, but suddenly is returning data in the wrong order. The code is like this:
Table:
[field1] varchar(256)
[field2] varchar(256)
.
.
.
[fieldX] varchar(256)
The process that I am using is:
delete from Table
insert into Table
Select
'ColName1',
'ColName2',
.
.
'ColNameX'
Insert into Table
select * from datasource
Previously, when I subsequently did a SELECT on the table, I would get:
ColName1,ColName2,....ColNameX
a,b,.....X
b,b,.....X
c,b,.....X
etc.
Now, I am receiving:
a,b,.....X
b,b,.....X
ColName1,ColName2,....ColNameX
c,b,.....X
where the "header" row is basically at random rows in the file.
There is no identity column, and no order by clause.
What would cause the functioning to change, and how can I retrieve the records from the table(s) in the order in which they were placed into the table?
PS. I know that there is probably a better way to do this using SSIS, but had to get data out of the system in a very limisted amount of time, and so I ended up doing this and using BCP to copy it out to disk. I DO plan on re-engineering the process, but am still pretty new at SQL server functionality, coming from a midrange platform and being self-trained.
May 31, 2012 at 3:29 pm
The only way to ensure order is to use an order by clause. This may mean you need to add an identity column to the tables to ensure that the column headers are the first records in the tables.
May 31, 2012 at 3:33 pm
As Lynn wrote, you need an ORDER BY clause. There is no guarantee of ordering at all, from one execution to the next, of the rows returned without that clause.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 31, 2012 at 3:36 pm
The strange part was that it did return a dataset in arrival sequence up until about a week ago (as near as I can tell). I should be the only admin doing anything on the box, but that does not mean that someone else didn't change something....
May 31, 2012 at 3:39 pm
All I can say is that you have been fortunate until now. SQL does not guarantee order of data when you query a table without using an ORDER BY clause.
May 31, 2012 at 4:08 pm
but that does not mean that someone else didn't change something
As Lynn has stated, that can happen even without any change being made to anything.
SQL only provides ORDERing if you explicitly request it. Otherwise, the return order of rows is basically an "accident", based on conditions -- of the table and of SQL -- at the time the SQL was run.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
May 31, 2012 at 4:16 pm
Most likely the data volume changed, the optimiser picked a different plan and since it had no requirement to preserve order it didn't.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply