IDENTITY NOT NULL at Table Creation

Question

Can anyone please tell me whether the instruction IDENTITY NOT NULL at a table creation is redundant or not? I mean, judging by the message

DEFAULT or NULL are not allowed as explicit identity values.

I would say that any column declared as IDENTITY is implicitly also declared as NOT NULL, but I would like to make sure. Can anyone please confirm?

Thank you very much.

Answer 1

SQL Server adds NOT NULL constraint to identity columns automatically eventhough he did not speficy it when creating a table

Consider the following table script

create table test(id int identity(1,1), name varchar(1000))

Now Generate the script of the table from Management Studio. It generates the script as

CREATE TABLE [dbo].[test](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](1000) NULL
) ON [PRIMARY]

Eventhough NOT NULL constraint is not specified in the table script by default it is added. The identity column will never be NULL. So NOT NULL constraint is added default