Creating user-defined datatype with IDENTITY property

To create a user-defined datatype with the IDENTITY property, use sp_addtype. The new type must be based on a physical type of numeric with a scale of 0 or any integer type:

sp_addtype typename, "numeric (precision, 0)", "identity"

The following example creates a user-defined type, IdentType, with the IDENTITY property:

sp_addtype IdentType, "numeric(4,0)", "identity"

When you create a column from an IDENTITY type, you can specify either identity or not null—or neither one—in the create or alter table statement. The column automatically inherits the IDENTITY property.

Here are three different ways to create an IDENTITY column from the IdentType user-defined type:

create table new_table (id_col IdentType)
drop table new_table
create table new_table (id_col IdentType identity)
drop table new_table
create table new_table (id_col IdentType not null)
drop table new_table