Re: Generate Random Primary Key

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 06/04/04 

Date: Fri, 4 Jun 2004 09:50:16 -0500

Just to add to the mayhem, you could change the seed value to start at the
minimum:

set nocount on
drop table testIdent

create table testIdent(testIdentId int identity (1,1), value varchar(10))

insert into testIdent(value) values ('test')
insert into testIdent(value) values ('test')
insert into testIdent(value) values ('test')
insert into testIdent(value) values ('test')
insert into testIdent(value) values ('test')

dbcc checkident ('testIdent',RESEED, -2147483648)

insert into testIdent(value) values ('test')
insert into testIdent(value) values ('test')
insert into testIdent(value) values ('test')
insert into testIdent(value) values ('test')
insert into testIdent(value) values ('test')

select * from testIdent

This returns:

Checking identity information: current identity value '5', current column
value '-2147483648'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
testIdentId value
----------- ----------
1 test
2 test
3 test
4 test
5 test
-2147483647 test
-2147483646 test
-2147483645 test
-2147483644 test
-2147483643 test

This would give you a few more billion inserts before having to reengineer 

-- 
----------------------------------------------------------------------------
Louis Davidson (drsql@hotmail.com)
Compass Technology Management
Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are
interested in consulting services.  All other replies will be ignored :)
"R. Paardekam" <robin.paardekam@tutch.nl> wrote in message
news:c9pq27$o5e$1@reader08.wxs.nl...
> Hello to everybody,
>
> I hope one of you guys / girls can help me out because I am pretty sad
> right now: couldn't find an answer on the net...
>
> We got an MS SQL Server 2000 on a Windows 2003 Server and one of the
> tables in my master-database is "out of integers". Well... we imported a
> bunch of records from MS Access some months ago. In MS Access the
> records where randomly generated. Now the SQL Server reached it's
> maximum integer value (2147483647) resulting in an error when I try to
> add a new one:
>
> Microsoft OLE DB Provider for SQL Server error '80004005'
> Arithmetic overflow error converting IDENTITY to data type int.
> /import_robin_20040604.asp, line 175
>
> Now I tried some things in SQL Server, but I must admit: I'm not really
> a specialst in those things.
>
> So my question is: can anyone tell me how to generate random integers?
> (unique ofcourse!)
>
> I hope to hear from anyone soon.
>
> Thanks in advance,
>
> Robin Paardekam
> Tutch Mobile Media
> (Robin.Paardekam[AT]Tutch.nl)

Relevant Pages

  • Re: WHERE clause applies to right-hand table of LEFT JOIN
    ... it's always best to specify INNER JOIN ... T1_PK int NOT NULL ... CONSTRAINT FK_T2_T1 FOREIGN KEY ... The SQL Server cost-based optimizer is very good at generating efficient ...
    (microsoft.public.sqlserver.server)
  • Re: WHERE clause applies to right-hand table of LEFT JOIN
    ... it's always best to specify INNER JOIN ... T1_PK int NOT NULL ... CONSTRAINT FK_T2_T1 FOREIGN KEY ... The SQL Server cost-based optimizer is very good at generating efficient ...
    (microsoft.public.access.queries)
  • Re: Consequences of Error 644?
    ... If you get the errr while running DBCC - The error is telling you that a row ... but the index entry for some index is missing... ... It says that the problem is a SQL Server ... will happen repeatedly when the customer does the same work which caused the ...
    (microsoft.public.sqlserver.server)
  • Re: 17805 Starting up Java App server, with SQL Server 2000 Backend
    ... network issue although you would expect a network issue to be more random. ... declare @P1 int ... On another machine running SQL Server 2000 Standard on Win2K advanced, ...
    (microsoft.public.sqlserver.clients)
  • Re: Users , Web Application and SQL Server back end - Beginner Questio
    ... Create table Users (userid int not null primary key, ... Create table UserTypes (usertypeid int not null primary key, ... I have developed a java based Web application that is using SQL Server ...
    (microsoft.public.sqlserver.security)