1 Sequence Object.
We used IDENTITY to get sequential Number(1,2,3......). Now, In SQL Server 2012, there is a new feature called Sequence.
Sequence it is a user-defined object that creates a sequence of a number.
It is same as identity column.
Syntax for creating Sequence object:
CREATE SEQUENCE [schema_name . ] sequence_name
[ AS [ built_in_integer_type | user-defined_integer_type ] ]
[ START WITH <constant> ]
[ INCREMENT BY <constant> ]
[ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]
[ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]
[ { CACHE [ <constant> ] } | { NO CACHE } ]
[ ; ]
Explanation:
START WITH: From which Number it should start
INCREMENT BY: Increment Value
MINVALUE: Minimum Value.
MAXVALUE: Maxmimum Value
CYCLE: If we set the MAXVALUE with the CYCLE, when it reaches the MAXVALUE it will recycle to the MINVALUE and start again.
CACHE: If a CACHE VALUE IS PROVIDED, THEN SQL Server will cache (store in memory) the amount of values specified.
Example:
CREATE TABLE Employee
(
ID INT,
EName VARCHAR(150) NOT NULL
)
The keyword "NEXT VALUE" is used to get the next sequential number from the Sequence.
Now i am inserting values into the table Employee using the Next Value as shown below
SELECT * FROM Employee
How to Add Sequence object to table
CREATE TABLE Students
(
ID INT DEFAULT (NEXT value for dbo.Sequence),
Name VARCHAR(50) NOT NULL
)
Note:
dbo.Sequence is the Table name which we have created before(Sequence Object)
Note: Here we can see the id value starts with 14
Alter Command
Alter SEQUENCE SEQUENCE
RESTART WITH 2
INCREMENT BY 1
MINVALUE 2
MAXVALUE 90
CYCLE
CACHE 10;
To View all Sequences in DataBase
SELECT * FROM SYS.SEQUENCES
Drop Sequence
Syntax:DROP SEQUENCE SequenceName
Identity: Identity value is specific only to a table column.
Sequence: Sequence number we can use across multiple tables.
Creating Sequence using UI
Click on DataBase
And Click on Ok Button.
Pagination
Using OFFSET and FETCH in SQL Server 2012
Example:
CREATE TABLE Student
(
ID INT,
SNAME VARCHAR(50)
)
INSERT INTO Student VALUES (1,'a')
INSERT INTO Student VALUES (2,'B')
INSERT INTO Student VALUES (3,'C')
INSERT INTO Student VALUES (4,'d')
INSERT INTO Student VALUES (5,'e')
INSERT INTO Student VALUES (6,'f')
INSERT INTO Student VALUES (7,'g')
INSERT INTO Student VALUES (8,'h')
INSERT INTO Student VALUES (9,'i')
INSERT INTO Student VALUES (10,'j')
INSERT INTO Student VALUES (11,'k')
INSERT INTO Student VALUES (12,'l')
INSERT INTO Student VALUES (13,'m')
INSERT INTO Student VALUES (14,'n')
INSERT INTO Student VALUES (15,'o')
INSERT INTO Student VALUES (16,'p')
INSERT INTO Student VALUES (17,'q')
SELECT * FROM Student
OFFSET Keyword: Here query will skip the number of records we specified in OFFSET n Rows.
OFFSET 2 ROWS, means sql server will skip 2 records from the result and display the remaining records , now see the output in the below screenshot.
FETCH NEXT Keywords:
If we are not using Offset than we will get an error as shown below.
With Stored Procedure
We used IDENTITY to get sequential Number(1,2,3......). Now, In SQL Server 2012, there is a new feature called Sequence.
Sequence it is a user-defined object that creates a sequence of a number.
It is same as identity column.
Syntax for creating Sequence object:
CREATE SEQUENCE [schema_name . ] sequence_name
[ AS [ built_in_integer_type | user-defined_integer_type ] ]
[ START WITH <constant> ]
[ INCREMENT BY <constant> ]
[ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]
[ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]
[ { CACHE [ <constant> ] } | { NO CACHE } ]
[ ; ]
Explanation:
START WITH: From which Number it should start
INCREMENT BY: Increment Value
MINVALUE: Minimum Value.
MAXVALUE: Maxmimum Value
CYCLE: If we set the MAXVALUE with the CYCLE, when it reaches the MAXVALUE it will recycle to the MINVALUE and start again.
CACHE: If a CACHE VALUE IS PROVIDED, THEN SQL Server will cache (store in memory) the amount of values specified.
Example:
CREATE SEQUENCE Sequence AS BIGINT
START WITH 12
INCREMENT BY 1
MINVALUE 12
MAXVALUE 30
CYCLE
CACHE 10;
Now, I am going to Create a table.
CREATE TABLE Employee
(
ID INT,
EName VARCHAR(150) NOT NULL
)
The keyword "NEXT VALUE" is used to get the next sequential number from the Sequence.
Now i am inserting values into the table Employee using the Next Value as shown below
INSERT Employee (ID, EName)VALUES (NEXT VALUE FOR SEQUENCE , 'UDAY');
INSERT Employee (ID, EName)VALUES (NEXT VALUE FOR SEQUENCE , 'ABC');
INSERT Employee (ID, EName)VALUES (NEXT VALUE FOR SEQUENCE , 'SDASD');
INSERT Employee (ID, EName)VALUES (NEXT VALUE FOR SEQUENCE , 'ASDFSD');
INSERT Employee (ID, EName)VALUES (NEXT VALUE FOR SEQUENCE , 'KITTU');
INSERT Employee (ID, EName)VALUES (NEXT VALUE FOR SEQUENCE , 'AFSASD');
INSERT Employee (ID, EName)VALUES (NEXT VALUE FOR SEQUENCE , 'ANKUR'); INSERT Employee (ID, EName)VALUES (NEXT VALUE FOR SEQUENCE , 'UDAY');
INSERT Employee (ID, EName)VALUES (NEXT VALUE FOR SEQUENCE , 'ABC');
INSERT Employee (ID, EName)VALUES (NEXT VALUE FOR SEQUENCE , 'SDASD');
INSERT Employee (ID, EName)VALUES (NEXT VALUE FOR SEQUENCE , 'ASDFSD');
INSERT Employee (ID, EName)VALUES (NEXT VALUE FOR SEQUENCE , 'KITTU');
INSERT Employee (ID, EName)VALUES (NEXT VALUE FOR SEQUENCE , 'AFSASD');
INSERT Employee (ID, EName)VALUES (NEXT VALUE FOR SEQUENCE , 'ANKUR');
INSERT Employee (ID, EName)VALUES (NEXT VALUE FOR SEQUENCE , 'UDAY');
INSERT Employee (ID, EName)VALUES (NEXT VALUE FOR SEQUENCE , 'ABC');
INSERT Employee (ID, EName)VALUES (NEXT VALUE FOR SEQUENCE , 'SDASD');
INSERT Employee (ID, EName)VALUES (NEXT VALUE FOR SEQUENCE , 'ASDFSD');
INSERT Employee (ID, EName)VALUES (NEXT VALUE FOR SEQUENCE , 'KITTU');
INSERT Employee (ID, EName)VALUES (NEXT VALUE FOR SEQUENCE , 'AFSASD');
INSERT Employee (ID, EName)VALUES (NEXT VALUE FOR SEQUENCE , 'ANKUR'); SELECT * FROM Employee
How to Add Sequence object to table
CREATE TABLE Students
(
ID INT DEFAULT (NEXT value for dbo.Sequence),
Name VARCHAR(50) NOT NULL
)
Note:
dbo.Sequence is the Table name which we have created before(Sequence Object)
INSERT Students(Name)VALUES ('UDAY');
INSERT Students (Name)VALUES ('Kumar');
INSERT Students (Name)VALUES ('Kittu');
Now, After Inserting Rows into students Table , we can see the values
SELECT * FROM STUDENTS
Note: Here we can see the id value starts with 14
Alter Command
Alter SEQUENCE SEQUENCE
RESTART WITH 2
INCREMENT BY 1
MINVALUE 2
MAXVALUE 90
CYCLE
CACHE 10;
To View all Sequences in DataBase
SELECT * FROM SYS.SEQUENCES
Drop Sequence
Syntax:DROP SEQUENCE SequenceName
Identity: Identity value is specific only to a table column.
Sequence: Sequence number we can use across multiple tables.
Creating Sequence using UI
Click on DataBase
And Click on Ok Button.
Pagination
Using OFFSET and FETCH in SQL Server 2012
Example:
CREATE TABLE Student
(
ID INT,
SNAME VARCHAR(50)
)
INSERT INTO Student VALUES (1,'a')
INSERT INTO Student VALUES (2,'B')
INSERT INTO Student VALUES (3,'C')
INSERT INTO Student VALUES (4,'d')
INSERT INTO Student VALUES (5,'e')
INSERT INTO Student VALUES (6,'f')
INSERT INTO Student VALUES (7,'g')
INSERT INTO Student VALUES (8,'h')
INSERT INTO Student VALUES (9,'i')
INSERT INTO Student VALUES (10,'j')
INSERT INTO Student VALUES (11,'k')
INSERT INTO Student VALUES (12,'l')
INSERT INTO Student VALUES (13,'m')
INSERT INTO Student VALUES (14,'n')
INSERT INTO Student VALUES (15,'o')
INSERT INTO Student VALUES (16,'p')
INSERT INTO Student VALUES (17,'q')
SELECT * FROM Student
OFFSET Keyword: Here query will skip the number of records we specified in OFFSET n Rows.
SELECT *
FROM STUDENT
ORDER BY ID
OFFSET 2 ROWS
FROM STUDENT
ORDER BY ID
OFFSET 2 ROWS
OFFSET 2 ROWS, means sql server will skip 2 records from the result and display the remaining records , now see the output in the below screenshot.
FETCH NEXT Keywords:
If we are not using Offset than we will get an error as shown below.
SELECT *
FROM STUDENT
ORDER BY ID
--OFFSET 2 ROWS
FETCH NEXT 10 ROWS ONLY;
Example with offset and fetch
SELECT *
FROM STUDENT
ORDER BY ID
OFFSET 2 ROWS
FETCH NEXT 6 ROWS ONLY;
Now the output is
Here it display only the 6 Rows starting from 3 to 8)
Bec: we have given Offset :2 (To skip 2 Records)
Fetch 6( To display 6 Records)
We cannot use Fetch Next without Offset.
CREATE PROCEDURE StudentPagination
(
@PageNo INT,
@Size INT
)
AS
DECLARE @Count INT
SET @Count = (@PageNo-1)*@Size
SELECT *
FROM Student
ORDER BY ID
OFFSET @Count ROWS
FETCH NEXT @Size ROWS ONLY