Sequences
Let us suppose that an e-commerce company wants to expand its business worldwide. As such, the Datawarehouse team wants to store the purchase details country wise, keeping the purchase id continuous. That is, if there are 3 tables {PurchaseID_India, PurchaseID_UK, PurchaseID_Canada} and 5 purchases {2 in India, 1 in the UK, 2 in Canada}, then the purchase id should be stored in the following pattern:
- 1 → PurchaseID_India,
- 2 → PurchaseID_India,
- 3 → PurchaseID_UK,
- 4 → PurchaseID_Canada,
- 5 → PurchaseID_Canada
In order to do this, we make use of Sequences. We cant make use of Identity here because Identity is table specific. A sequence is similar to Identity as it is also used to auto-generate values but unlike Identity, Sequence is not table specific and is independent of tables.
Creating A Sequence
The syntax for creating a sequence is as follows. Purchase_Sequence is the name of the sequence. The following query will create a sequence with default values
CREATE SEQUENCE Purchase_SequenceTo view the properties of the sequence that we just created, the syntax is:
SELECT *
FROM sys.sequences
WHERE name=’Purchase_sequence’Output: (The output comes in a row having multiple columns, I have divided the screenshot into two parts and placed one below the other here)
The default data type for the sequence is BIGINT. At first, the values for start_value, minimum_value, and current_value will remain the same.
Fetch the Next Value
In the case of Identity, we either explicitly entered a record into the table or just inserted a record into the table which auto-incremented the identity value. But in the case of sequence, we have to write the following query:
SELECT NEXT VALUE FOR Purchase_sequence AS Next_ValueOutput:
As we can observe, the next_value is the same as that of the start value. So, initially, the next_value will be the same as that of the start_value of the sequence. If we execute the above query once again, we will get the following output:
Since we didn't give any increment value and by default, the increment value is 1, so when we execute the query, it increments the Next_Value by 1. Hence, we get -922….807.
So, the final output will be observed by the following query:
SELECT name, start_value, increment, minimum_value, maximum_value, current_value
FROM sys.sequences
WHERE name=’Purchase_sequence’Output:
Deleting a Sequence
If we want to drop a sequence, we can do it by writing the following piece of query:
DROP SEQUENCE Purchase_sequenceCustomizing a Sequence
Let’s suppose we want to start the PurchaseId at 10 and increment it by 1. We write the following query in order to execute:
-- creating a sequence with customized value --CREATE SEQUENCE Purchase_sequence --creating a sequence
AS INT --specifying the data type
START WITH 10 --start the sequence at 10
INCREMENT BY 1 --this value can be any integer(1, 2, 3 etc.)
--for a decrementing sequence, use a negative
value (-1,-2 etc.)-- in order to fetch the columns from the sequence table --SELECT name, start_value, increment, minimum_value, maximum_value, current_value
FROM sys.sequences
WHERE name='Purchase_sequence'
Output:
Since we set the data type as INT, the minimum_value and maximum_value are set to the minimum and maximum value of integer data type.
Altering a sequence: If we want to change values for an already created sequence, we make use of ALTER. For example, if we want to change the value for minimum_value and maximum_value, we write the below query:
-- altering a sequence with customized value --ALTER SEQUENCE Purchase_sequence
MINVALUE 1 --setting minimum value
MAXVALUE 1000 -- setting maximum value-- in order to fetch the columns from the sequence table --SELECT name, start_value, increment, minimum_value, maximum_value, current_value
FROM sys.sequences
WHERE name='Purchase_sequence'
Output:
Cycle property: Suppose, on reaching its maximum_value, we want the sequence to restart from its minimum_value. For this, we use Cycle property. The default behaviour of sequence is NO CYCLE. is_cycling is a boolean column, where 1 represents true and 0 represents false.
ALTER SEQUENCE Purchase_sequence CYCLE --to generate a cycle for
sequence(column
is_cycling).-- to view the properties, execute the following --SELECT name, is_cycling
FROM sys.sequences
WHERE name = 'Purchase_Sequence'
Output:
Cache Property: If we want caching behaviour for our sequence, we can turn it on in the following manner. If caching is not required, this property is set to NO CACHE. Note that by default, sequences have caching enabled with cache size as NULL.
ALTER SEQUENCE Purchase_sequence
CACHE 50 --to set the cache memory of 50 for the sequence (column
is_cached)-- to view the properties, execute the following --SELECT name, is_cached, cache_size
FROM sys.sequences
WHERE name = 'Purchase_sequence'
Implementing the requirement
Now as stated above, where the company wants to store the purchase ID in a contiguous manner, as per the country, we can achieve it in the following manner. First, let's create 4 tables in the following manner:
-- Roles Table --CREATE TABLE Roles
(
[RoleId] TINYINT CONSTRAINT ck_roleId PRIMARY KEY,
[RoleName] VARCHAR(20) CONSTRAINT ck_rolename UNIQUE NOT NULL
)--------------------------------------------------------
-- Users Table --CREATE TABLE Users
(
[EmailId] VARCHAR(50) CONSTRAINT pk_email PRIMARY KEY,
[UserPassword] VARCHAR(15) NOT NULL,
[RoleId] TINYINT CONSTRAINT fk_roleId REFERENCES Roles([RoleId]),
[Gender] CHAR(1) CONSTRAINT ck_gender CHECK([Gender] IN(‘M’,’F’)),[DateOfBirth] DATE CONSTRAINT ck_date CHECK([DateOfBirth] < GETDATE()),
[Address] VARCHAR(200) NOT NULL,
)--------------------------------------------------------
-- PurchaseDetailIndia Table --CREATE TABLE PurchaseDetailsIndia
(
[PurchaseId] INT,
[EmailId] VARCHAR(50) CONSTRAINT fk_EmailId_India REFERENCES Users(EmailId),
[ProductId] CHAR(4) CONSTRAINT fk_ProductId_India REFERENCES Products(ProductId),
[QuantityPurchased] SMALLINT CONSTRAINT chk_QuantityPurchased_India
CHECK(QuantityPurchased>0) NOT NULL,
[DateOfPurchase] SMALLDATETIME CONSTRAINT chk_DateOfPurchase_India
CHECK(DateOfPurchase<=GETDATE()) DEFAULT GETDATE() NOT NULL
)--------------------------------------------------------
-- PurchaseDetailUK Table --CREATE TABLE PurchaseDetailsUK
(
[PurchaseId] INT,
[EmailId] VARCHAR(50) CONSTRAINT fk_EmailId_UK REFERENCES Users(EmailId),
[ProductId] CHAR(4) CONSTRAINT fk_ProductId_UK REFERENCES Products(ProductId),
[QuantityPurchased] SMALLINT CONSTRAINT chk_QuantityPurchased_UK
CHECK(QuantityPurchased>0) NOT NULL,
[DateOfPurchase] SMALLDATETIME CONSTRAINT chk_DateOfPurchase_UK
CHECK(DateOfPurchase<=GETDATE()) DEFAULT GETDATE() NOT NULL
)
Now, after creating the above tables, we input the following sets of queries in order to fill the tables:
-- inserts row into PurchaseDetailsIndia table --
INSERT INTO PurchaseDetailsIndia VALUES
(NEXT VALUE FOR Purchase_Sequence,’Franken@gmail.com’,’P101',2,’Jan 12 2014 12:00PM’)-- inserts row into PurchaseDetailsUK table --
INSERT INTO PurchaseDetailsUK VALUES
(NEXT VALUE FOR Purchase_Sequence,’Albert@gmail.com’,’P143',1,’Jan 13 2014 12:01PM’)-- inserts row into PurchaseDetailsIndia table --
INSERT INTO PurchaseDetailsIndia VALUES
(NEXT VALUE FOR Purchase_Sequence,’Franken@gmail.com’,’P112',3,’Jan 14 2014 12:02PM’)
To see the records inserted into the table, execute the following query
-- view the records of PurchaseDetailsIndia --
SELECT PurchaseId FROM PurchaseDetailsIndia-- view the records of PurchaseDetailsUK --
SELECT PurchaseId FROM PurchaseDetailsUK
Output:
