SEQUENCE in SQL Server(TSQL)

 

Sequence is a table-independent structure similar to the identity logic in SQL Server. You can use the sequence in more than one table.

You may want to read the article “Identitiy Insert On SQL Server” for more information on Identity.

With Sequence, numeric and sequential numbers are generated from certain values. Sequential numbers can be generated for different tables using the same sequence in multiple tables.

It would be more appropriate to go through an example to know the Sequence better.

For example, in the following example, we create a sequence.

If we created CACHE 30 instead of NO CACHE, it would cache numbers from 1 to 30, and when it reached 30, it would cache the numbers from 30 to 60 again.

Giving a CACHE value to make less IO seems to be good in terms of performance, but it can be seen that it doesn’t noticeably improve performance, but the numbers in the cache may be lost if the server unexpectedly shuts down.

After creating the Sequence, let’s start using it.

We can use it as follows during the table creation.

Or we can add it to an existing table as follows.

Since we’re linking the sequence to the table when we create the table, the above ALTER TABLE statement will give an error as follows.

Msg 1781, Level 16, State 1, Line 1

Column already has a DEFAULT bound to it.

Msg 1750, Level 16, State 0, Line 1

Could not create constraint or index. See previous errors.

Or, instead of adding to the table, we can get this value from the sequence in the insert clause.

Although we’re linking the sequence to the table while creating the Table, the following query won’t fail.

Even if there is a sequence linked to the table, we can add a record to the table as follows using same or another sequence.

We can re-program the Sequence to start after a certain value by ALTER.

After we’ve changed the Sequence, let’s add another record.

As you can see, since we added the IZMIR record after reprograming the sequence, the ID value of IZMIR was 70.

You can see the existing sequences as follows.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *