Triggers and String concatination

#11

--CELKO--

Quote:

Why did you store a computed column in a table?
Have you ever been to client for performnace tunning, are you familiar how

SQL Server estimates the number of rows satisfying this condition using the
column statistics ?

What do you think about those approaches?
WHERE UnitPrice * Quantity > 100

and ADDING a computed column on AS UnitPrice * Quantity

SQL Server will detect the use of the computed column in the second example
and it will create statistics on the computed column. The statistics will
allow the optimizer to determine the appropriate cardinality estimation on
the filter.

"--CELKO--" <jcelko212 (AT) earthlink (DOT) net> wrote

Quote:

Why did you store a computed column in a table? SQL is a declarative
language. But you are using procedural code all over the place, in a
TRIGGER and a UDF! You are still thinking as if you were writing for
magnetic tape files or punch cards. You even use the word "record"
because your mindset is all wrong.

There is no such thing as an IDENTITY field. First of all, a column
is nothing like a field. It is a proprietary, non-relational table
property. Programmers who do not know RDBMS often use it to get a
"record number" so that they can make their SQL look like a
sequential file -- magnetic tape files or punch cards.

The DDL skeleton would be something like this:

CREATE TABLE Clients
(client_id CHAR(7) NOT NULL PRIMARY KEY
CHECK(client_id LIKE '<pattern>'),
signup_date DATE DEFAULT CURRENT_DATE NOT NULL,
(<build card number here>) AS card_nbr,
etc);

You need a way of getting a client identifier that can be validated
and verified. IDENTITY has neither and it cannot even give you
consecutive numbers. I would not expose the client id in the card
number -- do you write your PIN on your bank card?

You need to learn how to follow ISO-11179 data element naming
conventions and formatting rules. Temporal data should use ISO-8601
formats. Code should be in Standard SQL as much as possible and not
local dialect.

#12

Cypher (cypher (AT) cypher (DOT) com) writes:

Quote:

Thanks, I needed that! How did you know I am old enough to have used
punched cards on a DEC System 10? But seriously:

No one needs Joe Celko's meaningless posts.

Quote:

- Consecutive numbers are not a requirement, but after the testing is
done and the database is on a production server, I expect it will be. I
was somewhat surprised by the SQL Server behavior because I expected the
operation to be rolled back on error.

The fact that IDENTITY is not contiguous is actually a feature: this permits
parallel processes to insert rows without blocking each other.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#13

Good point Uri.

You can also persist the computed column so long as its deterministic and
index it, saving you having to store the data twice.

In data modelling terms VIN should actually be a computed column - most
encoding "keys" should be because they should be stored in their constituent
parts.

Tony.

"Uri Dimant" <urid (AT) iscar (DOT) co.il> wrote

Quote:

--CELKO--
Why did you store a computed column in a table?
Have you ever been to client for performnace tunning, are you familiar
how SQL Server estimates the number of rows satisfying this condition
using the column statistics ?

What do you think about those approaches?
WHERE UnitPrice * Quantity > 100

and ADDING a computed column on AS UnitPrice * Quantity

SQL Server will detect the use of the computed column in the second
example and it will create statistics on the computed column. The
statistics will allow the optimizer to determine the appropriate
cardinality estimation on the filter.

"--CELKO--" <jcelko212 (AT) earthlink (DOT) net> wrote in message
news:687c5bf6-50b5-4a56-ac28-30a31d7c9898 (AT) 35g2000yqa (DOT) googlegroups.com...
Why did you store a computed column in a table? SQL is a declarative
language. But you are using procedural code all over the place, in a
TRIGGER and a UDF! You are still thinking as if you were writing for
magnetic tape files or punch cards. You even use the word "record"
because your mindset is all wrong.

There is no such thing as an IDENTITY field. First of all, a column
is nothing like a field. It is a proprietary, non-relational table
property. Programmers who do not know RDBMS often use it to get a
"record number" so that they can make their SQL look like a
sequential file -- magnetic tape files or punch cards.

The DDL skeleton would be something like this:

CREATE TABLE Clients
(client_id CHAR(7) NOT NULL PRIMARY KEY
CHECK(client_id LIKE '<pattern>'),
signup_date DATE DEFAULT CURRENT_DATE NOT NULL,
(<build card number here>) AS card_nbr,
etc);

You need a way of getting a client identifier that can be validated
and verified. IDENTITY has neither and it cannot even give you
consecutive numbers. I would not expose the client id in the card
number -- do you write your PIN on your bank card?

You need to learn how to follow ISO-11179 data element naming
conventions and formatting rules. Temporal data should use ISO-8601
formats. Code should be in Standard SQL as much as possible and not
local dialect.

#14

Quote:

I needed that! How did you know I am old enough to have used punched cards on a DEC System 10?

Because I am old enough to have to remember to say "main Storage"
instead of "core memory"! And I still think that PIP was the coolest
utility program ever.

Quote:

- I need the computed field [sic] in there because another dumb ID card printing program will use it to print an ID card.

One of my standard rants is to learn the differences between a field
and a column. Fields are physical things inside records, records are
inside files. Things are sequential and contiguous. The data has
meaning from the host program using it -- that is, "READ a,b,c FROM
FileX;" is not the same as "READ c,a,b FROM FileX;" but "SELECT a,b,c
FROM TableX;" returns the same data as "SELECT c,b,a FROM TableX;"
since order does not matter. Columns,rows and entire tables do not
have to be a physical unit of storage in the database.

Quote:

Also it is a foreign key field [sic] in other tables that contain data about the client. The id card is used later to
retrieve data using either a barcode or a Mag card reader.

The REFERENCES should not be a problem. But having client data spread
over multiple table could be a real problem. I would assume that the
client_id is the key and the card number is not. You identify an
automobile by its VIN, not by the local parking space card number. You
can re-issue a lost card; you cannot issue a new VIN because it is a
real identifier. This is another important difference between files
and RDBMS; there are a lot more!

In the old file systems, we had data all over the place; the goal of
DBMS was to get it all in one place. Be careful and do not commit the
errors of denormalization and attribute splitting.

Quote:

- I use the term IDENTITY because that is what MS calls an auto-increment field [sic].

Actually, it is a count of the PHYSICAL insertion attempts at the
table level. A rollback will not re-set the IDENTITY, so you get gaps,
this is soooo non-relational, it is not portable, etc.

Quote:

- Consecutive numbers are not a requirement, but after the testing is done and the database is on a production server, I expect it will be.

I would think that consecutive numbers area bad idea. They expose
information in violation of basic security requirements -- i.e.
passwords and identification codes ought to be hard to guess, and easy
to validate (check digits and regular expressions are good) and
verified by a trusted source.

When I have had to do this kind of thing, I generate a list of
unpredictable numbers and then issue them with a sign-out system. I
am also old enough to have wor5ke4d in the Cold War Era

Quote:

- This is not my day job but I am beginning to like it :-)

Have you see the IT market today? Keep the day job; it is important.
Even if the day job includes the technical term "Would you like fries
with that?"

On a more serious note, get a copy of my THINKING IN SETS book.
Getting over to RDBMS and SQL is a change of mindset from files and
procedural programming. Remember what a bitch it was to learn
recursion, weak and strong typing, etc.? Much as I hate the cliche,
SQL is another major paradigm shift.

#15

Quote:

That reminds me - the left/right pair of the nested sets model you popularize cannot be "validated" and "verified" because on day 1 the left/right pair might be 12,1 and the next day or even the next minute it might be 12,5; so, any table using the left/right pairing is now disconnected and orphaned.

No, on Day #1, the hierarchical pair (12, 1) is rejected because it
fails validation. The pair must conform to the constraint (lft <
rgt). As I have said for over ten years, you should do this in Full
Standard SQL-99 with a CHECK() or CREATE ASSERTION. In SQL Server and
other lesser SQLs, you can fake it with a VIEW, like this skeleton

CREATE TABLR RawTree
(node_id char(10) DEFAULT 'VACANT' NOT NULL
REFERENCES Nodes (node_id)
ON UPDATE CASCADE
ON DELETE SET DEFAULT,
-- PRIMARY KEY () -- business rules allow node_id or (lft, rgt),
lft INTEGER NOT NULL CHECK (lft > 0),
rgt INTEGER NOT NULL CHECK (rgt > 1),
CONSTRAINT valid_pair_ordering
CHECK (lft < rgt),
CONSTRAINT valid_pair_spacing -- business rules for no gaps?
CHECK ((rgt - lft) % 2 = 0);

Now fake the CREATE ASSERTION:

CREATE VIEW ValidatedTree (node_id, lft, rgt)
AS
SELECT node_id, lft, rgt
FROM Raw_Tree
WHERE NOT EXISTS -- no range overlaps
(SELECT *
FROM Raw_Tree AS T1, Raw_Tree AS T2
WHERE T1.lft BETWEEN T2.lft AND T2.rgt
AND T1.rgt BETWEEN T2.lft AND T2.rgt
AND (T1.lft <> T2.lft OR T1.rgt <> T2.rgt));

Warning, if you get directly the Raw_Tree base table, you can mess up
things. You need DDL, DML and DCL working together. For kicks, write
the same validations for the adjacency list model. The best I was able
to do was a trigger with procedural code to traverse the tree looking
for cycles.

Quote:

The stable and unchangeable value returned by IDENTITY on insert can easily be verified back - once inserted the value can never change.

Actually, this forum has a quite a few postings from forgetting to
set the right flags in BCP and scrambling the IDENTITY values. There
is no "trusted source" for verification since the IDENTITY is a local
table property. Compare this to the manufacturer for a part number

Quote:

Gaps occur only on rollbacks but the benefits in concurrency massively out weigh this problem; the architect needs to balance that with a "real" need for an incrementor without gaps in which case they must destroy concurrency and use MAX( x ) + 1 with some severe locking.

I have the CREATE SEQUENCE statement when I am using DB2, Oracle,
etc. I do not know if/when SQL Server will add it, but we did get the
MERGE and ANSI/ISO temporal stuff a few decades late, so I have hope.

#16

Quote:

No, on Day #1, the hierarchical pair (12, 1) is rejected because it
fails validation. The pair must conform to the constraint (lft
rgt). As I have said for over ten years, you should do this in Full
Standard SQL-99 with a CHECK() or CREATE ASSERTION. In SQL Server and
other lesser SQLs, you can fake it with a VIEW, like this skeleton

On Day #1, left is 1, right is 12

On Day #2 the same row the left, right columns are changed to left = 5,
right = 12

In another table that holds commission against the hierarchy at the time
holds the composite foreign key left = 1, right = 12

That row no longer exists in the hierarchy, worse still - it may be an
entirely different row.

That is the problem when you allow a key to be changeable which when you
treat the coordinate in the nested set a key happens because the hierarchy
is fluid and YOU CANNOT VALIDATE / VERIFY IT because it changes - there is
no getting away from that.

In the enumerated path model you simply use start/end windowing and the
problem is resolved, you can always determine the hierarchy at a given time.

Quote:

Actually, this forum has a quite a few postings from forgetting to
set the right flags in BCP and scrambling the IDENTITY values. There
is no "trusted source" for verification since the IDENTITY is a local
table property. Compare this to the manufacturer for a part number

Be very careful pointing out stuff as fact stuff you don't understand and
are just repeating because you've seen a post and misunderstood it.

You cannot use BCP or BULK INSERT for the method you propose, the MAX( col )
+ 1; with that method you are limited to inserting one row at a time -
entirely serialised across the entire database - with BCP and BULK INSERT
you can have many parallel streams and have a very high insert throughput.

The manufacture part number verifies back to a manufacturer database where
the incremental numbering method may be anything, IDENTITY, MAX( x ) + 1,
next number or manually dreamt up by a person - but, it will be one of them.

You lost this battle years ago, but I'm more than prepared to go on - shall
we?

Quote:

I have the CREATE SEQUENCE statement when I am using DB2, Oracle,
etc. I do not know if/when SQL Server will add it, but we did get the
MERGE and ANSI/ISO temporal stuff a few decades late, so I have hope.

The CREATE SEQUENCE has limited scope; where you say IDENTITY is limited to
a local table property; the CREATE SEQUENCE is from memory to a database -
not that much difference; it is certainly not unlimited and applicable off
that machine running DB2, Oracle etc...

Whilst an incremental sequence without gaps would be useful there is no mass
call for it because a) we have IDENTITY which satisfies most requirements
and b) there are plenty of examples of next number, max( x ) + 1 - some
actually document the concurrency consequences.

Shall we now talk about concurrency cleko??

--ROGGIE--

"--CELKO--" <jcelko212 (AT) earthlink (DOT) net> wrote

Quote:

That reminds me - the left/right pair of the nested sets model you
popularize cannot be "validated" and "verified" because on day 1 the
left/right pair might be 12,1 and the next day or even the next minute
it might be 12,5; so, any table using the left/right pairing is now
disconnected and orphaned.

No, on Day #1, the hierarchical pair (12, 1) is rejected because it
fails validation. The pair must conform to the constraint (lft
rgt). As I have said for over ten years, you should do this in Full
Standard SQL-99 with a CHECK() or CREATE ASSERTION. In SQL Server and
other lesser SQLs, you can fake it with a VIEW, like this skeleton

CREATE TABLR RawTree
(node_id char(10) DEFAULT 'VACANT' NOT NULL
REFERENCES Nodes (node_id)
ON UPDATE CASCADE
ON DELETE SET DEFAULT,
-- PRIMARY KEY () -- business rules allow node_id or (lft, rgt),
lft INTEGER NOT NULL CHECK (lft > 0),
rgt INTEGER NOT NULL CHECK (rgt > 1),
CONSTRAINT valid_pair_ordering
CHECK (lft < rgt),
CONSTRAINT valid_pair_spacing -- business rules for no gaps?
CHECK ((rgt - lft) % 2 = 0);

Now fake the CREATE ASSERTION:

CREATE VIEW ValidatedTree (node_id, lft, rgt)
AS
SELECT node_id, lft, rgt
FROM Raw_Tree
WHERE NOT EXISTS -- no range overlaps
(SELECT *
FROM Raw_Tree AS T1, Raw_Tree AS T2
WHERE T1.lft BETWEEN T2.lft AND T2.rgt
AND T1.rgt BETWEEN T2.lft AND T2.rgt
AND (T1.lft <> T2.lft OR T1.rgt <> T2.rgt));

Warning, if you get directly the Raw_Tree base table, you can mess up
things. You need DDL, DML and DCL working together. For kicks, write
the same validations for the adjacency list model. The best I was able
to do was a trigger with procedural code to traverse the tree looking
for cycles.

The stable and unchangeable value returned by IDENTITY on insert can
easily be verified back - once inserted the value can never change.

Actually, this forum has a quite a few postings from forgetting to
set the right flags in BCP and scrambling the IDENTITY values. There
is no "trusted source" for verification since the IDENTITY is a local
table property. Compare this to the manufacturer for a part number

Gaps occur only on rollbacks but the benefits in concurrency massively
out weigh this problem; the architect needs to balance that with a
"real" need for an incrementor without gaps in which case they must
destroy concurrency and use MAX( x ) + 1 with some severe locking.

I have the CREATE SEQUENCE statement when I am using DB2, Oracle,
etc. I do not know if/when SQL Server will add it, but we did get the
MERGE and ANSI/ISO temporal stuff a few decades late, so I have hope.

#17

Tony Rogerson (tonyrogerson (AT) torver (DOT) net) writes:

Quote:

You cannot use BCP or BULK INSERT for the method you propose, the MAX(col)
+ 1; with that method you are limited to inserting one row at a time -

This is in correct. You can certinly insert many rows in one go.

However, until you have committed, no other process can insert, so
there is serialisation. Which may be utterly bad, or simply no issue.

When it is an issue, IDENTITY is certainly worth considering. But
where there are no concurrency issues in sight there is little reason
to use IDENTITY, since there are a couple of usability problems with
it - you see them on the newsgroups all the time.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

#18

Quote:

You can certainly insert many rows in one go.

The fact that IDENTITY has to queue the rows in a multi-row insertion
in a non-deterministic fashion has been a big problem for me. It
means there is no hope of validation.

I need to look at the Standard CREATE SEQUENCE statement in depth to
see if that works. But I also need to play with

INSERT INTO Foobar (foo_seq, ..)
VALUES (
(SELECT COALESCE (MAX(Foobar.foo_seq), 0)
+ ROW_NUMBER() OVER (ORDER BY some_sequencing_columns))
FROM NewFoobar)
...);

or worse

#19

Quote:

This is in correct. You can certinly insert many rows in one go.

You miss read what I was saying Erland.

Unless you use an instead of trigger combined with a cursor you cannot
calculate the next number because you need to physically do a SELECT max(
id ) + 1 query.

You can imagine the performance of that!

Quote:

However, until you have committed, no other process can insert, so
there is serialisation. Which may be utterly bad, or simply no issue.

Correct.

Quote:

When it is an issue, IDENTITY is certainly worth considering. But
where there are no concurrency issues in sight there is little reason
to use IDENTITY, since there are a couple of usability problems with
it - you see them on the newsgroups all the time.

Except the added complexity I mention above, which frankly comes with its
own issues like the TSQL developer fully understanding triggers which a lot
of people don't.

Tony.

"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote

Quote:

Tony Rogerson (tonyrogerson (AT) torver (DOT) net) writes:
You cannot use BCP or BULK INSERT for the method you propose, the
MAX(col)
+ 1; with that method you are limited to inserting one row at a time -

This is in correct. You can certinly insert many rows in one go.

However, until you have committed, no other process can insert, so
there is serialisation. Which may be utterly bad, or simply no issue.

When it is an issue, IDENTITY is certainly worth considering. But
where there are no concurrency issues in sight there is little reason
to use IDENTITY, since there are a couple of usability problems with
it - you see them on the newsgroups all the time.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000:
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#20

Quote:

The fact that IDENTITY has to queue the rows in a multi-row insertion
in a non-deterministic fashion has been a big problem for me. It
means there is no hope of validation.

Obviously, the systems you've worked on in the past have been very small
chunks of data.

If you are loading lots of data you need parallel streams, absolutely in a
parallel load scenario the number given is not deterministic but often we
don't care, after all the ID 12 given by the issuing database to the email
blah (AT) xyz (DOT) com AT INITIAL INSERT is meaningless so long as it NEVER changes.

Quote:

see if that works. But I also need to play with

INSERT INTO Foobar (foo_seq, ..)
VALUES (
(SELECT COALESCE (MAX(Foobar.foo_seq), 0)
+ ROW_NUMBER() OVER (ORDER BY some_sequencing_columns))
FROM NewFoobar)
..);

Remember to check it on each product specifically for concurrency
implications - I will be checking your research for errors. I already know
the outcome on SQL Server.

--ROGGIE--

"--CELKO--" <jcelko212 (AT) earthlink (DOT) net> wrote

Quote:

You can certainly insert many rows in one go.

The fact that IDENTITY has to queue the rows in a multi-row insertion
in a non-deterministic fashion has been a big problem for me. It
means there is no hope of validation.

I need to look at the Standard CREATE SEQUENCE statement in depth to
see if that works. But I also need to play with

INSERT INTO Foobar (foo_seq, ..)
VALUES (
(SELECT COALESCE (MAX(Foobar.foo_seq), 0)
+ ROW_NUMBER() OVER (ORDER BY some_sequencing_columns))
FROM NewFoobar)
..);

or worse