If a column is marked as an identity column, then the values for this column are automatically generated, when you insert a new row into the table. The following, create table statement marks PersonId as an identity column with seed = 1 and Identity Increment = 1. Seed and Increment values are optional. If you don't specify the identity and seed they both default to 1.
Create Table tblPerson
(
PersonId int Identity(1,1) Primary Key,
Name nvarchar(20)
)
In the following 2 insert statements, we only supply values for Name column and not for PersonId column.
Insert into tblPerson values ('Sam')
Insert into tblPerson values ('Sara')
If you select all the rows from tblPerson table, you will see that, 'Sam' and 'Sara' rows have got 1 and 2 as PersonId.
Now, if I try to execute the following query, I get an error stating - An explicit value for the identity column in table 'tblPerson' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Insert into tblPerson values (1,'Todd')
So if you mark a column as an Identity column, you dont have to explicitly supply a value for that column when you insert a new row. The value is automatically calculated and provided by SQL server. So, to insert a row into tblPerson table, just provide value for Name column.
Insert into tblPerson values ('Todd')
Delete the row, that you have just inserted and insert another row. You see that the value for PersonId is 2. Now if you insert another row, PersonId is 3. A record with PersonId = 1, does not exist, and I want to fill this gap. To do this, we should be able to explicitly supply the value for identity column. To explicitly supply a value for identity column
1. First turn on identity insert - SET Identity_Insert tblPerson ON
2. In the insert query specify the column list
Insert into tblPerson(PersonId, Name) values(2, 'John')
As long as the Identity_Insert is turned on for a table, you need to explicitly provide the value for that column. If you don't provide the value, you get an error - Explicit value must be specified for identity column in table 'tblPerson1' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.
After, you have the gaps in the identity column filled, and if you wish SQL server to calculate the value, turn off Identity_Insert.
SET Identity_Insert tblPerson OFF
If you have deleted all the rows in a table, and you want to reset the identity column value, use DBCC CHECKIDENT command. This command will reset PersonId identity column.
DBCC CHECKIDENT(tblPerson, RESEED, 0)
Create Table tblPerson
(
PersonId int Identity(1,1) Primary Key,
Name nvarchar(20)
)
In the following 2 insert statements, we only supply values for Name column and not for PersonId column.
Insert into tblPerson values ('Sam')
Insert into tblPerson values ('Sara')
If you select all the rows from tblPerson table, you will see that, 'Sam' and 'Sara' rows have got 1 and 2 as PersonId.
Now, if I try to execute the following query, I get an error stating - An explicit value for the identity column in table 'tblPerson' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Insert into tblPerson values (1,'Todd')
So if you mark a column as an Identity column, you dont have to explicitly supply a value for that column when you insert a new row. The value is automatically calculated and provided by SQL server. So, to insert a row into tblPerson table, just provide value for Name column.
Insert into tblPerson values ('Todd')
Delete the row, that you have just inserted and insert another row. You see that the value for PersonId is 2. Now if you insert another row, PersonId is 3. A record with PersonId = 1, does not exist, and I want to fill this gap. To do this, we should be able to explicitly supply the value for identity column. To explicitly supply a value for identity column
1. First turn on identity insert - SET Identity_Insert tblPerson ON
2. In the insert query specify the column list
Insert into tblPerson(PersonId, Name) values(2, 'John')
As long as the Identity_Insert is turned on for a table, you need to explicitly provide the value for that column. If you don't provide the value, you get an error - Explicit value must be specified for identity column in table 'tblPerson1' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.
After, you have the gaps in the identity column filled, and if you wish SQL server to calculate the value, turn off Identity_Insert.
SET Identity_Insert tblPerson OFF
If you have deleted all the rows in a table, and you want to reset the identity column value, use DBCC CHECKIDENT command. This command will reset PersonId identity column.
DBCC CHECKIDENT(tblPerson, RESEED, 0)
GREAT LECTURE !!!!
ReplyDeleteif i have 3 rows with id 1 2 3 , i delete row with id 2 , i wanna it automaticlly to add id 2 on the next insert , is is possible ?
ReplyDelete(without SET Identity_Insert )
Nope, to my knowledge, you have to use Identity_Insert, and have to specify the value you want to insert.
Deletewhen try to xecute this query shows error
ReplyDeleteDBCC CHECKINDENT("tblItem", RESEED,0)
Incorrect DBCC statement. Check the documentation for the correct DBCC syntax and options
Remove the double quotes for table name and try...
DeleteFirst Check Your Spelling . it is CHECKIDENT not CHECKINDENT and also remove double quotes from tablename
DeleteInstead of double quotes use single quotes
DeleteDBCC CHECKINDENT('tblItem', RESEED,0)
Correct syntax is
DeleteDBCC CHECKIDENT(tblItem, RESEED, 0)
Changes mad are:-
1. Its CHECKIDENT and not CHECKINDENT
2. no single or double codes should be there while giving table name.
Thanks
Hi
ReplyDeletetaking the example you gave
Create Table tblPerson
(
PersonId int Primary Key,
Name nvarchar(20)
)
and then would like to add identity as well
Alter table tblPerson
add id int identity
But fail, help please and sorry but Latin and not much English
This comment has been removed by the author.
DeleteHi Esteban ...
DeleteFirst Created Table
Create Table tblPerson
(
PersonId int Primary Key,
Name nvarchar(20)
)
Now if u want to alter the PersonId column ,, first u need to drop the PersonId bcoz u cannot alter the Primary key column,,,since it is a primary key u cannot drop the PersonId directly..
So first u need to drop the Index that is generated by default for the PersonId Primary key column ..
To drop the Index .. expand the table name tblPerson.. then expand the Indexes folder..
Now Right click on the Index Name and select delete option and click ok..
So now the index that is associated with the Primary key column got deleted..
Now to delete the primary key column .. use the below code...
alter table tblPerson
drop column PersonId
Now u can see only the name column as shown below..
select * from tblPerson
Name
----
(0 row(s) affected)
Now add the column again with ur requirements (making identity column)..
alter table tblPerson
add PersonId int primary key identity
Now u can see two columns..
select * from tblPerson
Name PersonId
-------------------- -----------
(0 row(s) affected)
So now the PersonId is the Primary key identity column...
DeleteHI
I'm trying to drop my index before drop the primary key column.
but delete option hided on the menu.
USE test
ReplyDeleteGO
DBCC CHECKIDENT (tblPerson, RESEED, 5)
GO
i have set identity(1,5) by mistake
ReplyDeletehow do i alter it to identity(5,1) via sql command
create the table..
Deletecreate table tblPers
(
Id int Identity(1,5),
Name varchar(20)
)
Hi since Id here, is an identity column .. u cannot alter the Identity column directly,, so first u need to drop it and then add the Id column again as shown below...
drop the column
alter table tblPers
drop column Id
Add the column again
alter table tblPers
add Id int identity(5,1)
I hope u got the answer..
You can't alter the existing columns for identity.
ReplyDeleteYou have 2 options,
Create a new table with identity & drop the existing table
Create a new column with identity & drop the existing column
Alter Table Names Drop Column ID
Go
Good Job Sir thank you very much.
ReplyDeleteI have a question can i have identity column like A001 and increment by 1.
To my Knowledge I think u can't because column identity is only applicable for integer valued comlumn
DeleteIn an interview interviewer asked me if we do not declare identity column as primary key,then there is any problem
ReplyDeleteNo there will not be any problem, but you need to provide the value of primary key every time and make sure it is unique.
DeleteEven Truncate sets the identity to zero by deleting all records from table
ReplyDeleteDBCC CHECKIDENT('tblPerson', RESEED, 0)----correct one
ReplyDeleteComing in 2021 to see how awesome your content is! Thanks much!
ReplyDeleteThank you very much Mr. Venkat for your simple and clear explanations of these complicated topics. I have watched many videos on YouTube for SQL Server lessons, but believe that you are the best of all. You are very experienced and smart in teaching these topics. The other preference that you have is your great English communication.
ReplyDelete