Create IDENTITY Columns with User-Defined Datatypes

You can use user-defined datatypes to create IDENTITY columns. The user-defined datatype must have an underlying type of numeric and a scale of 0, or any integer type.

If the user-defined datatype was created with the IDENTITY property, you do not have to repeat the identity keyword when creating the column.

This example shows a user-defined datatype with the IDENTITY property:

sp_addtype ident, "numeric(5)", "identity"

This example shows an IDENTITY column based on the ident datatype:

create table sales_monthly
    (sale_id ident, stor_id char(4) not null)

If the user-defined type was created as not null, you must specify the identity keyword in the create table statement. You cannot create an IDENTITY column from a user-defined datatype that allows null values.