Sequences

  • 1 → PurchaseID_India,
  • 2 → PurchaseID_India,
  • 3 → PurchaseID_UK,
  • 4 → PurchaseID_Canada,
  • 5 → PurchaseID_Canada

Creating A Sequence

CREATE SEQUENCE Purchase_Sequence
SELECT * 
FROM sys.sequences
WHERE name=’Purchase_sequence’

Fetch the Next Value

SELECT NEXT VALUE FOR Purchase_sequence AS Next_Value
Next_Value
Next_Value
SELECT name, start_value, increment, minimum_value, maximum_value, current_value
FROM sys.sequences
WHERE name=’Purchase_sequence’

Deleting a Sequence

DROP SEQUENCE Purchase_sequence

Customizing a Sequence

-- 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'
-- 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'
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'
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

-- 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
)
-- 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’)
-- view the records of PurchaseDetailsIndia --
SELECT PurchaseId FROM PurchaseDetailsIndia
-- view the records of PurchaseDetailsUK --
SELECT PurchaseId FROM PurchaseDetailsUK
PurchaseDetailsIndia table
PurchaseDetailsUK table

--

--

Software Developer | Web Developer | Database Developer

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store