Force DELETE to reuse indentifier

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Force DELETE to reuse indentifier

Author

Topic

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2003-03-19 : 17:39:04

Hello all. Am I nuts, or do I remember seeing something in code that reuses the primary key in a delete function?

For instance, I have a table that holds values, that a user typed in to search off of, for a search engine. Once the engine has finished running, it deletes the record in the database table.

But the next time the engine is run, the primary key is now set at 2, next one is 3. Even though there is just that one row in the table.

Isn't there a way I can tell my DELETE query to reuse the primary key value?

Thanks!

Aj

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-19 : 17:41:14

It sounds like you are using the identity column. Why don't you remove the identity column and just use the natural key as the primary key?

Tara

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-03-19 : 21:43:47

I think you mean TRUNCATE TABLE, that will reset the identity seed. But if it's holding search results, I don't see why it would matter unless you're only using the identity to number rows.

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2003-03-20 : 15:21:54

Yeah, true be known, the identity field is only there because it falls into our table standards.

Does TRUNCATE TABLE drop the table? I have multiple users across the midwest who might be using this feature at the same time and can't risk one of them losing their search params.

Thanks all!

Aj

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-20 : 15:26:07

quote:

Yeah, true be known, the identity field is only there because it falls into our table standards.

I would reconsider your table standards then.

quote:

Does TRUNCATE TABLE drop the table?

No, it does not.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-20 : 15:31:32

quote:

the identity field is only there because it falls into our table standards.

I'd love to see those standards...can you post them?

Also if you don't care about the identity column, why are you concerned with reseting them?

Anyway, look up "IDENTITY Property" in BOL. You'll find the following along with other useful info:

From BOL:

B. Use generic syntax for finding gaps in identity values
This example shows generic syntax for finding gaps in identity values when data is removed.

Note The first part of the following Transact-SQL script is designed for illustration purposes only. You can run the Transact-SQL script that starts with the comment: - - Create the img table.

-- Here is the generic syntax for finding identity value gaps in data.
-- This is the beginning of the illustrative example.
SET IDENTITY_INSERT tablename ON

DECLARE @minidentval column_type
DECLARE @nextidentval column_type
SELECT @minidentval = MIN(IDENTITYCOL) FROM tablename
IF @minidentval = IDENT_SEED('tablename')
SELECT @nextidentval = MIN(IDENTITYCOL) + IDENT_INCR('tablename')
FROM tablename t1
WHERE IDENTITYCOL BETWEEN IDENT_SEED('tablename') AND
MAX(column_type) AND
NOT EXISTS (SELECT * FROM tablename t2
WHERE t2.IDENTITYCOL = t1.IDENTITYCOL +
IDENT_INCR('tablename'))
ELSE
SELECT @nextidentval = IDENT_SEED('tablename')
SET IDENTITY_INSERT tablename OFF
-- Here is an example to find gaps in the actual data.
-- The table is called img and has two columns: the first column
-- called id_num, which is an increasing identification number, and the
-- second column called company_name.
-- This is the end of the illustration example.

-- Create the img table.
-- If the img table already exists, drop it.
-- Create the img table.
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'img')
DROP TABLE img
GO
CREATE TABLE img (id_num int IDENTITY(1,1), company_name sysname)
INSERT img(company_name) VALUES ('New Moon Books')
INSERT img(company_name) VALUES ('Lucerne Publishing')
-- SET IDENTITY_INSERT ON and use in img table.
SET IDENTITY_INSERT img ON

DECLARE @minidentval smallint
DECLARE @nextidentval smallint
SELECT @minidentval = MIN(IDENTITYCOL) FROM img
IF @minidentval = IDENT_SEED('img')
SELECT @nextidentval = MIN(IDENTITYCOL) + IDENT_INCR('img')
FROM img t1
WHERE IDENTITYCOL BETWEEN IDENT_SEED('img') AND 32766 AND
NOT EXISTS (SELECT * FROM img t2
WHERE t2.IDENTITYCOL = t1.IDENTITYCOL + IDENT_INCR('img'))
ELSE
SELECT @nextidentval = IDENT_SEED('img')
SET IDENTITY_INSERT img OFF

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources