SQL Day 7: Identity Column in SQL Server

LO 1: Identity Column in SQL Server

Prerequisite: SQL Day 3: Creating Tables & Enforcing Primary and Foreign Key

When you mark the column as Identity Column then you don’t need to supply the value for that column.

If a column is marked as the Identity column, then the values in this column are automatically generated, when you insert a new row into the table.

To check whether the column is Identity column or not you need to open the column folder then right click on any column you want check and click on Properties. In this Properties window Identity display as false which means this column is not an identity column.



Let’s create a table with Identity column.

I have created a table with GUI and add the column name EmpID. In Column Properties window just below the table designer, you can see in Identity Specification section Is Identity mark as No which means currently this column is not an identity column.



Change Is Identity to Yes



You may notice two more Properties also get enabled Identity Increment and Identity Seed.

Identity Increment: Identity Increment defines increment value for every value that gets inserted in the table. For example, you want for every record that gets inserted then the identity column increment by 5, for that you can set Identity Increment to 5. By default, it is set to 1.

Identity Seed: Identity Seed defines from which value to start. For example, you want to start the identity value from 1000 then you need to set the Identity Seed to 1000 so for the very first record EmpID column value will be 1000. By default, it is set to 1.



I have also marked the EmpID column as Primary and created another column called Name and saved this table as tblPerson.



Now when you go to the column properties for the EmpID column then you should see the Identity is set to True.



Let’s insert the values in the tblPerson table.

As you can see, we have not supplied the value for the EmpID column because it gets generated automatically when we insert the new record in this table.


Effects on Identity Column when Delete the Values

Let’s see what will happen when we try to delete the values from Identity Column and insert the new values.



You can see that in above screen EmpID =2 records get deleted and when to insert the new record then Identity column value generated as 3.

So the question arise, Am I able to reuse the deleted Identity values and the answer is Yes.

Let’s again insert a new record with the EmpID =2 that means we are inserting the EmpId value explicitly in the insert query. When we run the query then an error message is displayed.


To handle this error we need first set the IDENTITY_INSERT to On by executing the below query.

SET IDENTITY_INSERT tblPerson ON

After executed above statement we need to insert the new values with the column list defined.

insert into tblPerson (EmpID, Name) values (2, 'Jane')

After execution of above insert query when we select all records from the tblPerson table we should see the EmpID set to 2 for record Jane.



Now let’s try to insert the values as we inserting values earlier by using below query.

insert into tblPerson values ('Tom')



This time SQL server displays an error because we have set the IDENTITY_INSERT to On. To pass the value implicitly we have to again set the IDENTITY_INSERT to Off by executing the below statement. 

SET IDENTITY_INSERT tblPerson OFF

Now again try to execute the insert query.

insert into tblPerson values ('Tom')

You can see that the value gets inserted after the IDENTITY_INSERT set to Off.

Resetting the Identity Column Values

Let’s say we have deleted all the values from the tblPerson and we want EmpID to again start with 1, to do that we need to use DBCC CHECKIDENT command.

Tip: When we delete all the records from tblPerson then don’t assume it will start from the beginning on the new insert. On new insert, the EmpID value will be set to incremented by last id value.



So to reset the identity value after deleting all the values from the table we need to execute the below command.

DBCC CHECKIDENT('tblPerson', RESEED, 0)



Now let’s insert the new record in the tblPerson table. You should see the EmpID is set to 1 for Tim.



That’s all for today. Thank You.

Comments

Post a Comment

Popular posts from this blog

SQL Day 11: Group By in SQL Server

Image
LO1: Grouping rows using Group By LO2: Filtering Groups LO3: Difference between Where and Having Clause Group By clause is used to group a selected set of rows into a set of summary rows by the values of one or more columns or expression. It is always used in conjunction with one or more aggregate functions. Let’s say we have a table tblEmployee which contains all the salary of employees including the cities. So if we need to find the maximum salary of employee we can use the MAX(Column_Name) aggregate function to find the highest salary of employees. select MAX(salary) from tblemployee In the same way, we can find the minimum salary and total salary of all the employees using different aggregate functions. select MIN(salary) as [Minimum Salary] from tblemployee select SUM(salary) as [Total Salary] from tblemployee Let’s say we have to find out the employee salary as per the different cities, to achieve this we need to use the Group B...
Keep reading

JAVA Script Executor Code to Click on an Element

Click on an Element in Selenium WebDriver Sometimes click() method does not work because two views are loaded at the same time on a page. In this case, we can use the JavascriptExecutor to get the work done. Below is code to click on an element using Javascript Executor. 1 2 3 4 5 6 7 @FindBy(id = Checkbox. financialAssistanceAccountEmailId ) public WebElement financialAssistanceAccountEmail; //Click to element JavascriptExecutor executor = (JavascriptExecutor) driver; executor. executeScript ( "arguments[0].click()" , financialAssistanceAccountEmail);
Keep reading

SQL Day 2: Creating, Altering and Dropping a Database

Image
LO1: Creating, Altering and Dropping a Database Prerequisite:   Connecting to SQL Server using SSMS After connecting to the database through SSMS first we need to create the database on which different queries will run. Let’s start with creating a Database. A SQL Server Database can be Created, Altered or Dropped in two ways: Graphically using SQL Server Management Studio or Using SQL Query Graphically using SQL Server Management Studio: Within the Object Explorer, right-click on Database folder and select the New Database. When clicked on New Database a new dialog box will open. Here you need to provide the database name and when clicking on OK, SQL Server run the query internally and will create the new database for us. When you expand the database folder then you will see the Sample database here Creating Database Using Query: We can also create the database by executing the query in the query editor. For creating a...
Keep reading