Exclusively for you!! Trial version of Google Apps, Apply Coupon-CNHPGX6T6QRWGV!!

IDENTITY as SQL Property and IDENTITY as SQL Function

sql-index
sql-index

Image courtesy of Grant Cochrane / FreeDigitalPhotos.net

IDENTITY as SQL Property and IDENTITY as SQL Function

Welcome to CodeSpread!

IDENTITY as SQL Property:

Almost all the time, We have a table with a primary key and would like to insert a new value automatically in the primary key field.

To achieve this, We have IDENTITY property in SQL which creates an auto-increment field in the table.

Syntax:

IDENTITY [ (seed , increment) ]

example: (1,1)

An identity column has a name, initial seed and step.  Seed is the starting value for IDENTITY and Increment is the incremental value.

In the example, Seed is 1 and Increment is 1.

Note: In case if values are not specified the default is (1,1).

Example:  Create a new table Employee with Employee Id as an IDENTITY column.

CREATE TABLE Employee
(
Employee_ID int IDENTITY(1,1) PRIMARY KEY,
Employee_FirstName varchar(255),
Employee_LastName varchar(255)
)

INSERT INTO Employee
(Employee_FirstName, Employee_LastName)
VALUES
(‘Code’, ‘Spread’)
INSERT INTO Employee<
(Employee_FirstName, Employee_LastName)
VALUES
(‘Code1’, ‘Spread1’)

@@IDENTITY

It returns last identity value entered into a table.

Example: The following example inserts a row into a table with an identity column (Employee_ID)

INSERT INTO Employee
(Employee_FirstName, Employee_LastName)
VALUES
(‘Code2’, ‘Spread2’)
GO
SELECT @@IDENTITY AS 'Identity';
GO

Here, It uses @@IDENTITY to display last identity value entered into a table.

SET IDENTITY_INSERT

What if I want to insert values into an IDENTITY column?

SQL provides  SET IDENTITY_INSERT to insert values into an IDENTITY column. But before that,
set the IDENTITY_INSERT option to “ON”.

Note: The setting of SET IDENTITY_INSERT is set at execute or run time.

Example:


SET IDENTITY_INSERT dbo. Employee ON;
CREATE TABLE Employee
(
Employee_ID int IDENTITY(1,1) PRIMARY KEY,
Employee_FirstName varchar(255),

Employee_LastName varchar(255) )

INSERT INTO Employee

VALUES (3,‘Code1’, ‘Spread1’)

SET IDENTITY_INSERT dbo. Employee OFF;

Reset IDENTITY

The following line resets the Identity value for the Customer table to 0 so that the next record added starts at 1.


DBCC CHECKIDENT('Customer', RESEED, 0)

IDENTITY as SQL Function

The IDENTITY function can only be used when the SELECT statement has an INTO clause.

Example:


SELECTIDENTITY (INT, 100, 5) ASNEW_ID,Employee_FirstName,Employee_LastName)

INTO #tempEmployee

From Employee

  • The first parameter for the IDENTITY function is that data type
  • The second parameter is the seed.
  • The third parameter is the increment.

Note: Only one Identity column is possible for a table.

Author: hershey

A passion for knowledge drives me to do programming, A passion for programming drives me to create something different, A passion for creation drives me to spread the knowledge.

Share This Post On

0 Comments

  1. nice superb explaination

    Post a Reply
  2. thanks for the tutorial
    I am having a problem.
    The feature “Create SQL server database” isn’t available , I can’t select it.
    Can you help, please ?

    Post a Reply

Submit a Comment

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

More from CodeSpread:

  • SQL : Clustered and NonClustered IndexSQL : Clustered and NonClustered IndexWe read about SQL indexes in our previous post, We will dive into the topic and learn the different types of indexing possible in SQL. We have clustered and non-clustered index in SQL, let’s see...
  • Few lines about SQL Use statementFew lines about SQL Use statement We see this statement in Management Studio now and then and it is used quite extensively. For Example, [code] USE MyDatabaseName GO [/code] MyDatabaseName : MyDatabaseName is the n...
  • SQL Bitwise operator can simplify DB designSQL Bitwise operator can simplify DB designFrankly speaking, I never tried SQL Bitwise operator but while doing DB design today for one of the projects, I came to know about this operator from one of my friend and I really liked it. To expl...
  • SQL: Common Table Expression called CTESQL: Common Table Expression called CTEI remember somebody talked about CTE or Common Table Expression with me and tried helplessly to make me understand it. Honestly, I listened very peacefully but was not able to figure out the exact ...
  • SQL:Target a TriggerSQL:Target a TriggerI remember those days when I used to simply fall into the trap set by client requirements which could have been easily resolved by using a trigger. So I can definitely say, trigger is a life saver,...
  • Transact-SQL : Try-Catch BlockTransact-SQL : Try-Catch BlockSimilar to exception-handling features of .Net, T-SQL code provides a TRY-CATCH block. It is one of the implicit features of T-SQL and a famous error handling technique to catch errors. A TRY-CA...
  • Verify existence of SQL tables using Object_ID()Verify existence of SQL tables using Object_ID()Why Object_ID() ? Let’s create a SQL table “Employee” [code] CREATE TABLE Employee ( [EmployeeID] [int] IDENTITY(1,1) PRIMARY KEY, [EmployeeName] [varchar](50) NULL, [Salary] [int] NUL...
  • SQL: Story of IndexesSQL: Story of IndexesI know many people know everything about this topic but as this is one of my favorite topics, I will make sure that I will give you some additional information or knowledge. My first thought abo...