SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 primary key start at record no. 1 again
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

cristuballa
Starting Member

Philippines
13 Posts

Posted - 10/27/2003 :  00:33:15  Show Profile
i have added 2 records to my table having ID as primary key and delete those records (clean the table) so When i add new record again the primary key number should start 1 but it does. how should i do that?

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 10/27/2003 :  00:39:33  Show Profile  Visit Merkin's Homepage
quote:

When i add new record again the primary key number should start 1



No it shouldn't.

If you delete a record and insert a new one, they shouldn't have the same ID because they are different records.
If you want to reset the identity for "neatness" reasons after testing, you can use "Truncate Table" or "DBCC CHECKIDENT". The syntax for each of these is in Books Online.



Damian
Go to Top of Page

Johnyalm
Starting Member

49 Posts

Posted - 10/12/2004 :  07:30:16  Show Profile  Visit Johnyalm's Homepage
quote:
Originally posted by Merkin

quote:

When i add new record again the primary key number should start 1



No it shouldn't.

If you delete a record and insert a new one, they shouldn't have the same ID because they are different records.
If you want to reset the identity for "neatness" reasons after testing, you can use "Truncate Table" or "DBCC CHECKIDENT". The syntax for each of these is in Books Online.




But Damian, what about all relationships? Does DBCC CHECKIDENT also rewrite all fields in all other tables where there are a relationship (FK)??

www.mirrorgate.com
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 10/12/2004 :  09:09:21  Show Profile  Visit nr's Homepage
>> But Damian, what about all relationships? Does DBCC CHECKIDENT also rewrite all fields in all other tables where there are a relationship (FK)??

Not that I like supporting Damian but I think that's what he meant by "No it shouldn't".

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 10/12/2004 :  09:54:37  Show Profile
quote:
Originally posted by nr

Not that I like supporting Damian...



And after all the support he gave after your last visit...oh, wait, that wasn't sql, it was more of vertical support variety...

You know, I should add this as a reason..there is no "cleaning". Conceptually, in the relational model, this will cause you HUGE problems...

http://weblogs.sqlteam.com/brettk/archive/2004/06/09/1530.aspx

Here's some rope...


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 char(1))
GO

INSERT INTO myTable99(Col2)
SELECT 'a' UNION ALL
SELECT 'b' UNION ALL
SELECT 'c'
GO

SELECT * FROM myTable99

TRUNCATE TABLE myTable99
GO

INSERT INTO myTable99(Col2)
SELECT 'd' UNION ALL
SELECT 'e' UNION ALL
SELECT 'f'
GO

SELECT * FROM myTable99
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO


Brett

8-)
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000