Prev: How to specify ranges with batches while updating a column?
Next: Conversion of int to number
From: sam on 17 Feb 2010 07:41
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
From: Uri Dimant on 17 Feb 2010 08:20
Take a look into ROW_NUMBER function if you are using SQL Server 2005 and
onwards




"sam" <sumesh.jangra2009(a)gmail.com> wrote in message
news:fc79c587-0f40-47fc-900e-55c8c50bce49(a)m35g2000prh.googlegroups.com...
>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


From: Plamen Ratchev on 17 Feb 2010 09:54
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
From: Gert-Jan Strik on 19 Feb 2010 14:26
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
 | 
Pages: 1
Prev: How to specify ranges with batches while updating a column?
Next: Conversion of int to number