This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Design and develop database solutions (35–40%)
   --> Design and implement database objects
      --> Design and Implement SEQUENCES

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

Many database applications require automatically generated numeric values for records such as order numbers, invoice numbers, customer identifiers, shipment IDs, and transaction references. While SQL Server developers have traditionally relied on IDENTITY columns to generate sequential numbers, SQL Server also provides a more flexible object called a SEQUENCE.

A SEQUENCE is a user-defined database object that generates a sequence of numeric values according to rules that you specify. Unlike an IDENTITY column, a SEQUENCE is independent of any table and can be shared across multiple tables or applications.

For the DP-800: Developing AI-Enabled Database Solutions certification exam, you should understand:

• What SEQUENCE objects are
• How SEQUENCES differ from IDENTITY columns
• How to create and use SEQUENCES
• Sequence options such as START WITH, INCREMENT BY, MINVALUE, MAXVALUE, CYCLE, and CACHE
• Performance considerations
• Best practices and common use cases

Understanding SEQUENCES is important because they provide greater flexibility for generating unique numeric values across modern SQL Server and Azure SQL Database solutions.

What Is a SEQUENCE?

A SEQUENCE is a schema-bound database object that generates a series of numeric values.

Unlike an IDENTITY column:

• It is independent of tables.
• Multiple tables can use the same SEQUENCE.
• Values can be generated before an INSERT occurs.
• Applications can request values whenever needed.

The database maintains the current value of the sequence.

Common Use Cases

SEQUENCES are commonly used for:

• Invoice numbers
• Purchase order numbers
• Ticket numbers
• Customer IDs across multiple tables
• Order tracking numbers
• Shipment numbers
• Financial transaction identifiers
• Distributed applications
• Data warehouse surrogate keys

SEQUENCE vs. IDENTITY

A common DP-800 exam objective is knowing when to choose a SEQUENCE instead of an IDENTITY column.

Creating a SEQUENCE

Basic syntax:

CREATE SEQUENCE dbo.OrderSequence
AS INT
START WITH 1
INCREMENT BY 1;

This sequence:

• Starts at 1
• Increments by 1
• Generates INT values

Using NEXT VALUE FOR

Values are generated using the NEXT VALUE FOR function.

Example:

SELECT NEXT VALUE FOR dbo.OrderSequence;

Output:

1

The next execution returns:

2

Then:

3

Each call advances the sequence.

Using a SEQUENCE During INSERT

Example:

INSERT INTO Orders
(
    OrderID,
    CustomerID
)
VALUES
(
    NEXT VALUE FOR dbo.OrderSequence,
    1001
);

The generated sequence value becomes the OrderID.

Sharing a SEQUENCE Across Multiple Tables

One of the biggest advantages of SEQUENCES is that multiple tables can use the same object.

Example:

OrderSequence
       │
 ┌─────┴─────┐
 │           │
Orders   ArchivedOrders

Both tables generate identifiers from the same sequence.

This guarantees unique values across both tables.

Choosing the Data Type

Supported numeric types include:

• TINYINT
• SMALLINT
• INT
• BIGINT
• DECIMAL
• NUMERIC

Example:

CREATE SEQUENCE dbo.InvoiceSequence
AS BIGINT;

Choose a type large enough for expected future growth.

START WITH

The START WITH clause specifies the first value.

Example:

CREATE SEQUENCE dbo.InvoiceSequence
AS INT
START WITH 1000;

Generated values:

1000
1001
1002

INCREMENT BY

Defines how much the sequence changes.

Example:

INCREMENT BY 10

Generated values:

10
20
30
40

Negative increments are also supported.

Example:

INCREMENT BY -1

Produces:

100
99
98
97

MINVALUE and MAXVALUE

A sequence can define minimum and maximum values.

Example:

CREATE SEQUENCE dbo.SmallSequence
AS INT
MINVALUE 1
MAXVALUE 100;

After reaching the maximum value, behavior depends on whether CYCLE is enabled.

CYCLE Option

The CYCLE option restarts the sequence after reaching its maximum (or minimum for descending sequences).

Example:

CREATE SEQUENCE dbo.TestSequence
AS INT
START WITH 1
MAXVALUE 5
CYCLE;

Generated values:

1
2
3
4
5
1
2

