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)

In previous articles i explained Sql server query to get second,third,fourth or nth highest salary of employee and Backup and restore sql server database  and 20 main differences between Stored procedures and Functions and Sql server stored procedure for insert,update,delete,bind and search operation in table and SELF JOIN in SQL SERVER with example 

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

CREATE TABLE tbBooks

(

                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:

BookId	BookName	Author	Publisher	Price
1	Sql Server For Beginners	Lalit	Raghuvanshi Publications	1200.00
2	Sql Server Tips For Beginners	Neha	Neha Publications	1250.00
3	Learn Sql Server in 60 days	Rozy	Rozy Publications	1000.00

 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." 

• Tweet
• Share
• Share
• Share

Related Post