March 10, 2003 at 5:59 pm
Have a table that needs to keep data in the order it is entered. Had been working fine for years, but suddenly getting sequence changed. Both SQL 7.0 and 2000 affected, and several servers. Collation has not changed.
It is affecting even simple new tables.
Only thing I've been able to reproduce is:
1. CREATE TABLE MyStuff (Fld1 varchar(10) NOT NULL, Fld2 varchar(40) NOT NULL, Fld3 varchar(40) NULL, Fld4 int NULL, Fld5 datetime NULL)
2. INSERT INTO MyStuff VALUES ('t1','one','e1',1,getdate() )
('t1','Two','e2',1,getdate() )
('t2','three','e2',0,getdate() )
3. SELECT * FROM MyStuff
shows:
t1 one e1 1 <date>
t1 Two e2 1 <date>
t2 three e2 0 <date>
so far, so good.
4. CREATE TABLE TmpMyStuff (Fld1 varchar(10) NOT NULL, Fld2 varchar(40) NOT NULL, Fld3 varchar(40) NULL, Fld4 int NULL, Fld5 datetime NULL)
5. INSERT INTO TmpMyStuff VALUES ('t1','four','e1',1,getdate() )
('t1','five','e2',1,getdate() )
6. INSERT INTO MyStuff SELECT * TmpMyStuff
7. SELECT * FROM MyStuff
shows:
t1 one e1 1 <date>
t1 Two e2 1 <date>
t2 three e2 0 <date>
t1 four e1 1 <date>
t1 five e2 1 <date>
still good
8. INSERT INTO TmpMyStuff VALUES ('t1','six','e1',1,getdate() )
9. SELECT * FROM MyStuff
shows row for 'six' at the top.
10. additional INSERTs put the data at the top or bottom with no rhyme or reason.
Why would insert fail to append? In the true table, it also seems to scatter the inserts.
*Any* suggestions would be appreciated.
+++++
March 10, 2003 at 6:05 pm
Have you changed your indexes? Data order is only guaranteed if an index is used (including clustered of course). Could be a result of a service pack too.
Andy
March 10, 2003 at 6:15 pm
needs to be order of entry, which precludes an index in this case.
We did go to latest service pack in late January... can't recall if it was 3 or 6.
March 11, 2003 at 2:10 am
Relations (including tables) are never ordered. You can never expect a specific order unless you manually sort the resultset. However, if you have a clustered index on a table then the data is actually stored in the order of the index, and the default order when selecting from it will be that order. However, I don't think that is not something that is etched in stone by Microsoft, the result data of a query (i.e a relation) can never be expected in a specific order unless explicitly sorted.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
March 11, 2003 at 5:09 am
I think you're going to have to index, either on a dateadded col or adding an identity. I don't know for sure about the sp, but its definitely possible. I think it was group by that used to have the side affect of always returning ordered in 6.5, that changed in 7.
Andy
March 17, 2003 at 7:40 am
Let me clarify a little.
Since a clustered index specifies how SQL server should physically sort a table, use one of those.
For example:
--First create the table
CREATE TABLE MyStuff (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
Fld1 varchar(10) NOT NULL,
Fld2 varchar(40) NOT NULL,
Fld3 varchar(40) NULL,
Fld4 int NULL,
Fld5 datetime NULL
)
--Add your Clustered index on the ID column (makes it stay sorted in the order you do INSERT INTO MyStuff)
CREATE CLUSTERED INDEX [IX_MyStuff] ON [dbo].[MyStuff]([ID]) ON [PRIMARY]
Greetings, H.Lindgren
March 17, 2003 at 7:50 am
quote:
Let me clarify a little.Since a clustered index specifies how SQL server should physically sort a table, use one of those.
No, this is not quite correct. Yes, SQL Server will store the data in the order of the clustered index, and yes, when you do a select from that table you will (probably) get the rows sorted in that order. But, as I said earlier (I have quoted myself below), this is not something that you should expect for all future. Relations per definition are unordered. Just because the data is stored in one specific order doesn't mean that a projection of it must be sorted in the same way, it just means that the physical operation of sorting it in that specific order will be very fast since it is already sorted!
quote:
if you have a clustered index on a table then the data is actually stored in the order of the index, and the default order when selecting from it will be that order. However, I don't think that is something that is etched in stone by Microsoft. The result data of a query (i.e a relation) can never be expected in a specific order unless explicitly sorted.
Edit: Of course, you are quite right in that a clustered index would solve the specific problem described in the original post of this thread. I just wanted to clarify that this is something that just happens to be so, but one should not depend on it. Remember what happened when MS removed the implicit sorting of SELECTs using GROUP BY...
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
Edited by - chrhedga on 03/17/2003 07:54:27 AM
March 17, 2003 at 8:08 am
You are right. Nothing can be assumed for all time but for MS SQL to change it's implementation of clustered indices into something new would be like a total migration into another DBMS.
quote:
Using Clustered IndexesA clustered index determines the physical order of data in a table. A clustered index is analogous to a telephone directory, which arranges data by last name. Because the clustered index dictates the physical storage order of the data in the table, a table can contain only one clustered index. However, the index can comprise multiple columns (a composite index), like the way a telephone directory is organized by last name and first name.
Since MS also states this in BOL:
quote:
Also, if there is a column(s) that is used frequently to sort the data retrieved from a table, it can be advantageous to cluster (physically sort) the table on that column(s) to save the cost of a sort each time the column(s) is queried.
It is already explicitly sorted 🙂
Regards, H.Lindgren
March 19, 2003 at 1:44 am
quote:
It is already explicitly sorted 🙂
Just to continue this interesting thread, here is what Dr E. F. Codd has to say about order and sorting. From the second page of his first article about the relational model:
quote:
... all the well-known information systems that are marketed today fail to make a clear distinction between order of presentation on the one hand and stored ordering on the other. Significant implementation problems must be solved to provide this kind of independence.
This was written 1969-1970, and discussed problems in the hierarchic and network databases of that time. Interesting how this is still relevant.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
March 19, 2003 at 8:11 pm
Thank you all for responding. While I know SQL does not guarantee any consistent sorting ( despite inconsistencies in the documentation which imply you can do it), I am more curious *why* it would suddenly show up as an issue on several servers at once.
And discovered a goof in my first message:
step 6 should be
SELECT * INTO MyStuff FROM TmpMyStuff
not
INSERT INTO MyStuff SELECT * TmpMyStuff
Which leads me to think the Select Into/Bulkcopy parameter may have an impact, or something similar.
And of course in tiny print the documentation says that feature will be phased out soon...
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply