SQL QUERY FOR THIS ? Please Help to solve this problem .

[coollife]

I have a table with 2 fields

category_ID Category_name
1 india
2 china
3 japan
4 africa
5 france

I bind these data into grid in aspx page and in that i have given delete command and when i perform delete operation and if i delete china and japan i will be left with

category_ID Category_name
1 india
4 africa
5 france

As i am autogenerating category_ID . In the front end i get max(cateogry_id) + 1 for category_ID that is 6 for the new record

To overcome this problem i want my table to become like this

category_ID Category_name
1 india
2 africa
3 france

Is it possible for the Category_ID column to just assign 1 to n values for the fields present once i perform delete of any record

or
UPDATE command to solve this
or
Trigger or something

Please help

[sknake]

First off this is a horrible idea. To answer your question:

IF OBJECT_ID('Category', 'U') IS NOT NULL DROP TABLE Category
--
GO
--
Create Table Category
(
  RecordId int identity(1000, 1) PRIMARY KEY, --You need a unique identifier
  CategoryId int UNIQUE,
  Name varchar(100)
)
--
GO
--
IF OBJECT_ID('GetNextCategoryId', 'FN') IS NOT NULL DROP FUNCTION GetNextCategoryId
--
GO
--
CREATE FUNCTION GetNextCategoryId ()
RETURNS INT
WITH EXECUTE AS CALLER --lock the table. This will probably cause problems
As
Begin

Declare @NextId int
Set @NextId =
(
  Select Min(CategoryId)
  From Category WITH (TABLOCKX,HOLDLOCK)
  Where 
  NOT EXISTS --doesnt have a number in front of it
  (
    Select *
    From Category (NOLOCK) x
    Where x.CategoryId = (Category.CategoryId+1)
  )
)

Set @NextId = IsNull(@NextId, 0) + 1

RETURN @NextId

END

GO

Insert Into Category (CategoryId, Name) Values (dbo.GetNextCategoryId(), 'Cat 1')
Insert Into Category (CategoryId, Name) Values (dbo.GetNextCategoryId(), 'Cat 2')
Insert Into Category (CategoryId, Name) Values (dbo.GetNextCategoryId(), 'Cat 3')
Insert Into Category (CategoryId, Name) Values (dbo.GetNextCategoryId(), 'Cat 4')
Insert Into Category (CategoryId, Name) Values (dbo.GetNextCategoryId(), 'Cat 5')
GO
Delete From Category Where CategoryId = 2
GO
Insert Into Category (CategoryId, Name) Values (dbo.GetNextCategoryId(), 'Should be cat2')
GO
Select *
From Category