SQL - SPLessons

SQL Sequence

Chapter 45

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

SQL Sequence

SQL Sequence

shape Description

SQL Sequence creates an object in SQL server which will generate a sequence of number and these number will be incremented automatically. This can be useful for generating primary key which will be a unique number.

shape Syntax

The syntax for SQL SEQUENCE is as follows:

CREATE SEQUENCE [schema.]sequence_name
[ AS datatype ]
[ START WITH value ]
[ INCREMENT BY value ]
[ MINVALUE value | NO MINVALUE ]
[ MAXVALUE value | NO MAXVALUE ]
[ CYCLE | NO CYCLE ]
[ CACHE value | NO CACHE ];

AS datatype

It can be TINYINT, INT, BIGINT, SMALLINT, NUMERIC or DECIMAL. On the off chance that datatype is not determined, the grouping will default to a BIGINT datatype.

START WITH value

The beginning esteem that the succession returns at first.

INCREMENT BY value

It can be neither a negative or positive quality. On the off chance that a positive worth is indicated, the succession will be a rising grouping of qualities. On the off chance that a negative worth is determined, the succession will be a diving grouping of qualities.

MINVALUE value

The base worth took into account the grouping.

NO MINVALUE

It implies that there is no base quality determined for the grouping.

MAXVALUE

The most extreme quality took into account the arrangement.

NO MAXVALUE

It implies that there is no most extreme quality indicated for the grouping.

CYCLE

It implies that the arrangement will begin once again once it has finished the grouping.

NO CYCLE

It implies that the arrangement will raise a blunder when it has finished the grouping. It won’t begin the succession once again once more.

CACHE value

It stores the succession numbers to minimize plate IO.

NO CACHE

It doesn’t reserve the grouping numbers.

shape Examples

The below example describes the SQL sequence:

sql> create table product(prod_id int not null auto_increment,prod_name varchar (255)not null,primary key(prod_id));
Query OK, 0 rows affected (0.52 sec)

sql> insert into product (prod_name) values ('mobile');
Query OK, 1 row affected (0.05 sec)

sql> insert into product (prod_name) values ('laptop');
Query OK, 1 row affected (0.09 sec)

sql> insert into product (prod_name) values ('system');
Query OK, 1 row affected (0.09 sec)

sql> select * from product;
+---------+-----------+
| prod_id | prod_name |
+---------+-----------+
|       1 | mobile    |
|       2 | laptop    |
|       3 | system    |
+---------+-----------+
3 rows in set (0.00 sec)

This will create a sequence of INT datatype and it will starts with 1001 and the value will be incremented by 1.

In accounts table it helps for filling account_num automatically. account_num field have to be a integer field.

The details in student table can be inserted like

INSERT INTO accounts
VALUES (NEXT VALUE FOR account_num_seq,'SAM','31000','21-05-1980');

To drop a sequence

DROP account_num_seq;

This statement will drop the account_num_seq.

Summary

shape Key Points

  • SQL Sequence – Makes an article in SQL database which will create an arrangement of number and these number will be addition consequently.