Identity column - Pros and Cons

Identity column - Pros and Cons

Post by Rajes » Mon, 14 Jul 2003 11:22:26



Can some one explain the Pros and Cons of using Identity columns?

-Rp?

 
 
 

Identity column - Pros and Cons

Post by Rob Verschoo » Tue, 15 Jul 2003 01:41:25



Quote:> Can some one explain the Pros and Cons of using Identity columns?

> -Rp?

Pro: they're a very fast way of generating sequential numbers (since
no disk I/O or transactional locking is needed for generating a new
value.

Cons: in pre-12.0: there's a risk of "identity gaps", see
www.sypron.nl/idgaps.html for details.
In 12.0+: identity gaps can be limited or avoided with the
'identity_gap' setting -- when  you use this, there are few
disadvantages left (but those are solved in 12.5.0.3 and 12.5.1).

HTH,

Rob
-------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
and Replication Server 12.5

New book(coming soon): 'Tips, Tricks & Recipes for Sybase ASE'
For details, see http://www.sypron.nl/ttr


http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------

 
 
 

Identity column - Pros and Cons

Post by osy4 » Tue, 15 Jul 2003 02:30:02


see http://www.sypron.nl/idgaps.html

--
Posted via http://dbforums.com

 
 
 

Identity column - Pros and Cons

Post by Anthony Mandi » Tue, 15 Jul 2003 21:09:43



> In 12.0+: identity gaps can be limited or avoided with the
> 'identity_gap' setting -- when  you use this, there are few
> disadvantages left (but those are solved in 12.5.0.3 and 12.5.1).

        What disadvantages have been solved in 12.5.x?

-am     ? 2003

 
 
 

Identity column - Pros and Cons

Post by Rob Verschoo » Wed, 16 Jul 2003 03:12:06




> > In 12.0+: identity gaps can be limited or avoided with the
> > 'identity_gap' setting -- when  you use this, there are few
> > disadvantages left (but those are solved in 12.5.0.3 and 12.5.1).

> What disadvantages have been solved in 12.5.x?

> -am ? 2003

In 12.5.0.3, some identty-related built-ins were added as well as the
command 'set identity_update'.
In the beta version of 12.5.1, it is possible to reset the identity
counter to an arbitrary value with sp_chgattribute.

Rob
-------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
and Replication Server 12.5

New book(coming soon): 'Tips, Tricks & Recipes for Sybase ASE'
For details, see http://www.sypron.nl/ttr


http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------

 
 
 

Identity column - Pros and Cons

Post by Anthony Mandi » Wed, 16 Jul 2003 13:24:55



> In 12.5.0.3, some identty-related built-ins were added as well as the
> command 'set identity_update'.
> In the beta version of 12.5.1, it is possible to reset the identity
> counter to an arbitrary value with sp_chgattribute.

        Very interesting ...

-am     ? 2003

 
 
 

1. pros/cons of computed(?) columns

i've got a table that that grows by roughly 25,000 rows per year.  i've
got two columns c1 char(2) and c2 char(2).  the concatenation of c1 and
c2 is used in another table as basically a foreign key.  i added a
column c3 where the formula is c1 + c2.  any drawbacks to this?  the
only reason i've done this is because users are writing their own
queries in access and need to be able to see the correct relationships
between the two tables.

ch

2. Spell Checking a memo field in a screen

3. Virtual Columns Pros & Cons

4. Performance Question

5. How to change an identity column to non identity column

6. easy question about queries

7. how to alter an identity column to a non-identity column

8. How do I create case-sensitive indexes in SQL?

9. How do make an identity column NOT an identity column

10. Identity Column lost its identity

11. Changing Identity column to Identity not for replication?

12. problema con IDENTITY en replicacion

13. Identity column reverts to non-identity