Re: SQL Sequence use cases

Agreed. I recently have had to deal with some SQL Server tables and
they are all designed that way. At first, I didn't care for it but I
really appreciate the value now.
We're used to things like part number, some code or other being the
primary key. Problem is, you can't change it without breaking the app. Having the "id" field be the primary key and the part number, etc. being
a natural key gives you a lot of flexibility and, as you described,
simplifies some things greatly.

Again, read Dan Cruikshank's article. Thanks to Elvis et. al. at
Centerfield for publishing that.

Pete@xxxxxxxxxx 11/25/2009 12:48:36 PM >>>

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