Identity & sequential No columns

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)

Identity & sequential No columns

Author

Topic

Scott
Posting Yak Master

145 Posts

Posted - 2002-11-25 : 02:30:20

I have a table for a competition as follows:

CREATE TABLE [Competition] (
	[CompID] [int] IDENTITY (1, 1) NOT NULL ,
	[FirstName] [varchar] (50) NOT NULL ,
	[LastName] [varchar] (50) NOT NULL ,
	[Cellno] [char] (10) NOT NULL ,
	[Email] [varchar] (50) NOT NULL ,
	[Answer1] [bit] NOT NULL ,
	[Answer2] [bit] NOT NULL ,
	[Comment] [varchar] (255) NULL 
	)
GO

I am using the following statement to draw the winner:


Declare @MaxValue int, @RandomNumber1 float  
Select @MaxValue = max(CompID) from Competition 
Select @RandomNumber1 = rand() * @MaxValue
Select TOP 1 * From Competition Where CompID >= @RandomNumber1

1 or 2 people entered who should not have so I delete them. This now creates a gap in CompID. How do I renumber this field? Is there something like DBCC CHECKIDENT?

rihardh
Constraint Violating Yak Guru

307 Posts

Posted - 2002-11-25 : 02:37:44

You have to drop the identity property and do it by hand (if you want to close the gap). For anything else look up BOL under IDENTITY and DBCC CHECKIDENT.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2002-11-25 : 05:26:33

Do you really need to re-number the field? or is it just enough to ensure that the proc returns a valid id? In that case, this might do:

Declare @MaxValue int, @RandomNumber1 float
Declare @WinnerID int
Select @MaxValue = max(CompID) from Competition

WHILE(@WinnerID IS NULL)
BEGIN
Select @RandomNumber1 = rand() * @MaxValue
Select @WinnerID = CompID From Competition Where CompID = @RandomNumber1
END

SELECT * FROM Competition WHERE CompID = @WinnerID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources