Applies to
ApexSQL Generate
Summary
This article explains how to generate sequential data in ApexSQL Generate by using the Incremental generator and shows some special cases regarding the settings of this generator.
The incremental generator creates a sequence of values from start up to end value, with the step specified by the increment value.
The generated sequence can go in ascending or descending order. The order is decided by the difference between start and end values. If the start value is lower than end value, the generated sequence goes in incremental order, and vice versa.
In the case of generating the sequence of SQL Datetime values starting from 1/1/2015 12:00:00 AM up until 12/31/2015 12:00:00 AM and with increment of 15 days, the generated sequence will go in incremental order.
The first 8 values of the generated sequence are shown on the image below.
If the start and end value switch places, i.e. start value is 12/31/2015 12:00:00 AM and end value becomes 1/1/2015 12:00:00 AM, but increment step stays the same (15 days), the generated sequence will go in decrementing order, which can be detected in the label next to the value of increment step.
The first 8 values of the generated sequence created with this setup is shown on the image below.
One of the special cases in generating the sequence of values can be when start and end values are the same. In this situation, only one row will be generated, containing the value set as start/end point of sequence. This is a special case because no matter how many rows for the selected column are requested, the generated sequence can create only one value, so the global number of rows for the whole table containing the selected column is reduced to 1. The same thing happens in every case when the sequence cannot create enough values to fill the requested number of rows for the table.
In this example the start value is set to 1/1/2015 12:00:00 AM, end value to 2/28/2015 12:00:00 AM, increment step is 15 days and the requested number of rows for the table containing the selected column is set to 8.
The sequence generated with this setup can contain only 4 values, shown on the image below.
To create enough values to fill the requested number of rows for the table containing the selected column (8 rows in this example), the Loop option needs to be checked. This repeats the generated sequence until the requested number of values is generated.
In the case when the increment step is set to 0, the generated sequence will create as much values as it is requested by the number of rows set for the table containing the selected column, but those values will all be the same and will be equal to the start value of the sequence.
Q: How does it work?
A: Incremental generator creates a sequence of values from the start value incremented or decremented in each step by the increment value until the end value is reached. Whether the start value will be incremented or decremented is decided by the end value. If the end value is higher than start value, the generated sequence will be in incremental order, and vice versa.
Q: Can I use it to get values in decrement order?
A: Yes. When the start value of Incremental generator is set to be higher than end value, the generated sequence will be in decrement order. When this happens, the text next to the increment value is changed from “Increment” to “Decrement”.
Q: Can the increment step vary, e.g. be doubled each time the next value is calculated?
A: No, the step is static, and its value is assigned through the Increment.
Q: What are the data types I can use Incremental generator for?
A: Incremental generator can be used for all numeric (int, float, real, money etc), datetime (date, time, datetime2, datetimeoffset, smalldatetime), binary, spatial (geography, geometry) and GUID (unique identifier) data types.
Q: What could I use this type of generator for?
A: The incremental generator can be used for generating example data with constant offset of each next value comparing to the previous. This can be used for comparing the variations of real life data against pre-set values. For example, calculating the regularity of real product release dates in production database against the planned “1 release in a month” schedule in test database.
Q: What happens when I reach the maximum value, but still don’t have enough values to fill my desired row count?
A: In this case, only the number of values which satisfy the conditions given by the start, end and increment value will be generated, and the row count will be reduced to that number. This can be overridden with the use of the Loop option which repeats the sequence until reaching the desired number of rows.
Q: What happens when I set the maximum value too high, but I need only few rows? Will it generate more rows that I requested?
A: No, the number of values generated by Incremental generator will match the requested number or rows. The rest of the values above the requested number will be unused.
