Sequence vs identity

by

I think you will find your answer here

Using the identity attribute for a column, you can easily generate
auto-incrementing numbers (which as often used as a primary key). With
Sequence, it will be a different object which you can attach to a
table column while inserting. Unlike identity, the next number for the
column value will be retrieved from memory rather than from the disk –
this makes Sequence significantly faster than Identity. We will see
this in coming examples.

And here:

Sequences: Sequences have been requested by the SQL Server community
for years, and it’s included in this release. Sequence is a user
defined object that generates a sequence of a number. Here is an
example using Sequence.

and here as well:

A SQL Server sequence object generates sequence of numbers just like
an identity column in sql tables. But the advantage of sequence
numbers is the sequence number object is not limited with single sql
table.

and on msdn you can also read more about usage and why we need it (here):

A sequence is a user-defined schema-bound object that generates a
sequence of numeric values according to the specification with which
the sequence was created. The sequence of numeric values is generated
in an ascending or descending order at a defined interval and may
cycle (repeat) as requested. Sequences, unlike identity columns, are
not associated with tables. An application refers to a sequence object
to receive its next value. The relationship between sequences and
tables is controlled by the application. User applications can
reference a sequence object and coordinate the values keys across
multiple rows and tables.

A sequence is created independently of the tables by using the CREATE
SEQUENCE statement. Options enable you to control the increment,
maximum and minimum values, starting point, automatic restarting
capability, and caching to improve performance. For information about
the options, see CREATE SEQUENCE.

Unlike identity column values, which are generated when rows are
inserted, an application can obtain the next sequence number before
inserting the row by calling the NEXT VALUE FOR function. The sequence
number is allocated when NEXT VALUE FOR is called even if the number
is never inserted into a table. The NEXT VALUE FOR function can be
used as the default value for a column in a table definition. Use
sp_sequence_get_range to get a range of multiple sequence numbers at
once.

A sequence can be defined as any integer data type. If the data type
is not specified, a sequence defaults to bigint.

More Related Contents:

  1. I dont know sql syntax for my table [closed]
  2. get a comma delimited string from rows [duplicate]
  3. Scope of temporary tables in SQL Server
  4. Count Number of Consecutive Occurrence of values in Table
  5. How to correctly insert newline in nvarchar [duplicate]
  6. Find min and max for subsets of consecutive rows – gaps and islands
  7. How to get Previous Value for Null Values
  8. SQL Server: +(unary) operator on non-numeric Strings
  9. SQL where clause odd behavior [closed]
  10. How do I escape a single quote in SQL Server?
  11. Simple way to transpose columns and rows in SQL?
  12. Only inserting a row if it’s not already there
  13. Unpivot with column name
  14. Selecting data from two different servers in SQL Server
  15. Maximum size for a SQL Server Query? IN clause? Is there a Better Approach [duplicate]
  16. Most efficient way in SQL Server to get date from date+time?
  17. Convert varchar into datetime in SQL Server
  18. How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM in SQL Server 20008R2?
  19. SQL : BETWEEN vs =
  20. How do I perform a GROUP BY on an aliased column in SQL Server?
  21. Need to list all triggers in SQL Server database with table name and table’s schema
  22. Is it possible to perform multiple updates with a single UPDATE SQL statement?
  23. Incorrect syntax near the keyword ‘with’…previous statement must be terminated with a semicolon
  24. Get previous and next row from rows selected with (WHERE) conditions
  25. Best equivalent for IsInteger in SQL Server
  26. Syntax of for-loop in SQL Server
  27. Can SQL Server Express LocalDB be connected to remotely?
  28. WITH CHECK ADD CONSTRAINT followed by CHECK CONSTRAINT vs. ADD CONSTRAINT
  29. Sql Server : How to use an aggregate function like MAX in a WHERE clause
  30. Date serial in SQL?

Leave a Comment