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»»

Insert Into - Please Please Help Me ! Expand / Collapse
Author
Message
Posted Tuesday, July 27, 2010 10:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 9, 2010 7:02 AM
Points: 5, Visits: 13
All,

We have just migrated from SQL 2000 to SQL 2008. Table structures,indexes etc are the same as they were before. However, when we run the following query in 08 Management Studio on our new server we get a different result than running it through MMS on our old server. It appears that the insert into or the order by is just not working as it seems to insert in a differnet order each time we run the query. Sometimes, we get what we expect but also NOT what we expect.

Please note that we are not selecting from a view so the 'order by' view bug i assume does not affect this ??


Query :

use userdata
go
drop table BB_HOLB999
go
create table BB_HOLB999 (
SYST varchar (10),
CREF decimal (10),
BDAT decimal(7,0))
go
create unique clustered index BI on BB_HOLB999(CREF,syst) with ignore_dup_key
go
insert into BB_HOLB999
select 'OSCAR' as [syst], CREF, bkbdat
from BB_TABLE join refdata.dbo.TDFBK on CREF=BKCREF
where BKCOST > 0
and BKPAX > 0
and BKINV1 > 0
and BKLINK is NULL
and bktype is NULL
and BKBSTS = ''
and BKLAPD is null
and BKBDAT<dmat
and (BKDEPT in ('20','23','16','50','10','40','52','80')
or (BKDEPT ='30' and BKPROD in ('Qp','39','8p','8q','8r','8s','8t','8u','8v','8w','8x','1A','9d','X2','AG','AH','AF','AI','6N','6A','8N','V1','U1')))
order by bkbdat desc
go

When running the query below to check a given customer we find the result can be different.(i.e not the customers last booking !!)

select *
from BB_HOLB999
where CREF='8458923'
go


Please please help as i have spent hours googling this and trying to understand why this is not working !!!!!!!!!

Thanks In Advance

Lee
Post #959533
Posted Tuesday, July 27, 2010 10:49 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:25 PM
Points: 43,008, Visits: 36,164
The only thing that an order by does when used on an insert...select is to set the order of the identity columns (if there is one). If you want an ordered resultset, you need to put the order by on the select statement. Without an order by on the select, SQL is free to return the rows in any order it likes, regardless of the 'order' of rows in the table.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #959557
Posted Tuesday, July 27, 2010 1:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 9, 2010 7:02 AM
Points: 5, Visits: 13
Excuse my ignorance, could you explain to me what you mean by an order by on the select statement please ?

Why does this work in 2000 though ??

Thanks In Advance

Lee
Post #959650
Posted Tuesday, July 27, 2010 2:38 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:25 PM
Points: 43,008, Visits: 36,164
Instead of putting the ORDER BY on the statement that inserts into BB_HOLB999, you need to put it onto the statement that retrieves from BB_HOLB999. So in the example you gave...

create table BB_HOLB999 (
SYST varchar (10),
CREF decimal (10),
BDAT decimal(7,0))
go
create unique clustered index BI on BB_HOLB999(CREF,syst) with ignore_dup_key
go
insert into BB_HOLB999
select 'OSCAR' as [syst], CREF, bkbdat
from BB_TABLE join refdata.dbo.TDFBK on CREF=BKCREF
where BKCOST > 0
and BKPAX > 0
and BKINV1 > 0
and BKLINK is NULL
and bktype is NULL
and BKBSTS = ''
and BKLAPD is null
and BKBDAT<dmat
and (BKDEPT in ('20','23','16','50','10','40','52','80')
or (BKDEPT ='30' and BKPROD in ('Qp','39','8p','8q','8r','8s','8t','8u','8v','8w','8x','1A','9d','X2','AG','AH','AF','AI','6N','6A','8N','V1','U1')))
go

select *
from BB_HOLB999
where CREF='8458923'
ORDER BY BDAT DESC
go

As for why it worked on SQL 2000, luck probably more than anything. The whole SQL architecture changed quite a lot between 2000 and 2005 and there were a lot of things that changed. The order of results has never been guaranteed without an order by. If SQL 2000 behaved in a certain way, that's by chance and not a feature of the DB engine.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #959704
Posted Tuesday, July 27, 2010 3:26 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:38 AM
Points: 1,945, Visits: 3,001
Read any book on RDBMS and look up Codd's 12 principles. There is one called "The Information Principle" which says that all relationships -- such as a sort order--have to be modeled as scalar values in the columns of rows in a table. A table has no ordering from its physical storage; the table is a set and sets by definition have no order.

You are still locked in a mindset that expects data to be on a magnetic tape in 1957. Get a copy of THINKING IN SET and see if it will help you escape it.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #959744
Posted Wednesday, July 28, 2010 3:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 9, 2010 7:02 AM
Points: 5, Visits: 13
All,

Thanks for the replies but i am still at a loss here. Let me explain further and simplify the query and exactly what i am trying to do. I know there is another way of doing this that does work but i want to understand 'WHY' this worked in 2000 and 'WHY' it does not in 2008 !

What i want to do (in English) :

I want to insert into the table BB_HOLB999 the latest booking date for each customer and make use of the with ignore_dup_key to ensure the insert does not fail when it finds a duplicate customer number.

use userdata
go
drop table BB_HOLB999
go
create table BB_HOLB999 (
SYST varchar (10),
CREF decimal (10),
BDAT decimal(7,0))
go
create unique clustered index BI on BB_HOLB999(CREF,syst) with ignore_dup_key
go

Does the query below NOT inssert the data in descending order of booking date ??

e.g. for our example of customer 8458923 who has booking dates as follows :

990401
1050912
930604

i would expect the 1050912 record to be inserted first and then because of the ignore_dup_key i would expect the next 2 records to be ignored. However, i can run this multiple times and sometimes i would get 1050912 in the table BB_HOLBB999 and sometimes i will not, hence, my question, what is going on ??


insert into BB_HOLB999
select 'OSCAR' as [syst], CREF, bkbdat
from BB_TABLE join refdata.dbo.TDFBK on CREF=BKCREF
where BKCOST > 0
and BKPAX > 0
and BKINV1 > 0
and BKLINK is NULL
and bktype is NULL
and BKBSTS = ''
and BKLAPD is null
and BKBDAT<dmat
and (BKDEPT in ('20','23','16','50','10','40','52','80')
or (BKDEPT ='30' and BKPROD in ('Qp','39','8p','8q','8r','8s','8t','8u','8v','8w','8x','1A','9d','X2','AG','AH','AF','AI','6N','6A','8N','V1','U1')))
order by bkbdat desc
go

Interestingly, if i specify the insert for just customer 8458923, it is correct every time. ie change the above query to the one below (so the order by appears to work !):

insert into BB_HOLB999
select 'OSCAR' as [syst], CREF, bkbdat
from BB_TABLE join refdata.dbo.TDFBK on CREF=BKCREF
where BKCOST > 0
and BKPAX > 0
and BKINV1 > 0
and BKLINK is NULL
and bktype is NULL
and BKBSTS = ''
and BKLAPD is null
and BKBDAT<dmat
and (BKDEPT in ('20','23','16','50','10','40','52','80')
or (BKDEPT ='30' and BKPROD in ('Qp','39','8p','8q','8r','8s','8t','8u','8v','8w','8x','1A','9d','X2','AG','AH','AF','AI','6N','6A','8N','V1','U1')))
and bkcref=8458923
order by bkbdat desc
go

Post #959902
Posted Wednesday, July 28, 2010 3:34 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:25 PM
Points: 43,008, Visits: 36,164
leebuckman (7/28/2010)
All,

