Steve Waters Steve Waters -4 years ago 107
SQL Question

Creating a sequence for a column's value with minimum value 1 if no values exist, and 1 larger than the highest value on the column if values exist

I have the table

organisation
and it has a column
customer_number
.

Now, I'm supposed to create a sequence, where whenever a new
organisation
is created - as in a new row is persisted to the table - the
customer_number
column on the new row would get value 1 IF no values exist in the column
customer_number
on any row. IF values DO exist, the new row would get a value on said column, which would be the highest value on said column in the table plus one.

Example: If there would be no rows or only rows that have no value on
customer_number
column, the newly added
organisation
row would get 1 as the value for its customer_number column. If there would be already for example two rows with values on said column - say 100 and 200 - the new row would get value 201 on that column.

I just can't figure out the SQL spells to achieve this goal.

The database is SQL Server 2012.

I already have a sequence for IDs. Here's how it is in the SQL-script that creates the table:

CREATE SEQUENCE organisation_seq AS BIGINT START WITH 1 INCREMENT BY 1;
CREATE TABLE organisation
(
id BIGINT NOT NULL,
customer_number VARCHAR(50) UNIQUE,
... rest of the columns ...
);


In the Organisation Entity bean it is like this:

@Entity
@Table(name = "organisation")
public class Organisation {

static Logger logger = Logger.getLogger(Organisation.class.getName());
private static final long serialVersionUID = 1L;

@Id
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="organisationSeq")
@SequenceGenerator(name="organisationSeq", sequenceName="organisation_seq", allocationSize=1)
private Long id;

private String customerNumber;

... rest of the Entity properties ...

Answer Source

If you just want to be able to restart a sequence starting at a value determined by a query, you can do that with dynamic sql like so:

create procedure dbo.mysequence_restart as 
begin
set nocount, xact_abort on;
  declare @restartSequence nvarchar(256);
  declare @restartWith nvarchar(10); = convert(nvarchar(10),isnull((
    select max(id)+1 
    from organisation 
    where customer_number is not null
    ), 1));

  set @restartSequence = 'alter sequence dbo.mysequence restart with '+@restartWith+';'; 
  exec sp_executesql @restartSequence;
end;
go

This does not have to be in a procedure, it is just an example.


That is not exactly how a sequence works in Sql Server. Here is a quick comparison of how a sequence and identity react to inserted values they did not generate.

Test setup: http://rextester.com/VDDF36095

/* ------------- using sequence ----------- */
create sequence organisation_seq as bigint 
  start with 1 increment by 1;
create table organisation
(
  id bigint not null default next value for organisation_seq,
  customer_number varchar(50) unique
);

insert into organisation values
 (next value for organisation_seq, 'a')
,(200, 'b')
,(next value for organisation_seq, 'c');

select * from organisation;

returns:

+-----+-----------------+
| id  | customer_number |
+-----+-----------------+
|   1 | a               |
| 200 | b               |
|   2 | c               |
+-----+-----------------+

If you use identity instead:

/* ------------- using identity ----------- */
create table organisation_identity
(
  id bigint not null identity (1,1),
  customer_number varchar(50) unique
);

insert into organisation_identity values
('a');

/* ------------- identity_insert on ----------- */
set identity_insert organisation_identity on;
insert into organisation_identity (id, customer_number) values
(200, 'b');
set identity_insert organisation_identity off;
/* ------------- identity_insert off ----------- */

insert into organisation_identity values
('c');

select * from organisation_identity;

returns:

+-----+-----------------+
| id  | customer_number |
+-----+-----------------+
|   1 | a               |
| 200 | b               |
| 201 | c               |
+-----+-----------------+

Sequence Reference:

Identity Reference:

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download