retrieving data incorrectly from a view using outer join

Hi, all,
I am having this problem with SQLServer 2000. Below is the script to
duplicate the problem. I appreciate if someone can help me on this or
confirm that this is the behavior of current SQLserver version.
Thank you for the help,
Shen
/********* script start ********************/
use northwind
GO
-- create tables and views
create table tbl1 (
newID int,
oldID int,
refID int)
create table tbl2(
newID int,
oldID int,
refID int)
GO
create view v_order
as
select
recordID = a.oldID,
orderID = a.newID,
refID = b.newID
from tbl1 a, tbl2 b
where a.refID *= b.oldID
GO
-- prepare data
insert into tbl1(oldID, newID, refID) values(1427 ,210504 ,1)
insert into tbl1(oldID, newID, refID) values(1953 ,210514 ,0)
insert into tbl1(oldID, newID, refID) values(646 ,210486 ,3)
insert into tbl1(oldID, newID, refID) values(650 ,210487 ,4)
insert into tbl1(oldID, newID, refID) values(749 ,210491 ,5)
insert into tbl2(oldID, newID, refID) values(1, 45280, null)
insert into tbl2(oldID, newID, refID) values(0, null, null)
insert into tbl2(oldID, newID, refID) values(3, 44701, null)
insert into tbl2(oldID, newID, refID) values(4, 44701, null)
insert into tbl2(oldID, newID, refID) values(5, 45827, null)
GO
-- now ready to see the problem
select recordID, orderID, refID from v_order
/**************** result is: ************
1427 210504 45280
1953 210514 NULL
646 210486 44701
650 210487 44701
749 210491 45827
****************************************
*/
select recordID, orderID, refID from v_order
where refID is null
/**************** result is: ************
1427 210504 NULL
1953 210514 NULL
646 210486 NULL
650 210487 NULL
749 210491 NULL
****************************************
*/
select recordID, orderID, refID from v_order
where refID is not null
/**************** result is: ************
1427 210504 45280
1953 210514 NULL
646 210486 44701
650 210487 44701
749 210491 45827
****************************************
*/
-- clean for this test
drop view v_order
drop table tbl1
drop table tbl2
GO
/*********** script end ******************/I didn't go through your script, but the old outer join syntax has some unex
pected behaviors, this is why it
will be removed in some future release. Did you consider using the modern ou
ter join syntax?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"S. Shen" <shshjun@.yahoo.com> wrote in message news:41cf02c.0405190724.748a8fd7@.posting.goog
le.com...
> Hi, all,
> I am having this problem with SQLServer 2000. Below is the script to
> duplicate the problem. I appreciate if someone can help me on this or
> confirm that this is the behavior of current SQLserver version.
> Thank you for the help,
> Shen
> /********* script start ********************/
> use northwind
> GO
> -- create tables and views
> create table tbl1 (
> newID int,
> oldID int,
> refID int)
> create table tbl2(
> newID int,
> oldID int,
> refID int)
> GO
> create view v_order
> as
> select
> recordID = a.oldID,
> orderID = a.newID,
> refID = b.newID
> from tbl1 a, tbl2 b
> where a.refID *= b.oldID
> GO
> -- prepare data
> insert into tbl1(oldID, newID, refID) values(1427 ,210504 ,1)
> insert into tbl1(oldID, newID, refID) values(1953 ,210514 ,0)
> insert into tbl1(oldID, newID, refID) values(646 ,210486 ,3)
> insert into tbl1(oldID, newID, refID) values(650 ,210487 ,4)
> insert into tbl1(oldID, newID, refID) values(749 ,210491 ,5)
> insert into tbl2(oldID, newID, refID) values(1, 45280, null)
> insert into tbl2(oldID, newID, refID) values(0, null, null)
> insert into tbl2(oldID, newID, refID) values(3, 44701, null)
> insert into tbl2(oldID, newID, refID) values(4, 44701, null)
> insert into tbl2(oldID, newID, refID) values(5, 45827, null)
> GO
> -- now ready to see the problem
> select recordID, orderID, refID from v_order
> /**************** result is: ************
> 1427 210504 45280
> 1953 210514 NULL
> 646 210486 44701
> 650 210487 44701
> 749 210491 45827
> ****************************************
*/
> select recordID, orderID, refID from v_order
> where refID is null
> /**************** result is: ************
> 1427 210504 NULL
> 1953 210514 NULL
> 646 210486 NULL
> 650 210487 NULL
> 749 210491 NULL
> ****************************************
*/
> select recordID, orderID, refID from v_order
> where refID is not null
> /**************** result is: ************
> 1427 210504 45280
> 1953 210514 NULL
> 646 210486 44701
> 650 210487 44701
> 749 210491 45827
> ****************************************
*/
> -- clean for this test
> drop view v_order
> drop table tbl1
> drop table tbl2
> GO
> /*********** script end ******************/

retrieving data incorrectly from a view using outer join

Hi, all,
I am having this problem with SQLServer 2000. Below is the script to
duplicate the problem. I appreciate if someone can help me on this or
confirm that this is the behavior of current SQLserver version.
Thank you for the help,
Shen
/********* script start ********************/
use northwind
GO
-- create tables and views
create table tbl1 (
newID int,
oldID int,
refID int)
create table tbl2(
newID int,
oldID int,
refID int)
GO
create view v_order
as
select
recordID = a.oldID,
orderID = a.newID,
refID = b.newID
from tbl1 a, tbl2 b
where a.refID *= b.oldID
GO
-- prepare data
insert into tbl1(oldID, newID, refID) values(1427 ,210504 ,1)
insert into tbl1(oldID, newID, refID) values(1953 ,210514 ,0)
insert into tbl1(oldID, newID, refID) values(646 ,210486 ,3)
insert into tbl1(oldID, newID, refID) values(650 ,210487 ,4)
insert into tbl1(oldID, newID, refID) values(749 ,210491 ,5)
insert into tbl2(oldID, newID, refID) values(1, 45280, null)
insert into tbl2(oldID, newID, refID) values(0, null, null)
insert into tbl2(oldID, newID, refID) values(3, 44701, null)
insert into tbl2(oldID, newID, refID) values(4, 44701, null)
insert into tbl2(oldID, newID, refID) values(5, 45827, null)
GO
-- now ready to see the problem
select recordID, orderID, refID from v_order
/**************** result is: ************
1427 210504 45280
1953 210514 NULL
646 210486 44701
650 210487 44701
749 210491 45827
*****************************************/
select recordID, orderID, refID from v_order
where refID is null
/**************** result is: ************
1427 210504 NULL
1953 210514 NULL
646 210486 NULL
650 210487 NULL
749 210491 NULL
*****************************************/
select recordID, orderID, refID from v_order
where refID is not null
/**************** result is: ************
1427 210504 45280
1953 210514 NULL
646 210486 44701
650 210487 44701
749 210491 45827
*****************************************/
-- clean for this test
drop view v_order
drop table tbl1
drop table tbl2
GO
/*********** script end ******************/I didn't go through your script, but the old outer join syntax has some unexpected behaviors, this is why it
will be removed in some future release. Did you consider using the modern outer join syntax?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"S. Shen" <shshjun@.yahoo.com> wrote in message news:41cf02c.0405190724.748a8fd7@.posting.google.com...
> Hi, all,
> I am having this problem with SQLServer 2000. Below is the script to
> duplicate the problem. I appreciate if someone can help me on this or
> confirm that this is the behavior of current SQLserver version.
> Thank you for the help,
> Shen
> /********* script start ********************/
> use northwind
> GO
> -- create tables and views
> create table tbl1 (
> newID int,
> oldID int,
> refID int)
> create table tbl2(
> newID int,
> oldID int,
> refID int)
> GO
> create view v_order
> as
> select
> recordID = a.oldID,
> orderID = a.newID,
> refID = b.newID
> from tbl1 a, tbl2 b
> where a.refID *= b.oldID
> GO
> -- prepare data
> insert into tbl1(oldID, newID, refID) values(1427 ,210504 ,1)
> insert into tbl1(oldID, newID, refID) values(1953 ,210514 ,0)
> insert into tbl1(oldID, newID, refID) values(646 ,210486 ,3)
> insert into tbl1(oldID, newID, refID) values(650 ,210487 ,4)
> insert into tbl1(oldID, newID, refID) values(749 ,210491 ,5)
> insert into tbl2(oldID, newID, refID) values(1, 45280, null)
> insert into tbl2(oldID, newID, refID) values(0, null, null)
> insert into tbl2(oldID, newID, refID) values(3, 44701, null)
> insert into tbl2(oldID, newID, refID) values(4, 44701, null)
> insert into tbl2(oldID, newID, refID) values(5, 45827, null)
> GO
> -- now ready to see the problem
> select recordID, orderID, refID from v_order
> /**************** result is: ************
> 1427 210504 45280
> 1953 210514 NULL
> 646 210486 44701
> 650 210487 44701
> 749 210491 45827
> *****************************************/
> select recordID, orderID, refID from v_order
> where refID is null
> /**************** result is: ************
> 1427 210504 NULL
> 1953 210514 NULL
> 646 210486 NULL
> 650 210487 NULL
> 749 210491 NULL
> *****************************************/
> select recordID, orderID, refID from v_order
> where refID is not null
> /**************** result is: ************
> 1427 210504 45280
> 1953 210514 NULL
> 646 210486 44701
> 650 210487 44701
> 749 210491 45827
> *****************************************/
> -- clean for this test
> drop view v_order
> drop table tbl1
> drop table tbl2
> GO
> /*********** script end ******************/

