[hibernate-dev] Trying to understand the significance and use of the sequence methods in Dialect

[Jason Pyeron]

Other than the javadocs, are these documented anywhere?

* public String getSequenceNextValString(String sequenceName)
* public String getSelectSequenceNextValString(String sequenceName)

I have been following their usage throughout the codebase, but it is not easy.

Is getSequenceNextValString only used in place of single values? E.g. INSERT INTO NEW_USER VALUES (SEQ_USER.NEXTVAL, 'Adams', 'John') ? Or are there other uses?

Is getSelectSequenceNextValString only used to get the "batch" for Hibernate's future issuance of ids?

The reason I am asking is we would like to leverage the MS SQL Server's sp_sequence_get_range . By using the approach of using the stored procedure for Hibernate's allocation and otherwise using an increment by 1 allows higher efficiency with the Hibernate based application and prevent breaking legacy access to the table/use of the sequence. This topic was unclearly broached in HHH-10130. Internet research below...

Take the following example:

CREATE SEQUENCE [dbo].[hibernate_sequence2] 
 AS [bigint]
 START WITH 1
 INCREMENT BY 1
 CACHE 200
;
CREATE TABLE [MyTable2]
(
    [ID] [bigint] PRIMARY KEY NOT NULL DEFAULT (NEXT VALUE FOR [dbo].[hibernate_sequence2]),
    [Title] [nvarchar](64) NOT NULL
);

insert into MyTable2 (Title) values ('test 1');
insert into MyTable2 (Title) values ('test 2');
insert into MyTable2 (Title) values ('test 3');
insert into MyTable2 (Title) values ('test 4');
select * from MyTable2;

--ID	Title
--1	test 1
--2	test 2
--3	test 3
--4	test 4

DECLARE @range_first_value_output sql_variant ;
declare @range_size int=50+1;  
DECLARE @sequence_increment  sql_variant ;   
declare @range_last_value sql_variant;

EXEC sys.sp_sequence_get_range  
@sequence_name = N'[dbo].[hibernate_sequence2]'
, @range_size = @range_size
, @range_first_value = @range_first_value_output OUTPUT
, @sequence_increment = @sequence_increment OUTPUT
, @range_last_value =  @range_last_value OUTPUT ;  
  
SELECT @range_first_value_output AS FirstNumber, @sequence_increment as sequence_increment, @range_last_value  as range_last_value  ;

--FirstNumber	sequence_increment	range_last_value
--     5     	        1         	       55

insert into MyTable2 (Title) values ('test 5');
select * from MyTable2;

--ID	Title
--1	test 1
--2	test 2
--3	test 3
--4	test 4
--56	test 5

I know there are issues of "knowing" the @SequenceGenerator(allocationSize), but I will assume a fixed 50 for now.

Internet research and other useless links...
1: Not hibernate, but same goal - https://groups.google.com/forum/#!topic/ebean/wG6VyVfEMQk
2: Not helpful - https://stackoverflow.com/questions/17780394/hibernate-identity-vs-sequence-entity-identifier-generators
3: Closest Hibernate Hit - https://hibernate.atlassian.net/browse/HHH-10130
4: My google searches - https://www.google.com/search?safe=off&q=%22sp_sequence_get_range%22+hibernate and https://www.google.com/search?safe=off&q=%22sp_sequence_get_range%22+jpa 
5: An interesting discussion, but not applicable - https://hibernate.atlassian.net/browse/HHH-10560
6: Sequences Added to SQL Server Dialect - https://hibernate.atlassian.net/browse/HHH-8440
7: My JIRA search - https://hibernate.atlassian.net/browse/HHH-6950?jql=(text%20~%20sequence%20or%20text%20~%20sp_sequence_get_range%20)%20and%20(text%20~%20SQLServer%20or%20text%20~%20%22SQL%20Server%22%20or%20text%20~%20SQLServer2012Dialect%20or%20text%20~%20SQLServer2008Dialect%20or%20text%20~%20SQLServer2005Dialect%20or%20text%20~%20SQLServerDialect)


-Jason


--
Jason Pyeron  | Architect
PD Inc        |
10 w 24th St  |
Baltimore, MD |
 
.mil: jason.j.pyeron.ctr at mail.mil
.com: jpyeron at pdinc.us
tel : 202-741-9397