Create table in sql server with auto increment primary key column

Introduction: In this article I am going to share How to create table in sql server database having primary key and auto increment column (IDENTITY)

Implementation: Let’s create the table having primary key and auto incremented column as:

                BookId INT IDENTITY(1,1) PRIMARY KEY,
                BookName VARCHAR(100) ,
                Author VARCHAR(100),
                Publisher VARCHAR(200),
                Price DECIMAL(18,2)

In above example BookId is Primary Key so it will be unique in the table and to specify that it should start at value 1 and increment by 1 I have written IDENTITY (1,1) . IDENTITY Keyword with seed or start value and increment value is used to set an auto increment column. Here seed value is 1 and increment value is 1.

Now Suppose we want to start the BookId with value 1000 and increment it by 1 then we need to set IDENTITY(1000,1). So now the starting value of BookId will be 1 and it will automatically increment by 1 for each record inserted in table.  For example first book record will have BookId 1000, second record will have 1001, third will have 1002 and so on.

Now let’s insert some data in above created table using the following queries

INSERT INTO tbBooks(BookName,Author,Publisher,Price) VALUES('Sql Server For Beginners','Lalit','Raghuvanshi Publications',1200)

INSERT INTO tbBooks(BookName,Author,Publisher,Price) VALUES('Sql Server Tips For Beginners','Neha','Neha Publications',1250)

INSERT INTO tbBooks(BookName,Author,Publisher,Price) VALUES('Learn Sql Server in 60 days','Rozy','Rozy Publications',1000)

Now check inserted data using the query

SELECT * from tbBooks

Output will be:
Sql Server For Beginners
Raghuvanshi Publications
Sql Server Tips For Beginners
Neha Publications
Learn Sql Server in 60 days
Rozy Publications
 Note: Have you noticed we have not inserted any value in BookId column still it showing 1, 2, 3 because It is being auto generated for each record inserted.

Points to remember
  • We must either set both the seed and increment value or none of them. If we don’t specify seed and incremental value then the default is (1,1).
  • IDENTITY Property can only be assigned to columns having INT data type and we can only create one identity column per table 

 Now over to you:
" I hope you have got the way to create sql server table with auto incremented primary key column and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates." 
Next Post »


Click here for comments
February 22, 2015 ×

how to diaplay the primary key error please reply me...

Congrats bro Anonymous you got PERTAMAX...! hehehehe...

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..