You are no doubt used to IDENTITY and how this works with a seed
and increment
value. SEQUENCE is structured in a very similar way, but with
fewer limitations,giving it a welcome flexibility.
A SEQUENCE object is created at the database level but, unlike an IDENTITY
property,
it can be used across multiple tables. An IDENTITY value is
generated when youinsert a row into a table and it cannot be updated. You can retrieve the SEQUENCE
value at any time and reset it without altering its previous value, and even set a
minimum and maximum value.
CREATE SEQUENCE mySequence AS int
START WITH 1INCREMENT BY 1
We have not used the SEQUENCE object yet, so the first value
returned should be 1.
Run the following statement to confirm this:
SELECT NEXT VALUE FOR mySequence AS [Next Value]
Result
NextValue
|
1
|
We can see that the SEQUENCE has not been used:
Next we will create a table so we can put SEQUENCE to the test.Run the following code to create the Employee table:
CREATE TABLE Employee
(
EmployeeID int NOT NULL,FirstName varchar(30) NOT NULL,
LastName varchar(30) NOT NULL
)
Now we will insert a couple of rows. Note that in the following
code we use NEXT
VALUE FOR just as we did in the preceding code to return the next SEQUENCE
value.This will increment the SEQUENCE, in our case by one, though you can set this to
be whatever you wish when you declare the SEQUENCE:
INSERT INTO Employee (EmployeeID, FirstName, LastName)
VALUES(NEXT VALUE FOR mySequence, 'Rachel', 'Clements'),
(NEXT VALUE FOR mySequence, 'Jon', 'Reade')
GO
SELECT * FROM Employee
EmployeeID
|
FName
|
LName
|
2
|
Raj
|
K
|
3
|
Randheer
|
P
|
The SEQUENCE doesn't have to be unique; we can reset the seed to
use the same
value again. If we had a unique constraint on our EmployeeID column
we wouldnot be able to do this, but because we have not added a constraint, we can have
some fun.
Run the following statement:
ALTER SEQUENCE mySequenceRESTART WITH 1
If again insert the value by using it the new value would be 1.
If we wanted to set a minimum and maximum value we could have declared our
SEQUENCE as follows:
CREATE SEQUENCE mySequence AS int
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 15
However we can change the maximum value using an ALTER statement.
If we wanted to restrict the number of rows inserted into a table
we could use a
SEQUENCE object to limit this number. So as you can see, the seed
and incrementvalues operate in the same fashion as IDENTITY, but you are free to use and
manipulate it.
No comments:
Post a Comment