SQL Sequence Syntax and Use with Examples
SQL Sequence Syntax and Use with Examples
A sequence is an arrangement of whole numbers 1, 2, 3 … that are created all together on demand. Sequences are oftentimes utilized as a part of databases since numerous applications require every row in a table to contain a distinct value, and sequences give a simple approach to create them.
Database systems like MySQL and MS SQL support the sequencing feature to produce unique values. For example: In MySQL, AUTO_INCREMENT is applied on column. This application automatically increments the column value by 1 each time a new record is entered into the table.
Creating a Sequence
Following syntax is used to create sequences:
CREATE Sequence Seq_name
start with initial_val
increment by inc_val
maxvalue max_val
cycle|nocycle
- Initial_val is used to specify the starting value of the Sequence i.e. 1.
- inc_val – increment value is the value that will be incremented for each record.
- max_val – Maximum value is the value specify the max value for the sequence
- cycle specifies whether the maximum value exceeds the set limit or not. If it is set, then sequence will restart after reaching the max value and if it is set to nocycle then it will not restart rather it will generate an error.
Example
Following query is the example of sequence:
CREATE Sequence mySeq_1
start with 1
increment by 1
maxvalue 99999
cycle;
Example to use Sequence
The Employees table,
| Emp_ID | Employee_Name |
| 1 | Tim Robbins |
| 2 | Adam Steven |
| 3 | Alex Murphy |
SQL query for new record would be:
INSERT into Employees value(mySeq_1.nextval,’James McGregor’);
Result table will look like,
| Emp_ID | Employee_Name |
| 1 | Tim Robbins |
| 2 | Adam Steven |
| 3 | Alex Murphy |
| 4 | James McGregor |
The nextval property increments the value in the sequence and will be stored in the new row.
More Related Articles For You
- Architecture of DBMS
- CODDS RULE DBMS
- Database Models in DBMS
- Relational DBMS Concepts
- Keys and Types of keys in Database
- Database Normalization
- Generalization, Specialization and Aggregation Concepts in DBMS
- ERD Diagram Tutorial with Examples in DBMS
- Introduction to SQL
- How to Create Query in SQL, Create Table, Delete Table and User Insert Info Statements
- Alter Query Command in SQL, Add, Modify and Drop Column in Table
- TCL Commands in SQL
- Truncate Query, Drop and Rename Query in SQL
- All DML Statement in SQL, Select Statement, Delete, Insert and Update Statement
- Data Control Language DCL Revoke and Grant Command in SQL
- Select Statement or Select Query in SQL Explained with Examples
- Distinct Keyword Explained in SQL
- WHERE Statement or WHERE Clause in SQL Statement Explained
- AND & OR Operators in SQL
- LIKE Operator in SQL
- ORDER BY Clause Sorting Explained in SQL
- Group By Clause in SQL
- Having Clause Explained in SQL
- SQL Constraints
- SQL Aggregate Functions
- SQL Aliases Use and Purpose in SQL
- SQL Joins Types, Use and Purpose
- SQL View
- SET Operation in SQL, UNION, UNION ALL, Intersect and Minus