Problem with ID update after deleting rows

Hi,

I hope some of you can help me with that

I made an application that insert some data to MS SQL 2005 DB Express Edition. One of columns in my database store text. The content of that field must, beyond the existing text, append the current id to its text string. Practically, it means that if on row nr 15 I store text value "15text", on the next row i will store "16text". I figured out that I can get value of max ID by creating following stored procedure.

USE [tracking_db]

GO

/****** Object: StoredProcedure [dbo].[spMaxId] Script Date: 07/18/2007 09:31:44 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[spMaxId]

@.maxId integer output

as

SELECT @.maxId= MAX(id) FROM FILES_TABLE

Once i get the maxID value i simply concat max id and string like:

string.Concat(max_id.ToString(), file_name);

where "max_id is integer return value from stored procedure and "file_name" is a string to rename like "max_id+file_name"

Two problems occur!!!

Problem nr 1.

Since I use to insert 10 new rows each time, the values from 0-9 are appended to text like "(0-9)text"

Problem nr 2.

If I delete some rows, ID does not get update. It means that after deleting all rows from table, next inserted item gets last existed ID before delting +1. New inserted item should get value 1 since table is empty after deleting all rows from it!!!

Hope some of you has any idea what to do

Muris

i think this table ID coulumn is Identity column. In that case identity value has to be reset using DBCC Checkident command to reset the value. Otherwise remove the Identity column and change the insert logic.

select COLUMNPROPERTY (object_id('yourtablename'),'yourcolumnname' , 'IsIdentity')

if the above statement returns 1 it means its a identity column. So you should use DBCC Checkident command to reset the identity.

Madhu

|||

Hi Madhu,

You are right. My ID column is an identity column and it is also autoincremental. I wil now try to solve problem using your idea.

Muris

|||

Hi Madhu,

I find your answer logical and helpful, but I have difficulties to implement it.

Where should I put this DBCC Checkident to reset the identity. I use a stored procedure to find the max value of my identity column.

Simply, where to add the statement your suggested?

Muris

|||

Execute Mahdu's suggestion one time only -in a empty query window.

DBCC CHECKIDENT ( 'YourTable', RESEED )

|||

Suppose u have 1000 rows in the table and you are deleting rows where ID>900 . ie 900 to 1000 - 100 rows. After deletion when you insert what i understand is you should have the ID as 900 but you will get it as 1001. If this is the case. After deletion you need to reset the indentity. in this particular scenario

Delete from yourtablename where id>900 -- It will delete 100 rows

DBCC CHECKIDENT ( 'YourTable', 900) -- REset the value to 900 , if you have not run this

statement your next id will be 1001. You can

do this in sp also. read about this BOL

Insert the new value

Madhu

|||

HI,

I was away from my computer for several days so my reply is a bit late. Anyway I solved the problem with ID update using your suggestions with small modification.

Typing DBCC CHECKIDENT('YourTable', new value of ID) does not help unless you do not specify command RESEED so following statement is fully functional:

DBCC CHECKIDENT('YourTable', RESEED,new value of ID) .

In terms of deleting all rows from your table you should write

DBCC CHECKIDENT('YourTable',RESEED,1) .

The number 1 will be the ID of the newly inserted row in your previously cleaned table.

Thank you all for your help!

Regards

Muris