Re: Are redundant fields ever appropriate?
From: <paul.vernon_at_ukk.ibmm.comm>
Date: Wed, 19 Dec 2001 18:34:51 +0000
Message-ID: <9vqmo4$t26$1_at_sp15at20.hursley.ibm.com>
Way to go Joe!
And if we're talking relations rather than tables it gets worse. E.g.
with RelVar having four columns (i, a, b, c) where i is an identity column
then
INSERT INTO RelVar (a, b, c) VALUES ('a1', 'b1', 'c1');
INSERT INTO RelVar (a, b, c) VALUES ('a1', 'b1', 'c1');
INSERT INTO RelVar (a, b, c) VALUES ('a2', 'b2', 'c2');
is not the same as
INSERT INTO RelVar (a, b, c)
VALUES ('a1', 'b1', 'c1')
, ('a1', 'b1', 'c1')
, ('a3', 'b3', 'c3')
The latter is trying to insert something that is not a relation as it has
duplicate rows, so in a proper relational system would fail.
And doing
INSERT INTO RelVar (a, b, c)
SELECT e, f,g FROM
TABLE(
VALUES (1, 'a1', 'b1', 'c1')
, (2, 'a1', 'b1', 'c1')
, (3, 'a3', 'b3', 'c3')
) AS (i, e, f, g)
would only insert two rows if SQL acted relationally.
I've never seen what's wrong with using
RANK() OVER(ORDER BY incomming_key) + (SELECT MAX(i) FROM RelVar)
to generate surrogate keys
Regards
Paul Vernon
Received on Wed Dec 19 2001 - 19:34:51 CET
Date: Wed, 19 Dec 2001 18:34:51 +0000
Message-ID: <9vqmo4$t26$1_at_sp15at20.hursley.ibm.com>
Way to go Joe!
And if we're talking relations rather than tables it gets worse. E.g.
with RelVar having four columns (i, a, b, c) where i is an identity column
then
INSERT INTO RelVar (a, b, c) VALUES ('a1', 'b1', 'c1');
INSERT INTO RelVar (a, b, c) VALUES ('a1', 'b1', 'c1');
INSERT INTO RelVar (a, b, c) VALUES ('a2', 'b2', 'c2');
is not the same as
INSERT INTO RelVar (a, b, c)
VALUES ('a1', 'b1', 'c1')
, ('a1', 'b1', 'c1')
, ('a3', 'b3', 'c3')
The latter is trying to insert something that is not a relation as it has
duplicate rows, so in a proper relational system would fail.
And doing
INSERT INTO RelVar (a, b, c)
SELECT e, f,g FROM
TABLE(
VALUES (1, 'a1', 'b1', 'c1')
, (2, 'a1', 'b1', 'c1')
, (3, 'a3', 'b3', 'c3')
) AS (i, e, f, g)
would only insert two rows if SQL acted relationally.
I've never seen what's wrong with using
RANK() OVER(ORDER BY incomming_key) + (SELECT MAX(i) FROM RelVar)
to generate surrogate keys
Regards
Paul Vernon
Received on Wed Dec 19 2001 - 19:34:51 CET