SQL Server AutoIncrement Issues

I am experimenting with SQL Server's AutoIncrement Functionality today, as I don't want to manually insert ID's into a database table every time I add a row, instead I would like to insert rows into the table and have the ID field automatically increment automatically based on each inserted row. I'm struggling getting the AutoIncrement functionality to work, as I am attempting to insert rows and I am getting the following error

     Cannot insert the value NULL into column 'PersonID', table      'MyLocalTestDataDB.dbo.Customers'; column does not allow nulls. INSERT fails.
     The statement has been terminated.

Let's start from scratch:

The MySQL Syntax is the following:
     CREATE TABLE Customers
     (
          PersonID int NOT NULL AUTO_INCREMENT,
          ...
     )

However, I'm using SQL Server, which uses a different Sytnax:

     CREATE TABLE Customers
     (
         PersonID int IDENTITY(1,1) PRIMARY KEY,
         ...
     )


The nice thing about this Identity([],[]) Syntax is that you have the flexibility to decide at which Number to begin incrementing from, and the interval between increments when each row is added.

IDENTITY(1,1) means the first row added will have an PersonID of 1, and each additional Row will be incremented by 1. (1,2,3, etc.)

IDENTITY(50,5) means the first row added will have a PersonID of 50, and each additional Row will be incremented by 5 (55, 60, 65, etc.)

You can make your Identity Column the Primary Key, or not make it the Primary Key if desired.


Back on track, why am I seeing the error?

It appears that the Column is not properly set as an AutoIncrement Column.  Since the column is a Primary Key, Null Values are not allowed.  When you insert a new row with an AutoIncrement Column, you can ignore adding data to the AutoIncrement column and let the AutoIncrement Functionality take over. 
In my case the AutoIncrement Column is also the Primary Key, so assuming the column is set to AutoIncrement, I can ignore populating data into this field with my T-SQL Insert Statement.

Since it's not being recognized as an AutoIncrement Column, it's interpreting the data entered into that column as null in the SQL Statement.  In the T-SQL Query Below, the PersonID Primary Key / AutoIncrement Field is intentionally absent from the query, under the assumption that the AutoIncrement behavior will take over.

     USE MyLocalTestDataDB;
     INSERT INTO [MyLocalTestDataDB].[dbo].[Customers] (LastName, FirstName, Address, City)
     VALUES ('Holmes', 'Sherlock', '221B Baker Street', 'London');

I performed some investigation. One potential approach is to validate and set the column as auto-increment in SQL Server Management Studio per the following steps:

Open your table in Design View




Select the Column and go to Column Properties
In the 'Identity Specification' section, set 'Is Identity' to 'Yes, AND 'Identity Increment' to '1'




When I try to save this table, I get the following message, preventing me from saving the changes:




This appears to effectively be the UI Equivalent of the T-SQL approach, and It appears that you are forced to re-create the table anyway.
          PersonID int IDENTITY(1,1) PRIMARY KEY

What was my Resolution?
I recreated the table with T-SQL instead of the UI and it worked perfectly.

     DROP TABLE Customers
     CREATE TABLE Customers
     (
          PersonID int IDENTITY(1,1) Primary Key,
          ...
     );

     USE MyLocalTestDataDB;
     INSERT INTO [MyLocalTestDataDB].[dbo].[Customers] (LastName, FirstName, Address, City)
     VALUES ('Holmes', 'Sherlock', '221B Baker Street', 'London');

Message:
     (1 row(s) affected)


What happened?
I potentially may have created the table without the AutoIncrement Definition IDENTITY(1,1) without realizing it.

OR:

My hypothesis is that because I created the table without the IDENTITY(1,1)
          PersonID int PRIMARY KEY
Then deleted and recreated using the IDENTITY(1,1)
          PersonID int IDENTITY(1,1) PRIMARY KEY
and somehow it got cached, or SQL Server Management Studio wasn't recognizing that I re-created it with the Identity(1,1).

At any rate, when in doubt, re-create the table! Assuming that this wasn't user error (which certainly is a possibility!), this solution might be the SQL Equivalent to the Windows Restart (when all else fails, reboot!).

I hope this may have been helpful for someone.