retrieving data incorrectly from a view using outer join

Hi, all,
I am having this problem with SQLServer 2000. Below is the script to
duplicate the problem. I appreciate if someone can help me on this or
confirm that this is the behavior of current SQLserver version.
Thank you for the help,
Shen
/********* script start ********************/
use northwind
GO
-- create tables and views
create table tbl1 (
newID int,
oldID int,
refID int)
create table tbl2(
newID int,
oldID int,
refID int)
GO
create view v_order
as
select
recordID = a.oldID,
orderID = a.newID,
refID = b.newID
from tbl1 a, tbl2 b
where a.refID *= b.oldID
GO
-- prepare data
insert into tbl1(oldID, newID, refID) values(1427,210504,1)
insert into tbl1(oldID, newID, refID) values(1953,210514,0)
insert into tbl1(oldID, newID, refID) values(646,210486,3)
insert into tbl1(oldID, newID, refID) values(650,210487,4)
insert into tbl1(oldID, newID, refID) values(749,210491,5)
insert into tbl2(oldID, newID, refID) values(1, 45280, null)
insert into tbl2(oldID, newID, refID) values(0, null, null)
insert into tbl2(oldID, newID, refID) values(3, 44701, null)
insert into tbl2(oldID, newID, refID) values(4, 44701, null)
insert into tbl2(oldID, newID, refID) values(5, 45827, null)
GO
-- now ready to see the problem
select recordID, orderID, refID from v_order
/**************** result is: ************
142721050445280
1953210514NULL
64621048644701
65021048744701
74921049145827
*****************************************/
select recordID, orderID, refID from v_order
where refID is null
/**************** result is: ************
1427210504NULL
1953210514NULL
646210486NULL
650210487NULL
749210491NULL
*****************************************/
select recordID, orderID, refID from v_order
where refID is not null
/**************** result is: ************
142721050445280
1953210514NULL
64621048644701
65021048744701
74921049145827
*****************************************/
-- clean for this test
drop view v_order
drop table tbl1
drop table tbl2
GO
/*********** script end ******************/
I didn't go through your script, but the old outer join syntax has some unexpected behaviors, this is why it
will be removed in some future release. Did you consider using the modern outer join syntax?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"S. Shen" <shshjun@.yahoo.com> wrote in message news:41cf02c.0405190724.748a8fd7@.posting.google.co m...
> Hi, all,
> I am having this problem with SQLServer 2000. Below is the script to
> duplicate the problem. I appreciate if someone can help me on this or
> confirm that this is the behavior of current SQLserver version.
> Thank you for the help,
> Shen
> /********* script start ********************/
> use northwind
> GO
> -- create tables and views
> create table tbl1 (
> newID int,
> oldID int,
> refID int)
> create table tbl2(
> newID int,
> oldID int,
> refID int)
> GO
> create view v_order
> as
> select
> recordID = a.oldID,
> orderID = a.newID,
> refID = b.newID
> from tbl1 a, tbl2 b
> where a.refID *= b.oldID
> GO
> -- prepare data
> insert into tbl1(oldID, newID, refID) values(1427 ,210504 ,1)
> insert into tbl1(oldID, newID, refID) values(1953 ,210514 ,0)
> insert into tbl1(oldID, newID, refID) values(646 ,210486 ,3)
> insert into tbl1(oldID, newID, refID) values(650 ,210487 ,4)
> insert into tbl1(oldID, newID, refID) values(749 ,210491 ,5)
> insert into tbl2(oldID, newID, refID) values(1, 45280, null)
> insert into tbl2(oldID, newID, refID) values(0, null, null)
> insert into tbl2(oldID, newID, refID) values(3, 44701, null)
> insert into tbl2(oldID, newID, refID) values(4, 44701, null)
> insert into tbl2(oldID, newID, refID) values(5, 45827, null)
> GO
> -- now ready to see the problem
> select recordID, orderID, refID from v_order
> /**************** result is: ************
> 1427 210504 45280
> 1953 210514 NULL
> 646 210486 44701
> 650 210487 44701
> 749 210491 45827
> *****************************************/
> select recordID, orderID, refID from v_order
> where refID is null
> /**************** result is: ************
> 1427 210504 NULL
> 1953 210514 NULL
> 646 210486 NULL
> 650 210487 NULL
> 749 210491 NULL
> *****************************************/
> select recordID, orderID, refID from v_order
> where refID is not null
> /**************** result is: ************
> 1427 210504 45280
> 1953 210514 NULL
> 646 210486 44701
> 650 210487 44701
> 749 210491 45827
> *****************************************/
> -- clean for this test
> drop view v_order
> drop table tbl1
> drop table tbl2
> GO
> /*********** script end ******************/

