oracle sequence sql server,sql server也支持序列了，不知道与oracle有什么区别

Sequence Numbers

A sequence is a new type of user-defined object in SQL Server 2012 that acts like a global identity column

for the database. The default data type for a sequence object is bigint. A sequence object can be very

useful, if you need to share a series of numbers between tables or if you need to know a value before

performing an insert. For example, you can call the sequence object from the application, insert a row

using that sequence number, and then run additional logic using the same sequence number to

uniquely identify the row. You can even choose to cycle the sequence object so that it will start over

when it reaches a specified value. You request the next sequence number using the NEXT VALUE FOR

clause (see Listing 1-1).

■ Note In addition to T-SQL, you can create and manage sequence objects using the Sequence folder located

under the Programability node for the database in SSMS.

Listing 1-1. Sequence Object Example

--Create Sequence Object

CREATE SEQUENCE TestSequence

AS BIGINT

START WITH 1

INCREMENT BY 1;

--Create Table Using Default Sequence Numbers

CREATE TABLE SequenceTest1

(

Col1 BIGINT DEFAULT (NEXT VALUE FOR TestSequence),

Col2 CHAR(1)

);

--Create Table With No Default

CREATE TABLE SequenceTest2

(Col1 BIGINT PRIMARY KEY,

Col2 CHAR(1));

--Insert 3 Rows Into SequenceTest1

INSERT INTO SequenceTest1 (Col2)

VALUES ('a'), ('b'), ('c');

--Insert 3 Rows Into SequenceTest2

INSERT INTO SequenceTest2 (Col1, Col2)

VALUES (NEXT VALUE FOR TestSequence,'d'),

(NEXT VALUE FOR TestSequence,'e'),

(NEXT VALUE FOR TestSequence,'f');

--Select The Data

SELECT Col1, Col2

FROM SequenceTest1;

SELECT Col1, Col2

FROM SequenceTest2;

You can see the results of Listing 1-1 in Figure 1-4. As you can see in Col1 in Figure 1-4, the sequence

object TestSequence has maintained the correct sequential order between the tables SequenceTest1 and

SequenceTest2.

You can restart a sequence object by using ALTER SEQUENCE as shown in the following command.

ALTER SEQUENCE TestSequence

RESTART WITH 1;

If you want a sequence object to restart automatically, you can specify the options when creating

the sequence object. For example, the following sequence object will restart at the number 1 specified by

the MINVALUE when it reaches 100 specified by the MAXVALUE. If you create a sequence object in

descending order by specifying a negative number for the INCREMENT BY option, the sequence object

will restart when it reaches the MINVALUE.

CREATE SEQUENCE CycleSequence

AS tinyint

START WITH 1

INCREMENT BY 1

MINVALUE 1

MAXVALUE 100

CYCLE;

GAPS IN SEQUENCES

Sequences have long been available in other database systems—notably Oracle’s. Perhaps the most

common source of angst is from gaps in sequences. There are several reasons individual sequence values

might be lost. For example, you might insert a set of rows into a table and end up with keys numbered 1,

2, 4, 5, but without a 3. Our advice? Do not worry about gaps. Gaps are a side-effect of how sequences

are implemented within the instance. If you use sequences as surrogate key generators, then gaps don’t

really matter because the keys are arbitrary anyway. If gaps do matter to you, then rethink your position

and your design, and if gaps still matter, then sequences are the wrong solution.