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.
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 ];
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.
The beginning esteem that the succession returns at first.
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.
The base worth took into account the grouping.
It implies that there is no base quality determined for the grouping.
The most extreme quality took into account the arrangement.
It implies that there is no most extreme quality indicated for the grouping.
It implies that the arrangement will begin once again once it has finished the grouping.
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.
It stores the succession numbers to minimize plate IO.
It doesn't reserve the grouping numbers. Examples
The below example describes the SQL sequence:
[c]
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)
[/c]
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
[c]
INSERT INTO accounts
VALUES (NEXT VALUE FOR account_num_seq,'SAM','31000','21-05-1980');[/c]
To drop a sequence
[c]
DROP account_num_seq;[/c]
This statement will drop the account_num_seq.
Key Points
- SQL Sequence - Makes an article in SQL database which will create an arrangement of number and these number will be addition consequently.