dbatools bring us a set of useful commands to manage sequence objects in SQL Server. In this article, we are going through the five new commands. The commands are shown below.

# get commands that have something to do with sequence within dbatools module
Get-Command -Module dbatools -Name *sequence*
<#
CommandType     Name                                               Version    Source                            
-----------     ----                                               -------    ------                            
Function        Get-DbaDbSequence                                  1.1.14     dbatools                          
Function        New-DbaDbSequence                                  1.1.14     dbatools                          
Function        Remove-DbaDbSequence                               1.1.14     dbatools                          
Function        Select-DbaDbSequenceNextValue                      1.1.14     dbatools                          
Function        Set-DbaDbSequence                                  1.1.14     dbatools        
#>

What's a Sequence?

A user object called a sequence generates a series of numeric values based on specified criteria. While it closely resembles the identity property of a column, it is not limited to a single table. SQL Server 2012 introduced the concept of sequences.

dbatools

The dbatools module is a free, open-source project designed to help database administrators (DBAs) manage SQL Server more easily. You can find more information about the project on its website.

Shameless plug: I have written blog posts about some functions before, but not sequence functions. Now that they're available, this article discusses them.

New-DbaDbSequence

To quickly create a default sequence with a starting value of 1 and an increment value of 1, execute the following command. This command uses the bigint datatype by default and generates the sequence in the dbo schema.

# the minimum set of parameters to create a default sequence
New-DbaDbSequence -SqlInstance $s1 -Database Mikey -Name Seq01Default
<#
ComputerName   : localhost
InstanceName   : MSSQLSERVER
SqlInstance    : localhost
Database       : Mikey
Schema         : dbo
Name           : Seq01
DataType       : bigint
StartValue     : 1
IncrementValue : 1
#>

Default settings may not always suffice. The command allows us to adjust start and increment values, as well as a datatype. It accepts all integer types, including user-defined ones. We'll begin by creating a simple datatype alias, using another dbatools command to execute T-SQL queries.

# create an alias for an integer datatype
$newIntegerType = "CREATE TYPE teenyint FROM tinyint NOT NULL;"
Invoke-DbaQuery -SqlInstance $s1 -Database Mikey -Query $newIntegerType

Next, we'll create custom parameters to test the command's flexibility.

# creating a customized sequence
New-DbaDbSequence -SqlInstance $s1 -Database Mikey -Schema sch01 -Name Seq02Teenyint -StartWith 0 -IncrementBy 5 -IntegerType teenyint

As we can see above, we've added a schema name (-Schema). If the schema does not already exist in the database, the command will take care of that and create it. Since we have created the custom integer data type we can use it too (-IntegerType), as well as custom start (-StartWith) and increment values (-IncrementBy).

Finally, with the command, we can control if the sequence should cycle (-Cycle) the values once it reaches the end of the range or the maximum value.

# allow cycling the sequence values
New-DbaDbSequence -SqlInstance $s1 -Database Mikey -Name Seq03Cycle -StartWith 255 -IntegerType tinyint -Cycle

In this example, we are using the tinyint data type with a range of values from 0 to 255. So what will happen when we reach 255?

That leads us to the next command.

Select-DbaDbSequenceNextValue

With this command, we can obtain the current sequence number.

Note that the Seq03Cycle sequence after reaching the limit of tinyint will start from 0 (smallest value of the tinyint range), as we configured it with a cycle.

# get the next available value
Select-DbaDbSequenceNextValue -SqlInstance $s1 -Database Mikey -Name Seq01Default
Select-DbaDbSequenceNextValue -SqlInstance $s1 -Database Mikey -Schema sch01 -Name Seq02Teenyint 
Select-DbaDbSequenceNextValue -SqlInstance $s1 -Database Mikey -Name Seq03Cycle
<# first run
1
0
255
#>
<# second run
2
5
0
#>

Now, to see what sequences we have in our systems we can use the Get- command.

Get-DbaDbSequence

We can list the objects on the SQL Server instance level or database level. And, with additional parameters -Schema and -Sequence we can be even more specific.

# List all the sequences on the SQL instance
Get-DbaDbSequence -SqlInstance $s1 | Format-Table
ComputerName InstanceName SqlInstance  Database Schema Name         DataType StartValue IncrementValue
------------ ------------ -----------  -------- ------ ----         -------- ---------- --------------
localhost    MSSQLSERVER  localhost    model    dbo    Seq04model   tinyint         255              1
localhost    MSSQLSERVER  localhost    Mikey    dbo    Seq01Default bigint            1              1
localhost    MSSQLSERVER  localhost    Mikey    dbo    Seq03Cycle   tinyint         255              1

Set-DbaDbSequence

The set command allows us to update the properties of the sequence or restart it from a supplied value. Also, -Confirm parameter can be used to skip the prompt.

# restart a specific sequence
Set-DbaDbSequence -SqlInstance $s1 -Database Mikey -Name Seq01Default -RestartWith 1 -Confirm:$false

Remove-DbaDbSequence

When you are done with the sequence there is an easy way to drop it.

# Drop all sequences
Get-DbaDbSequence -SqlInstance $s1 | Remove-DbaDbSequence

Conclusion

The dbatools module brings a range of useful commands for managing sequences in SQL Server, including creating, modifying, and deleting them.