Identity is a great way of generating a primary key.  Yes, there is a high
potential of having 'holes' in the sequence (though if that is a serious
problem it can be overcome).  For the most part, having all you primary key
values in a clean sequence without and holes is not an issue, as the only
purpose of the PK is to have a unique value.

I for one have never had an issue of running out of IDs.  considering that
you can use a bigint ( -9223372036854775808 to +9223372036854775807), there
is not much chance of running out.

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