What is Auto-Increment?
It is very common for databases to be used to store data that needs to be organized. As an example and for the use of this article we’ll pretend to have an online store that sells t-shirts.
When a new customer creates an account on our store we may want to store information about this individual such as their first name, last name, and preferred t-shirt size. This means the table that stores the users information would need to have a column specifically for first name, last name, and t-shirt size. This would look like the below:
First_Name Last_Name TSize
John Smith Large
Michael Johnson Medium
Stacey Thompson Small
John Carter Medium
When performing server-side scripting we want an easier way to pull data for a specific user. We can’t perform a SELECT statement pulling the right T-shirt size for the user named John as there are many users in the database with a similar name.
For these types of cases it’s a great practice to add an additional column with an ID for the individual user. The ID needs to be unique and not be re-usable for more than one user or you can run into issues. By setting a column within the table to use auto increment we are able to perform this automatically by the SQL server without any additional programming needed from a development perspective.
Note: Additionally by having a unique primary key and identity using Auto-Increment you will have better performance for querying the database when you have enabled indexing, which is highly recommended.
ID First_Name Last_Name TSize
1 John Smith Large
2 Michael Johnson Medium
3 Stacey Thompson Small
4 John Carter Medium
The above table is an example of auto-incrementing by 1, as each time a new user is created in the table it uses the next available ID. This makes it much easier programmatically to pull data for a specific user. You can now pull the T-Shirt size specific to the user with the ID of ‘4’ for example, which would be “John Carter”.
You can also auto-increment by values different than 1. The default setting for Auto-Increment is 1, but this can easily be changed when creating your table or modifying the table. You can also start the auto-increment value at a different number. For example if set the identity seed to 1001 and have the increment value set to 10 then the first entry in the database would be 1001 and the next would be 1011, 1021, 2031, etc…
Note: In most cases the default seed values of (1,1) are used.
Creating tables with auto-increment
There are two ways to create tables in your Microsoft SQL database. We’ll provide instructions on enabling auto-increment for both scenarios below:
Create Tables via T-SQL:
When using T-SQL (Microsofts SQL syntax) you can specify the IDENTITY attribute. It’s also recommended that the ID fields are the primary key for a table so we’ll include that in our syntax. If you don’t need it to be a primary then you can remove this from the syntax.
CREATE TABLE Store_Users
(
User_ID int IDENTITY(1,1) PRIMARY KEY,
FirstName varchar(255) NOT NULL,
LastName varchar(255),
TSize varchar(255)
)
The above query would create a table (Store_Users) that would store user data such as their first name, last name, and t-shirt size. For each user it would store a unique ID within the User_ID column.
Create Tables via SSMS Interface
The second way that tables are usually created are via the graphical interface of SSMS. It’s also recommended that the ID fields are the primary key for a table so we’ll include that in our example. If you don’t need it to be a primary then you can remove this from the options.
To get to the table creation expand your database and right click on Tables, then choose Table…
The above screenshot shows we are creating a table (Store_Users) that would store user data such as their first name, last name, and t-shirt size. For each user it would store a unique ID within the User_ID column since we set the identity column attribute for this column.
If you want to update the SEED value or increment options you can see the Column Properties (by default located below the table column names you are creating). You would want to select the table in question (User_ID in this case) and then expand Identity Specification. You will then see the options for:
(Is Identity): Enabling this to ‘Yes’ will make this column auto-increment.
Identity Increment: This value will determine by how much each new row in the table increments by. The default is 1, which is normally the value most applications require.
Identity Seed: This value determines what value the auto-increment should start at when the table is created. The default is 1, which means the first row in the table will start at 1.
Example: If you set the Identity Increment to 10 and the Identity Seed to 1011, then the first row in the table will be 1011 and the next would be 1021, 1031, 2041, etc…
View existing auto increment values
It is common for developers to inquire about the existing auto increment values. This can easily be done with the below query:
DBCC CHECKIDENT ('User_Info', NORESEED);Note: Be sure to update User_Info with the correct table name.
The above query would check the values of the current identity value and the current column value. The answer should be similar to the following:
“Checking identity information: current identity value ‘4’, current column value ‘4’.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.”
As shown in the example output above the table is reporting that the last row in the table is using the value of ‘4’, which should be correct since the current identity value for the seed is 4. If the identity value is lower than the current column value you could run into issues when adding new rows to the table. In most cases these values should be the same integer.
Change existing table auto-increment settings
There are cases where a table needs to have their auto-increment seed values updated. In most cases this could be due to a database import (such as a backup) messing up the values, or possibly a unexpected server reboot that did not have a clean stop of the SQL services causing a corruption of the auto-increment settings.
It is recommended to first run the query mentioned in the section above to determine the values for the current identity and column integers. You can also run a SELECT statement and pull the last 10 or so items from the table and order them by DESC so you can see the last few rows of the table (to determine the last rows ID so you can set the new value of auto-increment properly).
Change via T-SQL
To change the existing seed values you can run the command below:
DBCC CHECKIDENT ('User_Info', RESEED, 4);Note: Be sure to change the 4 in the above query to the actual value you want to set the seed value to. If your last row in the table has an ID of 10 for example, then you’d most likely want to set the reseed to a value of 10. You will also want to be sure to change the value of User_Info to your actual table name.
Change via SSMS
Alternatively to T-SQL you can use SSMS table designer tool to change the current value (as well as the auto-increment by value).
Prior to doing so you want to go to SSMS > Tools > Options > Designers > Table and Database Designers and uncheck the value labeled Prevent saving changes that require table re-creation.
After making the above-mentioned change within your SSMS options locate the table in the object explorer. Right click the table and choose the option labeled Design.
You can now select the column that handles the auto-increment values and then at the bottom of SSMS you should see the ‘Column Properties’ section. Within this panel you can expand Identity Specification and change the value for the Identity Seed to the new value you’re wanting.
Note: By default this will show 1 as the identity seed if you haven’t made any prior changes. This isn’t necessarily the current seed value and you should instead use the section above for finding the current seed values.
Set the values here to your desired values and then close the designer query window and choose the option to Save Changes. Now when you add a new value to the database it should use the seed value you added within the table designer window.