DB identity Column problem [Resolved]

For deletion of all the records from the table use the Truncate Table statement as below:

Truncate table <TableName>

Following is the difference between Delete and Truncate tables with practical illustration:

'Create Table

Create Table TableDemo

(

EmpId int IDENTITY,

NAME VARCHAR(50),

GENDER VARCHAR(10)

)

Insert data into table:

INSERT INTO TableDemo(Name,Gender) VALUES ('Aishwarya','Female')

INSERT INTO TableDemo(Name,Gender) VALUES ('Madhuri','Female')

INSERT INTO TableDemo(Name,Gender) VALUES ('Karishma','Female')

INSERT INTO TableDemo(Name,Gender) VALUES ('Karina','Female')

INSERT INTO TableDemo(Name,Gender) VALUES ('Kangna','Female')

INSERT INTO TableDemo(Name,Gender) VALUES ('Amitabh','Male')

INSERT INTO TableDemo(Name,Gender) VALUES ('Shahrukh','Male')

INSERT INTO TableDemo(Name,Gender) VALUES ('Amir','Male')

INSERT INTO TableDemo(Name,Gender) VALUES ('Salman','Male')

INSERT INTO TableDemo(Name,Gender) VALUES ('Abhishek','Male')

''Verify insertion of records:

SELECT * FROM TableDemo



Output:

1	Aishwarya	Female

2	Madhuri	Female

3	Karishma	Female

4	Karina	Female

5	Kangna	Female

6	Amitabh	Male

7	Shahrukh	Male

8	Amir	Male

9	Salman	Male

10	Abhishek	Male

'Delete all the records from table:

Delete from TableDemo

'Again insert the same data:

INSERT INTO TableDemo(Name,Gender) VALUES ('Aishwarya','Female')

INSERT INTO TableDemo(Name,Gender) VALUES ('Madhuri','Female')

INSERT INTO TableDemo(Name,Gender) VALUES ('Karishma','Female')

INSERT INTO TableDemo(Name,Gender) VALUES ('Karina','Female')

INSERT INTO TableDemo(Name,Gender) VALUES ('Kangna','Female')

INSERT INTO TableDemo(Name,Gender) VALUES ('Amitabh','Male')

INSERT INTO TableDemo(Name,Gender) VALUES ('Shahrukh','Male')

INSERT INTO TableDemo(Name,Gender) VALUES ('Amir','Male')

INSERT INTO TableDemo(Name,Gender) VALUES ('Salman','Male')

INSERT INTO TableDemo(Name,Gender) VALUES ('Abhishek','Male')

'Verify insertion of records:

SELECT * FROM TableDemo



Output:

11	Aishwarya	Female

12	Madhuri	Female

13	Karishma	Female

14	Karina	Female

15	Kangna	Female

16	Amitabh	Male

17	Shahrukh	Male

18	Amir	Male

19	Salman	Male

20	Abhishek	Male

'Truncate table:

Truncate table TableDemo

'Again insert the same data:

INSERT INTO TableDemo(Name,Gender) VALUES ('Aishwarya','Female')

INSERT INTO TableDemo(Name,Gender) VALUES ('Madhuri','Female')

INSERT INTO TableDemo(Name,Gender) VALUES ('Karishma','Female')

INSERT INTO TableDemo(Name,Gender) VALUES ('Karina','Female')

INSERT INTO TableDemo(Name,Gender) VALUES ('Kangna','Female')

INSERT INTO TableDemo(Name,Gender) VALUES ('Amitabh','Male')

INSERT INTO TableDemo(Name,Gender) VALUES ('Shahrukh','Male')

INSERT INTO TableDemo(Name,Gender) VALUES ('Amir','Male')

INSERT INTO TableDemo(Name,Gender) VALUES ('Salman','Male')

INSERT INTO TableDemo(Name,Gender) VALUES ('Abhishek','Male')

'Verify insertion of records:

SELECT * FROM TableDemo

Output:

1	Aishwarya	Female

2	Madhuri	Female

3	Karishma	Female

4	Karina	Female

5	Kangna	Female

6	Amitabh	Male

7	Shahrukh	Male

8	Amir	Male

9	Salman	Male

10	Abhishek	Male

Hetalkumar M Kachhadiya

http://dotnetsqlinterview.blogspot.com

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

That is how the Identity works when it used with the Delete, but if you want to start from 1 you need to use the Truncate statement , the truncate statement will remove all the records in the table, i am not sure if it was you want.

Thank you for posting at Dotnetfunda

vuyiswa Maseko

Thank you for posting at Dotnetfunda
[Administrator]

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

Excellent explanations Hetalkumar1980, you did it before I could. Thanks and keep it up!

Regards,
Sheo Narayan
http://www.dotnetfunda.com

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

he is very Fast , i thought i got it :)

Thank you for posting at Dotnetfunda
[Administrator]

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

Thank u.. Now my issue is cleared. Keep doing the same gud work. Cheers... Gokul

Thanks and Regards,
GokulNath Nithy.

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

Hi

When using DELETE:
1. Only the data will be deleted. But the prepared statistics will be there in Header of the Data Page.
2. When you insert new record, The Identity refer the last generated sequence from Header of the data page.
3. So, The Identity will be generated (Existing Value + 1).

When using TRUNCATE:
1. Truncate will De-allocate the actual data page instead of data.
2. So, When you insert new record, you don't have any previously prepared statistics in Header of the Data page (ie: Data page deallocated and reclaimed the memory used for the Data Page)
3. So, you will get New sequence for the actual table schema (0 + Seed Value).

If you plan to remove all the records from the table, You can use Truncate instead of Delete. "So you can avoid the Unnecessary log grow".

But, If this is referred by any table(s)(FK) then, You can not use Truncate as the Parent table's Header in data will retain the References parameters.

If so, You have to use only Delete and You can Re-seed the Identity usinf(DBCC CHECKIDENT)

Cheers

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

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

Hi Hetalkumar .

it is really nice . your explanation really good..

Thanks

SIVA PRASAD ADIRAJU

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