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:
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
If you try to create a column that allows nulls from
an IDENTITY type, the create table or alter
table statement fails.
