Why to use identity in sql server, how to write query

Posted by Jitendrasoft09 under Sql Server on 6/26/2013 | Points: 10 | Views : 924 | Status : [Member] [MVP] | Replies : 5
Hi All,
I want to understand Why to use identity in sql server, how to write query, let me know if anybody knows.

Jitendra Kumar
If my post helps you, plz mark as an answer.



Responses

Posted by: Satyapriyanayak on: 6/26/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
Identity (or AutoNumber) is a column that automatically generates numeric values. A start and
increment value can be set, but most DBA leave these at 1. A GUID column also generates numbers,
the value of this cannot be controled. Identity/GUID columns do not need to be indexed.

If this post helps you mark it as answer
Thanks

Jitendrasoft09, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Neeraaj.Sharma on: 6/27/2013 [Member] Starter | Points: 25

Up
0
Down
hey Satyapriyanayak ,

Identity/GUID columns do not need to be indexed.
this is misleading clear that please.





Neeraj Prasad Sharma

www.tutorialsqlserver.com

Jitendrasoft09, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriramnandha on: 9/6/2013 [Member] Starter | Points: 25

Up
0
Down
identity column is used for automatic increment values.....

create table test
(empid int identity(1,1),empname varchar(90))

hope this will help




sriram

Jitendrasoft09, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Allemahesh on: 9/6/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
Dear Jitendra Kumar,

Why to use identity in SQL server?
Very often we would like the value of the primary key field to be created automatically every time a new record is inserted. We would like to create an auto-increment field in a table. For this you will use identity in SQL.

How to write query?
Use the IDENTITY property with CREATE TABLE

This example creates a new table using the IDENTITY property for an automatically incrementing identification number.

USE pubs

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'new_employees')
DROP TABLE new_employees
GO
CREATE TABLE new_employees
(
id_num int IDENTITY (1,1),
fname varchar (20),
minit char(1),
lname varchar(30)
)

INSERT new_employees
(fname, minit, lname)
VALUES
('Karin', 'F', 'Josephs')

INSERT new_employees
(fname, minit, lname)
VALUES
('Pirkko', 'O', 'Koskitalo')




SELECT @@IDENTITY
It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
@@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.

SELECT SCOPE_IDENTITY()
It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.
SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.

SELECT IDENT_CURRENT(‘tablename’)
It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.

Happy Coding.

If it helps you or directs U towards the solution, MARK IT AS ANSWER

Jitendrasoft09, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriramnandha on: 10/10/2013 [Member] Starter | Points: 25

Up
0
Down
Hi,

Identity is used for Auto Increment for the column..

sriram

Jitendrasoft09, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response