When should I use IDENTITY field ?

Hi all.

I'm going to create a big DB that will hold important info
(the usual stuff - Clients, Products, Orders...)

I wonder where should I use the IDENTITY field,
for example - on Orders I will have Order_ID...
(and where does SQL server the numbers of a deleted records)


My fear is that IDENTITY fields will go wrong somehow so I can
loose connections within the tables
(maybe when restoring my DB to some other locations... with DTS... or other issues)


1. When should I use IDENTITY field ?

2. If I do NOT - how can I lock a record when I add a new one



P.S.

How can I alter a table turning ON or OFF an IDENTITY field ?

for example:
if I had my DB with Client_ID as an I IDENTITY field and for some reason it has
changed to just INT (with no IDENTITY) - how can I tell it to be IDENTITY field again ?

There are lots of debates on this subject, some sql designers think identity values are close to heresy, others use identity all the time.

I generally use an identity column as a primary key value in most tables myself.

If you need numbers without any gaps, don't use identity. And converting out & back to identity might mess up your keys.

Thanks,

What I don't seems to understand is how SQL Server knows
which unique numbers already been used
(does it write this info in some table ??)

+

if I'm going to use IDENTITY fields for some tables,
how can I also use the old fashion (safer option) on SQL Server ?
should I "lock" a record before I write ? (how ?)

Do U have a suggestion for me or a link to an article about this ?