In SQL Server, the sp_sequence_get_range system stored procedure is used to retrieve a range of values from a specified sequence object. This is particularly useful when you want to get more than one value at a time, which can improve performance for scenarios where multiple identifiers are needed, such as inserting multiple rows into a table.
Here’s how to use sp_sequence_get_range:
Syntax
EXEC sp_sequence_get_range
@sequence_name = 'schema.sequence_name',
@range_size = size,
@start_value = @start_value OUTPUT,
@end_value = @end_value OUTPUT;
Parameters
- @sequence_name: The name of the sequence object, including its schema (for example, 'dbo.MySequence').
- @range_size: The number of values you want to retrieve from the sequence.
- @start_value: An output parameter that will return the starting value of the range.
- @end_value: An output parameter that will return the ending value of the range.
Example
Here is an example of how to use sp_sequence_get_range to get a range of values from a sequence named MySequence:
DECLARE @start_value BIGINT;
DECLARE @end_value BIGINT;
EXEC sp_sequence_get_range
@sequence_name = 'dbo.MySequence',
@range_size = 10,
@start_value = @start_value OUTPUT,
@end_value = @end_value OUTPUT;
SELECT @start_value AS StartValue, @end_value AS EndValue;
Explanation
1. Declare Variables: You declare @start_value and @end_value as output parameters to hold the start and end values of the retrieved range.
2. Execute the Stored Procedure: The EXEC command calls sp_sequence_get_range, specifying the sequence name and the desired range size (10 in this case). The output parameters are assigned values from the procedure.
3. Retrieve Values: After executing the stored procedure, you can select the start and end values to see the retrieved range.
Notes
- The values returned will be sequential and based on the increment value defined for the sequence.
- Ensure that you have the necessary permissions to execute this procedure and access the sequence.
- If you call the procedure again, it may not give you the same start and end values, especially if the sequence has been altered or new values have been generated in other sessions.
This is how you can efficiently retrieve a range of values from a sequence object in SQL Server.