Without CYCLE, requesting another value after reaching the limit results in an error.

Use CYCLE only when reused values are acceptable.

NO CYCLE

NO CYCLE is the default behavior.

Example:

CREATE SEQUENCE dbo.OrderSequence
AS INT
NO CYCLE;

Once the maximum value is reached, SQL Server raises an error rather than restarting.

This is appropriate for identifiers that must remain unique.

CACHE Option

To improve performance, SQL Server can cache sequence values in memory.

Example:

CREATE SEQUENCE dbo.OrderSequence
AS INT
CACHE 100;

Benefits:

• Fewer disk writes
• Higher throughput
• Better scalability

Trade-off:

If SQL Server stops unexpectedly, cached values that were not used are lost, resulting in gaps in the sequence.

NO CACHE

Disables sequence caching.

Example:

NO CACHE

Benefits:

• Reduces gaps caused by unexpected shutdowns

Trade-offs:

• Slightly slower performance
• Increased metadata updates

Restarting a SEQUENCE

A sequence can be restarted.

Example:

ALTER SEQUENCE dbo.OrderSequence
RESTART WITH 5000;

The next generated value will be 5000.

This is useful after data migrations or when implementing new numbering schemes.

Altering a SEQUENCE

Existing sequences can be modified.

Example:

ALTER SEQUENCE dbo.OrderSequence
INCREMENT BY 5;

Future values increase by 5.

Dropping a SEQUENCE

Example:

DROP SEQUENCE dbo.OrderSequence;

This removes the sequence object from the database.

Obtaining Multiple Sequence Values

Applications can retrieve sequence values before performing inserts.

Example:

DECLARE @OrderID INT;
SET @OrderID =
NEXT VALUE FOR dbo.OrderSequence;

This is useful when:

• Creating parent-child records
• Generating invoice numbers
• Passing identifiers between services
• Building distributed workflows

Sequence Gaps

An important exam concept is that SEQUENCES do not guarantee gap-free numbering.

Gaps may occur because of:

• Transaction rollbacks
• Application failures
• Cached values lost during server restart
• Deleted rows
• Unused generated values

Therefore, SEQUENCES should not be used when legal or regulatory requirements demand consecutive numbers with no gaps.

Performance Considerations

SEQUENCES generally perform very well.

Performance is improved by:

• Using CACHE
• Selecting appropriate data types
• Avoiding unnecessary contention
• Sharing sequences when appropriate

High-volume OLTP systems often use cached sequences for improved throughput.

SEQUENCES in Distributed Applications

Because SEQUENCES are independent objects, they are useful in distributed architectures.

Examples include:

• Microservices
• Azure Functions
• Event-driven systems
• Service Bus workflows
• Multi-table transactional systems

Applications can reserve identifiers before inserting data.

AI-Enabled Database Scenarios

Although SEQUENCES are not AI-specific, they are useful in AI-enabled database solutions for generating unique identifiers for:

• AI inference requests
• Prompt execution logs
• Conversation sessions
• Vector embedding batches
• Training jobs
• Experiment tracking
• Model evaluation records

Using a shared sequence ensures consistent identifiers across related AI components.

Best Practices

• Use SEQUENCES when multiple tables require a common numbering scheme.
• Use BIGINT if long-term growth is expected.
• Use CACHE for high-throughput transactional workloads.
• Avoid relying on sequence values being gap-free.
• Do not use CYCLE for primary keys or other values that must remain globally unique.
• Choose START WITH carefully to accommodate business requirements.
• Document shared sequences to prevent accidental reuse.
• Monitor sequence exhaustion when using small numeric data types.
• Restart sequences only after careful planning.

Common Exam Tips

For the DP-800 exam, remember these key facts:

• A SEQUENCE is a database object, not a table property.
• NEXT VALUE FOR retrieves the next sequence value.
• Multiple tables can share the same SEQUENCE.
• SEQUENCES can generate values before an INSERT statement.
• CACHE improves performance but may introduce gaps after an unexpected shutdown.
• Transaction rollbacks do not return consumed sequence values.
• CYCLE restarts a sequence after reaching its limit; NO CYCLE raises an error instead.
• SEQUENCES are often preferred over IDENTITY when values must be shared across tables or generated outside of INSERT operations.

Practice Exam Questions

