restart sequence when the year changes

Is it possible to do a sequence with Start at 1 and INCREMENT 1, and when changing the year, restart the sequence?

``````-- code# Create a sequence
CREATE SEQUENCE SEQ_ALUNO
INCREMENT BY 1
MINVALUE 1
MAXVALUE 10
CYCLE  CACHE 10;

-- code# Create a table T_ALUNO
CREATE TABLE T_ALUNO
(
COD_ALUNO INT,
NOM_ALUNO VARCHAR(50),
ANO int
);
``````

Note: I am using SQL server express and will not be able to use the windows task scheduler.

• How many stored procedures or pieces of code directly cause the sequence to increment? – Aaron Bertrand Aug 9 at 19:39
• I did not understand. When entering a record in the table above, create a sequence in the `COD_ALUNO` column and when the year changes, the sequence will restart. – Edvaldo Lucena Aug 9 at 19:47

I don't think it is possible using a Sequence. Sequence use cycles between numbers like bigint, and cycle to either the min or max depending if its an ascending or descending sequence.

This problem can be solved by using a Trigger on Insert which performs an update to the field you aim to have a cyclic sequence number based on ANO(Year).

First create the table:

``````--Drop and Create table T_ALUNO
IF OBJECT_ID('T_ALUNO','U') IS NOT NULL
DROP TABLE T_ALUNO

CREATE TABLE T_ALUNO
(
Id int not null identity(1,1), -- add id for easy joining after insert
COD_ALUNO INT default 0, --added a default for primary key
NOM_ALUNO VARCHAR(50),
ANO int,
Primary Key (COD_ALUNO, NOM_ALUNO, ANO) -- added for uniqueness
)
``````

Second, Create a Trigger on Insert to perform an update which will include a common table expression (cte) including a Window Function Row_Number() with a partition as well as an ordering clause. The ordering here is crucial and specific to the default value chosen (0).

Then the update uses this new row number field value in a join on the inserted table by our new id field. The inserted table is a reference table like the deleted table which contains rows of the same schema from the current operation - in our case insert.

``````CREATE OR ALTER TRIGGER tr_Sequence_Annual_Cycle
ON T_ALUNO
AFTER INSERT
AS
;with mycte as (
select row_number() over (partition by ANO order by case when id = 0 then 1 else 0 end, id) rnum
,id
,COD_ALUNO
from T_ALUNO
)

UPDATE m
set COD_ALUNO = m.rnum
FROM mycte m
inner join inserted i
on m.Id = i.Id
``````

Finally perform a test of the newly created trigger by inserting some test values:

``````insert into T_ALUNO (NOM_ALUNO,ANO)
select 'JOSE', 2019
union all
select 'JAVIER', 2019
union all
select 'JUANITA', 2019
union all
select 'NOE', 2020
union all
select 'JUAQUINA', 2021

select * from T_ALUNO order by 4,2

insert into T_ALUNO (
NOM_ALUNO,
ANO)
select 'JOSEPH', 2019
union all
select 'JESSE', 2019
union all
select 'JOHN', 2019
union all
select 'JIM', 2020
union all
select 'JOAN', 2021

select * from T_ALUNO order by 4,2
``````

And you should see the following results:

Hope this helps.