Thanks for the replies but i am still at a loss here. Let me explain further and simplify the query and exactly what i am trying to do. I know there is another way of doing this that does work but i want to understand 'WHY' this worked in 2000 and 'WHY' it does not in 2008 !


Why it worked in 2000 - The 2000 engine did honour the order by in various situations where it shouldn't have and didn't need to (including within views, subqueries and inserts). This was a bug, a shortcoming in the engine. Why it does not work in 2008 - changes to the query execution engine to allow it to work more optimally and it will not do sorts in cases where they are not necessary.

Remember that tables by definition DO NOT have an order. They are unordered sets of rows. Hence the order that data is inserted is irrelevent. The only time the presence of an order by affects an insert is when there's an identity column in the destination table. Then the Order By sets the sequence for that.

I want to insert into the table BB_HOLB999 the latest booking date for each customer and make use of the with ignore_dup_key to ensure the insert does not fail when it finds a duplicate customer number.


Now that's a lazy way of doing things if I ever heard of one. Why not just insert the rows that you want? That way you're not depending on undocumented behaviour which can and does change.

If you'd mentioned that in the first post, i could have given you an efficient, working solution then.

[b]Does the query below NOT inssert the data in descending order of booking date ??


No. Order of rows in a table is meaningless, so the SQL engine is under no obligation to do the order by, or to insert the rows in any order whatsoever. Especially the case if the query runs in parallel.

Try this, which just inserts the rows that you actually need.

insert into BB_HOLB999
SELECT syst, CREF, bkbdat FROM
(select 'OSCAR' as [syst], CREF, bkbdat, ROW_NUMBER() OVER (PARTITON BY CREF ORDER BY bkbdat DESC) AS BookingOrder
from BB_TABLE join refdata.dbo.TDFBK on CREF=BKCREF
where BKCOST > 0
and BKPAX > 0
and BKINV1 > 0
and BKLINK is NULL
and bktype is NULL
and BKBSTS = ''
and BKLAPD is null
and BKBDAT<dmat
and (BKDEPT in ('20','23','16','50','10','40','52','80')
or (BKDEPT ='30' and BKPROD in ('Qp','39','8p','8q','8r','8s','8t','8u','8v','8w','8x','1A','9d','X2','AG','AH','AF','AI','6N','6A','8N','V1','U1')))
) sub
WHERE BookingOrder = 1
go

That will insert just one row per CREF, the one with the latest booking date.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #959910
Posted Wednesday, July 28, 2010 3:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 9, 2010 7:02 AM
Points: 5, Visits: 13
Once again, thanks for your reply. The query was sent to me by another team member who was doing it this way. I personally said to group by the customer ref and insert the maximum date using the max function !

But the question remained, why did it work before and does not now, hence my post on here !

Thankyou for your answer, i will go back to my colleague and give them the explanantion as to why this is the case.

Best Regards

Lee
Post #959920
Posted Wednesday, July 28, 2010 4:10 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:25 PM
Points: 43,008, Visits: 36,164
leebuckman (7/28/2010)
But the question remained, why did it work before and does not now, hence my post on here !


Basically your colleague was depending on undocumented and incorrect behaviour that was fixed in a later version of SQL.

The only time you can depend on ORDER BY to order rows is when it's applied to the outer query in a SELECT statement, so not in inserts, not in views, not in subqueries.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #959923
Posted Wednesday, July 28, 2010 4:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:59 AM
Points: 11,194, Visits: 11,166
Some references for you to help you understand the things people have said:

The behavior of the IDENTITY function when used with SELECT INTO or INSERT .. SELECT queries that contain an ORDER BY clause (Microsoft Knowledge Base)

Blog entries by Conor Cunningham (currently a Principal Software Architect for the SQL Server Engine at Microsoft):
No Seatbelt - Expecting Order without ORDER BY
Ordering guarantees in SQL Server
TOP 100 Percent ORDER BY Considered Harmful

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #959929
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse