Chapter 6: Using and Creating Datatypes

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

If you try to create a column that allows nulls from an IDENTITY type, the create table or alter table statement fails.

Creating IDENTITY columns from user-defined datatypes

View this book as PDF