Question regarding SQL Server's generation of random key values 1

[Azathoth]

I have a database that I often delete all values from and populate with new ones for test data. The scripts I run to populate the database often include tables with foreign key relations that force me to run part of the script, see what kind of keys are generated, and manually add values to tables based on these relations.

For example, if I'm inserting specific values into all of the fields except the primary key (id number) for a table, the id number starts at a random number, say 720, and if there are 30 insert statements, will increment by one each time and end at 749. If I then delete all the values from this table, then run the same script again to re-populate, the random primary key generated will start at 750.

I was wondering if there was a way to control the number (maybe an environmental variable?) that these id numbers begin at...anyone have any ideas?

 

[hmckillop]

Its sounds a bit confusing when you are using the terminology "Random primary key" - I am assuming you are using an Identity field? - if not how is this number generated.

If it is an identity field you can reset it using

DBCC CHECKIDENT (mytable, RESEED, 1)
GO

will reset the identity to 1 on table called MyTable


"I'm living so far beyond my income that we may almost be said to be living apart

 

[NoCoolHandle]

Sounds like you are using an indentity property..

2 ideas

1. Truncate the tables (if possible - foreign keys can stop this) as Trucnate runs fast and always resets the Identity property

2. Use DBCC CHECKIDENT
[block]
Checks the current identity value for the specified table and, if needed, corrects the identity value.

Syntax
DBCC CHECKIDENT
( 'table_name'
[ , { NORESEED
| { RESEED [ , new_reseed_value ] }
}
]
)
[/block]

HTH


Rob

 

[Azathoth]

Exactly what I was looking for, thanks.