If you care about what the values will be in your primary key, then you
shouldn't use identity values. When you use an identity value for a key, you
are basically trading extreme ease of programming an artificial key for the
inflexibility of the method.  You will get gaps not only on a delete, but if
there is an error inserting a new row (or rows) into the table.

What makes them (in my opinion :) a good key is simplicity.  I let the
system generate a value, which I do not care what it is (could be
sequential, could be random, could be any unique value (note they are not
called sequences, rather they are called IDENTITY)) but once it has been
generated, it is like the DNA for the row that has been created.  I use it
anytime I am doing a programatic access of a row, like a join, or an update/
delete/ UI access, etc.

On the other hand, the identity value is generally a terrible key for people
to use to access a row.  This is why all rows must have at least one natural
key identified and protected via a key constraint.  If you use identities as
the primary key, then a unique constraint on these values.  The unique
constraints that you create will be your key that users will use.  Clearly
this means a bit more work for you than simply using the natural key as the
primary key, so why do it?

I personally like to have every updateable value have one and only one home.
If the natural key is the primary key, any related tables will contain the
value.  Now I have to cascade operations all around, and this could mean
10 - 20 places or even more for a large model.  With identity keys, I simply
migrate a single part key.

Second, single part keys (which I always have) give me a very standard
looking table that gives me the power to generate code that uses every table
basically the same from a key standpoint (which is a large part of the
work.)  So the more work I mentioned a bit earlier dissapates because I no
longer hand code as much, rather I build code once to do it.

Hi,
   I am thinking of creating an identity column to use it as primary key in
my table. But the problem is that once I delete some records, the SQL Server
would not reuse the IDs of the deleted records. So there will be holes in
the sequence. I know many people use Identity columns to generate unique id.
How do they deal with the holes in the identity columns? Do I need to worry
about the size of the identity column or whether or not the column would run
out of IDs or any other problem?

thanks...
-Nikhil