When creating a sequence object in SQL Server, the default data type is bigint. However, we can change this so that the sequence uses a data type of our choosing, as long as it’s an integer type (see below for accepted data types).

We can set the data type of a sequence by using the AS argument when defining the sequence.

Example

Here’s an example to demonstrate:

CREATE SEQUENCE Sequence1 AS smallint;

In this case, I created a sequence using the smallint data type. I didn’t specify any properties, and so they all use the default options. Some of these default options are dictated by the data type. In our case, because we used the smallint data type, the MINVALUE and MAXVALUE properties will adhere to the limitations of that type. In other words, the MINVALUE will be -32,768 and the MAXVALUE will be 32,767.

This will also impact on the START WITH property, because the default start value for a new sequence object is the minimum value for an ascending sequence object and the maximum value for a descending sequence object.

We can query the sys.sequence system catalog view to check the values of these properties:

SELECT
    current_value,
    start_value,
    increment,
    minimum_value,
    maximum_value,
    is_cycling,
    is_cached,
    TYPE_NAME(system_type_id) AS system_type,
    TYPE_NAME(user_type_id) AS user_type,
    [precision],
    scale
FROM sys.sequences
WHERE name = 'Sequence1';

Result:

current_value  start_value  increment  minimum_value  maximum_value  is_cycling  is_cached  system_type  user_type  precision  scale
-------------  -----------  ---------  -------------  -------------  ----------  ---------  -----------  ---------  ---------  -----
-32768         -32768       1          -32768         32767          false       true       smallint     smallint   5          0    

As expected, the values of the aforementioned properties adhere to the smallint data type.

Accepted Data Types

A sequence can be defined as any integer type. The following types are allowed:

• tinyint – Range 0 to 255
• smallint – Range -32,768 to 32,767
• int – Range -2,147,483,648 to 2,147,483,647
• bigint – Range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
• decimal and numeric with a scale of 0.
• Any user-defined data type (alias type) that is based on one of the allowed types.