Question 1

A developer needs a single numbering mechanism that can generate unique identifiers for both the Orders and ArchivedOrders tables. Which feature should be used?

A. A DEFAULT constraint

B. An IDENTITY column

C. A computed column

D. A SEQUENCE

Answer: D

Explanation: A SEQUENCE is an independent database object that can be shared by multiple tables, making it ideal for generating unique identifiers across related tables.

Question 2

Which statement best describes a SEQUENCE object?

A. It is bound to a single table and generates values only during INSERT operations.

B. It can only generate BIGINT values.

C. It automatically creates a clustered index.

D. It is an independent database object that generates numeric values according to defined rules.

Answer: D

Explanation: A SEQUENCE is a standalone database object that can generate numeric values independently of any table and supports several numeric data types.

Question 3

Which function retrieves the next available value from a SQL Server SEQUENCE?

A. NEXT IDENTITY

B. GET NEXT

C. NEXT VALUE FOR

D. CURRENT VALUE

Answer: C

Explanation: The NEXT VALUE FOR function retrieves and advances a SEQUENCE to its next value.

Question 4

Why might a developer choose a SEQUENCE instead of an IDENTITY column?

A. Because SEQUENCES cannot contain gaps.

B. Because a SEQUENCE automatically enforces referential integrity.

C. Because a SEQUENCE can generate values before an INSERT and be shared across multiple tables.

D. Because SEQUENCES automatically create foreign keys.

Answer: C

Explanation: Unlike an IDENTITY column, a SEQUENCE is independent of tables and can generate values before inserts, making it useful across multiple tables or applications.

Question 5

What is the primary benefit of enabling the CACHE option on a SEQUENCE?

A. It guarantees gap-free numbering.

B. It improves performance by reducing metadata updates.

C. It automatically encrypts sequence values.

D. It prevents transaction rollbacks.

Answer: B

Explanation: Caching sequence values reduces the frequency of metadata updates, improving throughput. However, cached values may be lost during an unexpected shutdown, creating gaps.

Question 6

Which statement about sequence values is correct?

A. Sequence values are returned to the pool if a transaction rolls back.

B. Sequence values are always consecutive with no gaps.

C. Transaction rollbacks do not reclaim sequence values that have already been generated.

D. Sequence values can only be generated during INSERT statements.

Answer: C

Explanation: Once a sequence value is generated, it is consumed. If a transaction later rolls back, that value is not reused, so gaps are expected.

Question 7

A SEQUENCE is created with MAXVALUE 5 and the CYCLE option enabled. What happens after the value 5 is generated?

A. SQL Server raises an error.

B. The sequence automatically restarts at its minimum (or starting) value.

C. The sequence becomes read-only.

D. SQL Server automatically increases the maximum value.

Answer: B

Explanation: The CYCLE option causes a sequence to restart after reaching its maximum value rather than generating an error.

Question 8

Which statement about the NO CYCLE option is correct?

A. It causes sequence values to restart automatically.

B. It caches all generated values.

C. It allows duplicate sequence values.

D. It prevents the sequence from restarting after reaching its limit and raises an error instead.

Answer: D

Explanation: NO CYCLE is the default behavior. Once the sequence reaches its maximum or minimum value, SQL Server raises an error instead of restarting the sequence.

Question 9

Which of the following is a common use case for a SEQUENCE?

A. Automatically maintaining historical versions of rows

B. Enforcing referential integrity

C. Generating invoice numbers shared across multiple applications

D. Validating JSON documents

Answer: C

Explanation: SEQUENCES are frequently used to generate shared numbering schemes, such as invoice numbers, order numbers, or ticket identifiers across multiple systems.

Question 10

A developer uses a cached SEQUENCE to generate order numbers. After an unexpected SQL Server restart, several sequence values are missing. What is the most likely explanation?

A. The PRIMARY KEY constraint removed duplicate values.

B. Transaction rollbacks deleted the missing values.

C. The sequence automatically renumbered existing rows.

D. Cached sequence values that had not yet been issued were lost during the restart.

Answer: D

Explanation: Cached sequence values are stored in memory to improve performance. If SQL Server stops unexpectedly, any unused cached values are lost, resulting in gaps in the generated sequence.

Go to the DP-800 Exam Prep Hub main page