How start identity field at 100

  • Thread starter Thread starter Cirene
  • Start date Start date
C

Cirene

I have a table with the PK field defined as an identity field with the
increment of 1. How do I START the calculations at 100, rather than 1?
 
When the table is first created, use an IdentitySeed value of 100.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
 
Set the seed to 100. If you are using the SQL Manager, you can alter there,
but be careful with live databases using this method.

With scripting, the method is:

CREATE TABLE TableWithIdentityColumn
(
MyID int IDENTITY(100,1) PRIMARY KEY
, SomeData varchar(2000) NOT NULL
)

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

*************************************************
| Think outside the box!
|
*************************************************
 
What if the rows already exist? There are 3 rows that i don't want to
recreate, but it won't let me manually change the PK field. Any ideas?
Thanks!
 
The only purpose for Identity is to give a unique number. Even you can
managed to make it starts from 100, is it still OK if next number is 103,
not 101 (if 2 records have been added mistakenly and then deleted)? If it is
OK, then I do not see why you have to waste time to get the SQL Server to
start an ID from 100 Or any other arbitatry number). If it is not OK, then
you should not use Identity column at all.
 
It is ok as long as it starts at 100 and above. I guess I need to tweak the
design. Thanks!
 
Don't worry about what Cirene is saying. It's OK to display an identity
column to your users. Some people choose not to do so, but it's more about
design differences than anything else. I don't agree with pushing this
functionality up to the app level merely to "not waste the DB's time". Odds
are you are going to use an identity column to link tables anyway. You may
as well use it for an ID in the app rather than waste your time building
more code.
 
Thanks!

Andrew Faust said:
Don't worry about what Cirene is saying. It's OK to display an identity
column to your users. Some people choose not to do so, but it's more about
design differences than anything else. I don't agree with pushing this
functionality up to the app level merely to "not waste the DB's time".
Odds are you are going to use an identity column to link tables anyway.
You may as well use it for an ID in the app rather than waste your time
building more code.

--
Andrew Faust
andrew[at]andrewfaust.com
http://www.andrewfaust.com


Cirene said:
It is ok as long as it starts at 100 and above. I guess I need to tweak
the design. Thanks!
Click to expand...
Click to expand...
 
Setting the Identity column properties like Seed and AutoIncrement should
only be done on an empty table--not when tables already exist. It's rarely a
good idea to change the PK anyway.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
 
Bill,
Setting the Identity column properties like Seed and AutoIncrement should
only be done on an empty table--not when tables already exist. It's rarely
a good idea to change the PK anyway.
Click to expand...

I did it yesterday with a corrupted datatable from an SQL server 2000, which
was corrupt because that there was no primary key and was handled using SQL
Management Studio (it was not me who did it, so I am not sure what exactly
was the reason).

I could only do it with the SQL Enterprise tools. However it did help me a
lot.

Therefore never say never.

:-)

Cor
 
Back
Top