Retrieving a distal query plan

Hi!
I have a consultant onsite at a client, and she's sending back
pictures of the query plan for a script with a performance issue. These
are useful, as they're turning out somewhat different from the
development and test machines. But, is there any way we can pass the
query plan so that I can look at it in Query Analyzer's fine query plan
renderer?
Thanks!
IonIon,
SQL Server 2005 has this feature, but not SQL Server 2000.
In SQL Server 2005 Management Studio, you can save and
reopen query plans saved as .sqlplan files (which are XML files).
There might be a third-party tool that can render the graphical plan
from the SHOWPLAN_TEXT text plan - at least it's conceivable -
but I don't know of one.
Steve Kass
Drew University
ionFreeman@.gmail.com wrote:

>Hi!
> I have a consultant onsite at a client, and she's sending back
>pictures of the query plan for a script with a performance issue. These
>are useful, as they're turning out somewhat different from the
>development and test machines. But, is there any way we can pass the
>query plan so that I can look at it in Query Analyzer's fine query plan
>renderer?
>Thanks!
>Ion
>
>|||I think I heard that you can use SQL Server 2005 Management Studio
against a 2000 database, and that it will allow you to save the plan
and send it as with any 2005 database.
Roy Harvey
Beacon Falls, CT
On Fri, 28 Apr 2006 19:51:02 -0400, Steve Kass <skass@.drew.edu> wrote:
>Ion,
>SQL Server 2005 has this feature, but not SQL Server 2000.
>In SQL Server 2005 Management Studio, you can save and
>reopen query plans saved as .sqlplan files (which are XML files).
>There might be a third-party tool that can render the graphical plan
>from the SHOWPLAN_TEXT text plan - at least it's conceivable -
>but I don't know of one.
>Steve Kass
>Drew University
>
>ionFreeman@.gmail.com wrote:
>|||Thanks, guys. I'll see where this brings me.