Hi Guys,

A quick question about the 'GeneratedWhen' please.

I notice in the examples in the SQL manual for all of the examples where a table have been created, the incremental ID has been created using 'Always'

For example...

CREATE TABLE "Customer"
(
"ID" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1), etc etc

Should it not be "GENERATED BY DEFAULT AS IDENTITY" ?

I noticed on GUID fields created with 'GENERATED ALWAYS'  - that the GUID does change everytime any field is changed for the record and same for TIMESTAMPS (where the default is set to DEFAULT CURRENT_TIMESTAMP / DEFAULT CURRENT_GUID )

Can I get some clarification on if this correct as far as how I should be using GENERATED BY DEFAULT and GENERATED ALWAYS just to be 100% sure.

Always.... The generated column is always updated when a row is
inserted or updated, overwriting any value that may exist for
the column

By Default.... The generated column is only updated when a row is
inserted if no value currently exists for the column (NULL)

Thanks,

Phil.

Philip,

<< Should it not be "GENERATED BY DEFAULT AS IDENTITY" ? >>

You can do it either way - it's up to your needs.

<< I noticed on GUID fields created with 'GENERATED ALWAYS'  - that the GUID
does change everytime any field is changed for the record and same for
TIMESTAMPS (where the default is set to DEFAULT CURRENT_TIMESTAMP / DEFAULT
CURRENT_GUID ) >>

DEFAULT is not equal to GENERATED, and GENERATED <Expression> is not equal
to GENERATED..IDENTITY.

<< Can I get some clarification on if this correct as far as how I should be
using GENERATED BY DEFAULT and GENERATED ALWAYS just to be 100% sure. >>

Yes, that is correct, with one clarification: IDENTITY columns are *only*
generated during INSERT operations.  (Generated) Expression columns are
generated for INSERT and UPDATE operations.

Tim Young
Elevate Software
www.elevatesoft.com