1.

Explain the difference between Sequence vs Identity?

Answer»

An IDENTITY column in the table has auto-generate & auto increase value with each new row insert. The user cannot insert value in the identity column.

The sequence is a new feature introduced with SQL Server 2012 similar to Oracle’s sequence objects.  A sequence object generates a sequence of unique numeric VALUES as per the specifications mentioned. Next VALUE for a SEQUENCE object can be generated using the NEXT VALUE FOR clause.

  • IDENTITY is column level property & tied to a particular table. This cannot be SHARED among multiple TABLES.
  • SEQUENCE is an object DEFINED by the user with specific details and can be shared by multiple tables. This is not tied to any particular table.
  • IDENTITY property cannot be reset to its initial value but the SEQUENCE object can be reset to initial value any time.
  • Maximum value cannot be defined for IDENTITY whereas this can be done for SEQUENCE object.


Discussion

No Comment Found

Related InterviewSolutions