0

I have this table:

CREATE TABLE [dbo].[users](
[userid] [int] IDENTITY(1,1) NOT NULL,
[realname] [nvarchar](250) NOT NULL,
[password] [varchar](250) NOT NULL,
CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED 
(
[userid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

I copied it from another database, i want it to increment each row by 1 , currently it increment each new row by 1 but when reach 100 it turns to 1000 and when reach 1099 it turns to 2000 not to 1100, 1101 so on .... How can i solve it to increment by 1 continuously ?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Hamied
  • 27
  • 5
  • 3
    Are you deleting rows? Side note: the day you stop caring about gaps in identity sequences is a happy day. – Jacob H Feb 08 '18 at 17:13
  • yes, it was containing many rows, i deleted, then reset identity to 1 – Hamied Feb 08 '18 at 17:15
  • Gaps in identity are perfectly normal. Consider what would happen if you delete a row. Do you have to then renumber the entire table? What about all the rows that have foreign keys to this value? Also when you are service restarts gaps will happen do to the way the sql engine caches identity values. If you are placing some importance on the value of the identity I would suggest you need to revisit that because it shouldn't matter. – Sean Lange Feb 08 '18 at 17:15
  • 2
    The others have said what IO have already said. If you care about having an incrementing number, use `ROW_NUMBER()`. – Thom A Feb 08 '18 at 17:16
  • but why this pattern happen: 100 ... 1099, 2000 .... 2099,3000 ? why not turn normally from 1099 to 1100 ? – Hamied Feb 08 '18 at 17:18
  • 1
    This is well known and documented. I mentioned this in my previous comment. Here is one to look at. https://dba.stackexchange.com/questions/106209/unexpected-gaps-in-identity-column – Sean Lange Feb 08 '18 at 17:24

0 Answers0