DB identity Column problem [Resolved]

Posted by Gokul under Regular Expressions on 12/1/2010 | Points: 10 | Views : 1433 | Status : [Member] | Replies : 7
Hi Friends
I have developed a page to display the images from DB into grid. I have Image_Id as one column and i have set the identity property to true. When i deleted all the ddatas and upload a image again its image_id is not starting fom 1. It s continuing from where the last image is uploaded. Y?

Thanks and Regards,
GokulNath Nithy.



Responses

Posted by: Hetalkumar1980 on: 12/1/2010 [Member] Starter | Points: 50

Up
0
Down

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

Posted by: Vuyiswamb on: 12/1/2010 [Member] [MVP] [Administrator] NotApplicable | Points: 25

Up
0
Down
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

Posted by: SheoNarayan on: 12/1/2010 [Administrator] HonoraryPlatinum | Points: 25

Up
0
Down
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

Posted by: Vuyiswamb on: 12/1/2010 [Member] [MVP] [Administrator] NotApplicable | Points: 25

Up
0
Down
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

Posted by: Gokul on: 12/1/2010 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: PandianS on: 12/1/2010 [Member] [MVP] Silver | Points: 25

Up
0
Down
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

Posted by: Sivaprasadadiraju on: 12/4/2010 [Member] Starter | Points: 25

Up
0
Down
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

Login to post response