Why is SQL Server sequence jumping by two when fetched from Java code?

user_10586727 0
Viewed 108

Let's say there is a sequence created on SQL Server:

CREATE SEQUENCE dbo.my_seq
    START WITH 1
    INCREMENT BY 1
    NO CYCLE;
GO

And the following Java code to fetch the next sequence value:

Connection conn = ...;
String sql = "SELECT NEXT VALUE FOR my_seq;";

try (Statement statement = conn.createStatement();
        ResultSet resultSet = statement.executeQuery(sql)) {
    while (resultSet.next()) {
        long seq = resultSet.getLong(1);

        System.out.println(seq);
    }
}

Why are sequences jumping by two when this code is executed repeatedly?

2
4
6

I've tried with the CACHE option on and off. It makes no difference.

Sequences are incremented by one if I execute the same query multiple times on Azure Data Studio.

I'm running Microsoft SQL Server 2019 (RTM-CU15) (KB5008996) - 15.0.4198.2 (X64). I tried the same code with com.microsoft.sqlserver:mssql-jdbc:10.2.0.jre8 and com.microsoft.sqlserver:mssql-jdbc:11.1.1.jre8-preview drivers and got the same behavior.

I analyzed the SQL Server query history, and the Java code made only one query to fetch the next sequence value per execution.

jdbc sql-server java
1 Answers

According to Gary's answer to a similar question, this is a known behavior when using the selectMethod=cursor option. Just remove this option from the connection URL, and sequence numbers won't skip anymore.

What if you must use selectMethod=cursor for some reason? Then try the sys.sp_sequence_get_range stored procedure instead as demonstrated below.

Connection conn = ...;
String sequenceName = "my_seq";

try (CallableStatement statement = conn.prepareCall("{call sys.sp_sequence_get_range(?, ?, ?)}")) {
    statement.setString("sequence_name", sequenceName);
    statement.setLong("range_size", 1);
    statement.registerOutParameter("range_first_value", microsoft.sql.Types.SQL_VARIANT);
    statement.execute();

    long seq = statement.getLong("range_first_value");
    
    System.out.println(seq);
}

It doesn't skip sequence numbers even if the selectMethod=cursor option is enabled.

user_105867270
Related