AutoNumber Column and ...

Hi *.*
I plan to crate a database and want to know if it's good to use autonumber
columns as my primary key or I should generate the numbers myself?
Also, If I want to replicate the DB, is it ok to use autonumber columns?
At last, Is there any way I can manage the autonumber start number? Means
autonumbering starts at 100 or 300000 instead of 0?
Thanks for your help,
HOA
Hoa wrote:

> Hi *.*
> I plan to crate a database and want to know if it's good to use autonumber
> columns as my primary key or I should generate the numbers myself?
> Also, If I want to replicate the DB, is it ok to use autonumber columns?
> At last, Is there any way I can manage the autonumber start number? Means
> autonumbering starts at 100 or 300000 instead of 0?
> Thanks for your help,
> HOA
>
I suggest you take a step back and spend a month researching all this
along with getting your database professionally designed. Every one of
those questions are so "depends" in nature that the very fact that you
asked them tells me you don't have a good enough grasp of the situation
to appreciate the answers.
But, quickly:
1) Depends.
2) Sorta. Depends.
3) Yes.
There is an entire section in the Books Online that is called
"Replication Data Considerations" with a topic "Using IDENTITY Values".
Have you not even bothered to read that section prior to posting your
very open ended question?
Zach
|||Identity columns are fine for PK... as long as you plan on keeping your identity values unique. PK values MUST be unique; Identity does not explicitly enforce uniqueness. Identity columns are good for PKs in the fact that they are INTEGER datatype. This d
atatype is small, so it works well w/ indexes (PK defaults to unique, clustered)
Identity columns may need to be set to 'NOT FOR REPLICATION' in certain situations. See Books Online: Replication topology & NOT FOR REPLICATION for more info.
Yes, you can start an Identity value at a number greater than 0 or 1... you declare it with the IDENTITY(10,5) clause (10 being the 'seed' value, 5 being the increment.
"Hoa" wrote:

> Hi *.*
> I plan to crate a database and want to know if it's good to use autonumber
> columns as my primary key or I should generate the numbers myself?
> Also, If I want to replicate the DB, is it ok to use autonumber columns?
> At last, Is there any way I can manage the autonumber start number? Means
> autonumbering starts at 100 or 300000 instead of 0?
> Thanks for your help,
> HOA
>
>
|||Oh boy. Books Online is your friend, don't let it be a stranger. These
issues are all covered in the documentation.
Your choice of a primary key depends on more factors than "it's good"... see
for a bit of my own opinion.
Using an IDENTITY in a replication scenario is okay, depending on what type
of replication. It will not work in merge replication, for example, since
IDENTITY can't guarantee uniqueness from two different database sources.
As for the identity, yes see the CREATE TABLE and DBCC CHECKIDENT tables in
Books Online. If the table hasn't been created:
CREATE TABLE Splunge
(sid INT IDENTITY(30000, 1))
INSERT Splunge DEFAULT VALUES
INSERT Splunge DEFAULT VALUES
SELECT * FROM Splunge
GO
DROP TABLE Splunge
Once again, please become familiar with Books Online. All of the questions
are answered, and then some, in the documentation you already have available
to you.
http://www.aspfaq.com/
(Reverse address to reply.)
"Hoa" <newpoorguy@.yahoo.com> wrote in message
news:eMTZ#DkeEHA.2804@.TK2MSFTNGP11.phx.gbl...
> Hi *.*
> I plan to crate a database and want to know if it's good to use
autonumber
> columns as my primary key or I should generate the numbers myself?
> Also, If I want to replicate the DB, is it ok to use autonumber columns?
> At last, Is there any way I can manage the autonumber start number? Means
> autonumbering starts at 100 or 300000 instead of 0?
> Thanks for your help,
> HOA
>
|||Using identity columns in replication is supported and as other posters have
indicated this is covered in BOL. The exact use of identities in replication
depends on hte type of replication you have selected, but if for example you
were to do a merge publication, another tab appears on the article
properties which allows you to have SQL Server assign ranges to publisher
and subscriber which ensures there is no overlap in assigned numbers - this
is enforced by check constraints. You can alternatively manage the identity
ranges yourself and Michael Hotek shows how to do this on his site
(http://www.mssqlserver.com/replicati...h_identity.asp).
HTH,
Paul Ibison