Re: SQL Sequence use cases

As long as it is unique I think it can be quite useful. Most of the tables I work with outside of DB2 are keyed by sequence #. If you work with a Rail application, most of the unique keys on tables are assumed to be sequences and named 'id'. In fact, if you DO use sequences and the column name is 'id', Rails pretty much works right out of the box.

I have table on the i from a "legacy" application that has 8 fields that make up the unique key. If the table had a column that was a sequence, my I/O would be SO much easier to accomplish. Issuing an update to table x where id = 34423 is a lot easier than update tablex where key1='foo1' and key2 = 'bar1', etc....

As for a "business" case, simplifying I/O could simplify development and reduce costs. In the MSSQL application I work with, every table has a sequence column named "id" (even though it isn't a Rails app, it is a .Net app). Initially I found it confusing but as I work with the tables more, my code can follow the pattern of using the sequence as the unique key to the row making for greater consistency in SQL update statements and my model code.

Perhaps it boils down to coding and DB design preferences. But, going forward, all my new tables will have sequences that uniquely identify the record.

Pete


David Gibbs wrote:

On 11/25/2009 2:09 PM, Luis Rodriguez wrote:

As per the business cases that David comments, I suppose that this
(the sequence object) could be seen as just another tool that SQL
provides. Myself, am a little uneasy about the storing of the data in
a *DTAARA (something that sometimes gets overlooked when restoring or
saving). Also, the fact that I can't get a guarantee of the sequence
can give some problems (think, for example, invoice numbers).

I'm not so concerned about the backup / restore issues ... as I always recommend backing up the entire data library at once.

The non-sequential nature of a cached sequence makes me wonder what kind of application it could be used for.

david