SQL Server allows only one IDENTITY column per table. The DATA TYPE of an IDENTITY column can be INT, BIGINT, SMALLINT, TINYINT, DECIMAL or NUMERIC. In case of NUMERIC, the scale should be defined as 0.

For example, all the declarations given below are valid.

DECLARE @t1 TABLE (id INT IDENTITY)
DECLARE @t2 TABLE (id BIGINT IDENTITY)
DECLARE @t3 TABLE (id SMALLINT IDENTITY)
DECLARE @t4 TABLE (id TINYINT IDENTITY) 
DECLARE @t5 TABLE (id DECIMAL IDENTITY)
DECLARE @t6 TABLE (id NUMERIC(8,0) IDENTITY)

However the following is invalid.

DECLARE @t6 TABLE (id NUMERIC(8,2) IDENTITY)

The following is a valid IDENTITY key declaration.

DECLARE @t4 TABLE (id numeric IDENTITY)

The following data types can store numeric values, but cannot be used as IDENTITY columns.

DECLARE @t4 TABLE (id bit IDENTITY)
DECLARE @t4 TABLE (id float IDENTITY)
DECLARE @t4 TABLE (id money IDENTITY)
DECLARE @t4 TABLE (id real IDENTITY)
DECLARE @t4 TABLE (id smallmoney IDENTITY)

Though you can define any of the six data types (INT, BIGINT, SMALLINT, TINYINT, DECIMAL and NUMERIC) as IDENTITY columns, you may not want to do that most of the times. Most people tend to use INT or BIGINT data type as IDENTITY columns. Using a smaller data type like TINYINT has a risk of IDENTITY value growing past the storage capacity of the associated data type. Since the IDENTITY value is managed and incremented by SQL Server, you need to make sure that the identity value does not grow beyond the max capacity of the given data type. The INSERT operation will fail if the new identity value generated is bigger than the data type can store.

DECLARE @Employees TABLE (id tinyint IDENTITY(100,100), Name VARCHAR(20))
INSERT INTO @Employees(Name) SELECT 'Jacob'
INSERT INTO @Employees(Name) SELECT 'Steve'
INSERT INTO @Employees(Name) SELECT 'Bob'

/*
Msg 8115, Level 16, State 1, Line 4
Arithmetic overflow error converting IDENTITY to data type tinyint.
Arithmetic overflow occurred.
*/

The third INSERT statement in the above example will FAIL because the IDENTITY value generated for the third row will be 300. The highest value a TINYINT data type can store is 255, hence the INSERT operation will fail.

The risk of IDENTITY value growing out of the capacity of the data type is more in cases where you delete and insert data frequently. The IDENTITY value will keep growing every time you insert a row. So it can happen that your table may have only a few thousand records, but your identity value will be in millions. This can also happen when you perform a lot of transaction ROLLBACKS. When you insert a row and ROLLBACK transaction, the IDENTITY key will not rollback. It will keep growing. I will show an example of this in another post.

You can run a query against the system metadata to retrieve a list of all IDENTITY columns in a given database. The following query retrieves all the IDENTITY columns in AdventureWorks database along with their table name and data type.

SELECT 
    OBJECT_NAME(object_id) AS TableName, 
    c.name AS ColumnName,
    t.name AS DataType
FROM sys.columns c
INNER JOIN sys.types t ON t.system_type_id = c.system_type_id
WHERE is_identity = 1

/*
TableName            ColumnName           DataType
-------------------- -------------------- --------------------
Address              AddressID            int
ProductReview        ProductReviewID      int
TransactionHistory   TransactionID        int
AddressType          AddressTypeID        int
ProductSubcategory   ProductSubcategoryID int
AWBuildVersion       SystemInformationID  tinyint
BillOfMaterials      BillOfMaterialsID    int
...                  ...                  ...
*/

Question to Readers: What data types do you usually use for IDENTITY columns?