Create SQL Sequence – Syntax and Example
1. Objective
In this SQL tutorial, we are going to learn about SQL Sequence. Moreover, we will start our tutorial, with the meaning of SQL Sequence. Also, we will see syntax and example of SQL Sequence.
So, let us start SQL Sequence Tutorial.
2. What is SQL Sequence?
The sequence in SQL is a set of integers that are supported and generated by database systems to produce on demand unique values.
You must know about SQL Operators
Following are the characteristics of SQL Sequence –
- It is a user-defined scheme in SQL which is bound to an object which is used to generate a sequence of numeric values.
- They are frequently used with databases because many applications require row in a table to have unique sequences and values. Sequences provide an easy way to do so.
- In a type of numeric values sequences when generated in an ascending or descending order at defined intervals and can be configured to restart when exceeds max_value.
3. Sequences in SQL
The syntax of SQL Sequence:
CREATE SEQUENCE sequence_name START WITH initial_value INCREMENT BY increment_value MINVALUE minimum value MAXVALUE maximum value CYCLE|NOCYCLE ;
sequence_name: Name of the sequence.
initial_value: starting value from where the sequence starts.
Initial_value should be greater than or equal
to minimum value and less than equal to maximum value.
Have a look at SQL Data Types
increment_value: Value by which sequence will increment itself.
Increment_value can be positive or negative.
minimum_value: Minimum value of the sequence.
maximum_value: Maximum value of the sequence.
cycle: When sequence reaches its set_limit
it starts from beginning.
nocycle: An exception will be thrown
if sequence exceeds its max_value.
Following is the grouping question making arrangement in rising request.
Case 1:
CREATE SEQUENCE sequence_1 start with 1 increment by 1 minvalue 0 maxvalue 100 cycle;
Above query will make a succession named sequence_1. A sequence will begin from 1 and will be increased by 1 having most extreme value 100. Grouping will rehash itself from beginning a value in the increasing 100.
Illustration 2:
CREATE SEQUENCE sequence_2 start with 100 increment by -1 minvalue 1 maxvalue 100 cycle;
Above example will make a grouping named sequence_2. A Sequence will begin from 100 and ought to be not exactly or equivalent to greatest value and will be set by – 1 having least value 1.
Do you know about SQL Expressions?
The example of SQL Sequence –
Make TABLE understudies ( ID number(10), NAME char(20) );
- Presently insert values into table
INSERT into students VALUES(sequence_1.nextval,'Ramesh'); INSERT into students VALUES(sequence_1.nextval,'Suresh');
where sequence_1.nextval will embed id’s in id segment in an arrangement as characterized in sequence_1.
So, this was all in SQL Sequences. Hope you like our explanation.
4. Conclusion – SQL Sequences
Hence, in this tutorial Sequence in SQL tutorial, we learned about the SQL Sequence. Moreover. we discussed the characteristics and syntax of SQL Sequence. Also, we understand Sequence in SQL with the help of the example. Still, if you are having any confusion in SQL Sequence, ask in the comment tab.
See also –
Null values in SQL
For reference