How to make a Auto Increment Column in a table.
Here I am going to create a Table with a name 'inctable1'.In 'inctable1'I am creating 3 columns as 'name varchar, location varchar, id numeric. When a record is inserted in a Table 'inctable1' automatically 'id' column number should be generated and incremented automatically when another record is inserted.
How to set a 'id' Column as increment:
Select the id column, you can see below properties In properties set ' Identity' as 'YES' and set ' Identity Increment' value as 1.Here default 'Identity Increment' value is 1.
You can see below images:
When you execute a 'inctable1' it returns zero Rows.
Insert records in a 'inctable1'
Here I am inserting only two column values name and location. I am not going to insert a id column value. Because I had set a auotoincremant value when a record is inserted successfully.
When you Execute a 'inctable1'.it returns 3 records with a Identity increment 1 in id colomn.you can see below image to understand.
In above it 'id' column it is incrementing with 1. You can also set your own increment value.
Lets see a example Images:
Above I set an Identity increment value as 5.
After inserting some records records execute a 'inctable'and see the output it is increment by 5, from a third record.
Don't try to insert a value of Identity increment column.
Note: You can create a Identity Increment on numeric, smallint, tinyint, int, decimal, bigint data types only