SQL 2000 vs. 2005 T-SQL output results vary

Question

SQL 2000 vs. 2005 T-SQL output results vary

Express12

SSCoach

Points: 18514
More actions
September 25, 2007 at 7:11 am

#374767

Run the sql script using sql server 2005 first and then later on 2000 server. The results are different:
DECLARE @tbl2 table (AccountID varchar(12), FundName varchar(100), Ticker varchar(10))
INSERT into @tbl2 SELECT 'j1','f1','t1'
SELECT * FROM @tbl2

DECLARE @tbl TABLE (AccountID varchar(12), FundName varchar(100), Ticker varchar(10) )
INSERT into @tbl SELECT 'j1','f1','t1'
UNION ALL
SELECT t1.* FROM @tbl2 t2 LEFT OUTER JOIN @tbl t1 on t1.ticker = t2.ticker
WHERE t1.ticker is null

SELECT * FROM @tbl
SQL SERVER 2005 RESULTS
AccountID       FundName        Ticker
j1              f1              t1
NULL            NULL            NULL
SQL SERVER 2000 RESULTS
AccountID       FundName        Ticker
j1              f1              t1
The results are different. Essentially, @tbl is not getting populated with the first "insert to @tbl" (pls see the one bolded with blue). Therefore, when we try to join the second part of the union all statement (pls see the one bolded with dark red color) - in this example @tbl2 with the first @tbl it results NULLS.
BT

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

using SQL2000's QA to connect to botha SQL2000 and SQL 2005, i get the same results that your SQL 2000 did; no null row; i changed SET ANSI_NULLS on and off on both servers, but didn't get a difference int he output.

could there be a different SET setting that is allowing that null row to get inserted?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 2

HI there,

I have tried this on SQL2005 comp mode 90 and 80 and on SQL2000 and I always get the same result.

(the one with the nulls)

I agree that perhaps there is a difference in your SET options.

Try looking at the server configuration on the two servers.

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Express12 SSCoach Points: 18514 More actions · Answer 3

We noticed that SQL 2000 SP4 retunrs same result as SQL 2005. BUT---- SQL 2000 SP3 (and less) return the different result set.

BT

ScottPletcher SSC Guru Points: 101098 More actions · Answer 4

Essentially, @tbl is not getting populated with the first "insert to @tbl"

The INSERT will not, and should not, be reflected in the table until the statement has finished. Therefore the result with the NULL row is correct.

The earlier versions may return different results because they recompile (? just a guess) at an intermediate step and then improperly "see" rows they shouldn't later.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".