I would really like to have a "unique integer" data type for a database:

ALTER TABLE MyTable
ADD COLUMN MyCounter UID,

When the database was created this function would be created with a value of 1 each time it was called, from any SQL Statement or Script it would return "i+1" and increment itself.

Sort of just an AutoInc for the whole database.

Such a field is useful as it allows any record in a database to be located with a "Find this UID" command, since a Script can iterate the Information.Tables table for table-names and call "SELECT * FROM ? WHERE MyCounter = ?" substituting in the table-name and UID value until it returns a record.

--

I realise that such fields would be complicated in a REPLICATION situation where there were multiple copies of a single database ... I wouldn't expect to use them without serious adaptation in such a situation ... but they would be useful in quite a few situations for me.

Adam,

> I would really like to have a "unique integer" data type for a database:
>
> ALTER TABLE MyTable
> ADD COLUMN MyCounter UID,
>
> When the database was created this function would be created with a value of 1 each time it was called, from any SQL
Statement or Script it would return "i+1" and increment itself.
>
> Sort of just an AutoInc for the whole database.

Is there a reason why you wouldn't just create this function yourself?

John

Adam,

<< I would really like to have a "unique integer" data type for a database:

ALTER TABLE MyTable
ADD COLUMN MyCounter UID,

When the database was created this function would be created with a value
of 1 each time it was called, from any SQL Statement or Script it would
return "i+1" and increment itself.

Sort of just an AutoInc for the whole database. >>

The problem with this approach and EDB, as it currently exists, is that EDB
doesn't have a place to store "database-level" data.  You might think that
the database catalog is that place, but that is really not the case.  The
database catalog is not designed to be constantly updated, so performance
would be abysmal if such a counter was stored there.

This *is* something that I can look at once EDB 3 is out, however.

--
Tim Young
Elevate Software
www.elevatesoft.com

John

I have created it myself, but if Tim writes it into the DB engine it can function like "GENERATED AS IDENTITY", perhaps even as a variant of these key words.

GENERATED columns have some nice, useful features which I would like to be able to access.