About Autonumbering

If you want to avoid gaps then you have to use custom sequence generation. See one example here:
http://www.sqlmag.com/Article/ArticleID/101339/sql_server_101339.html

--
Plamen Ratchev
http://www.SQLStudio.com
 >> Stay informed about: About Autonumbering 

sam wrote:
>
> i Have an Column Having DataType is Integer.and Column is Auto
> numbering.Can I Check any how in database
> If Nay Number got deleted then Numbering Should not be rendered e.g
>
> If No Assigned Are 1,2,3,4,5
>
> As if No 4 gets deleted then 5 should go to 4 and New No should be
> Assigned as 5

You should ask yourself if you really want to renumber any rows when you
delete one of the rows. Would that renumbering really serve any purpose?
If you want sequential numbering, then it might be better to add this
only in your select queries (as Uri suggested).

And renumbering many rows will have serious impact on performance.
Suppose your table has 1,000,000 rows, and you delete row with ID 2.
Then 999,998 rows have to be renumbered. And if the ID is part of the
clustered index, then not only will all rows be updated, but all indexes
will have to updated as well. That makes deleting a row a very
cumbersome process.

--
Gert-Jan
 >> Stay informed about: About Autonumbering