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

Update Statement - Differences between SQL Versions? Expand / Collapse
Author
Message
Posted Wednesday, September 19, 2012 7:57 PM
SSC-Addicted

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

Group: General Forum Members
Last Login: Monday, September 15, 2014 6:15 PM
Points: 480, Visits: 458
Hi,

I've been having a chat with Google and haven't been able to find an answer to it so here goes ...

I have 2 servers, one SQL 2008 R2 & the other SQL 2000, both using the same data, same SQL Statements & same Collation and they are returning different results.

The SELECT statement of the UPDATE returns multiple rows for each row that is updated. SQL 2008 R2
uses the values from the First row returned and SQL 2000 is using the values from the Last row returned for each update.

Example below ...
- If I run the code below on a SQL 2008R2 machine the result is 1.
- If I run the code below on a SQL 2005 machine the result is 1.
- If I run the code below on a SQL 2000 machine the result is 3.

CREATE TABLE #Test
(
Row INT IDENTITY (1, 1),
Result INT
)

CREATE TABLE #UpdateResult
(
Result INT
)

INSERT #UpdateResult
SELECT 0

INSERT #Test
SELECT 1

INSERT #Test
SELECT 2

INSERT #Test
SELECT 3

UPDATE #UpdateResult
SET Result = #Test.Result
FROM #Test

SELECT *
FROM #UpdateResult

DROP TABLE #UpdateResult
DROP TABLE #Test

Questions ...
- Huh?
- Is this a setting that is specified anywhere? If so can it be changed?



Post #1361704
Posted Thursday, September 20, 2012 12:45 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:49 PM
Points: 3,648, Visits: 5,326
I've seen this sort of "inconsistent" result on UPDATEs where you're applying multiple rows to a single row and I don't believe it is controlled by a setting.

You need to control it by specifying which row of #Test updates the #UpdatedResults table, like by using a WHERE clause.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1361756
Posted Thursday, September 20, 2012 5:15 PM
SSC-Addicted

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

Group: General Forum Members
Last Login: Monday, September 15, 2014 6:15 PM
Points: 480, Visits: 458
I had a feeling that would be the case. We're currently migrating an old Datawarehouse from SQL 2000 to SQL 2008 R2 and obviously we want the data to match across the systems.

Fixing the problem is easy enough but this means we are left with inconsistencies between the two systems which isn't ideal.




Post #1362380
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse