SQL 2012 NEW FEATURES PART 2

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

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.

With Stored Procedure

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