auto increment in table in sql server

Auto increment in table in sql server

Hell friends, we will learn here how to set auto increment in table. For this we can use both design and sql query.

See the image for more details:

add auto increment to the table in sql server
add auto increment to the table in sql server

First of all add the columns as shown in the figure and and mind one thing that the column must have datatype bigint or a numeric type like int.

Select the column and set column properties as shown in the picture:

add identity specification to the column
add identity specification to the column

Now expand the Identity specification as shown in the picture and double click on the Is Identity see the results in the picture.

set autoincrement to one
set auto increment to one

There are two most important thing in setting auto increment :

  1. auto increment : The number indicate the number  which will be incremented after each insert. Here is auto increment value 1. So the every time we will insert any records, the column value is increased by 1. You can change it as per your requirement.
  2. Identity Seed: This will set the starting point the auto increment column say if we will set it 1000, then the next record will hold 1001, 1002 and so on.

To change the Identity seed which is also called reseed(or reset the auto increment)have to use the below sql query:

DBCC CHECKIDENT('[table_name]', RESEED, [new_reseed_value])
DBCC CHECKIDENT('tblUser', RESEED, 1000)


Now its query time to set auto increment value.  Use following query to set auto increment value to any column.

CREATE TABLE [dbo].[table_autoincrement](
 [auto_id] [bigint] IDENTITY(1,1) NOT NULL,
 [normalcolumn] [bigint] NOT NULL,
 [vacharcolumn] [varchar](50) NULL
)

Happy coding...




How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: