0

in mysql, we can modify next sequence value to be inserted for new record by using ALTER TABLE contacts AUTO_INCREMENT = 50 then the insert statement will insert a new record with identity = 50 (or 51 I don't remember)

eg: if the table has 40 records and last Id inserted was 40 then the next identity value should be 41

I want to change the next value for next record should be 51 for example, by using alter statement above then next identity value will be 51

My question is how to implement this action in SQL server?

Ming Hieu
  • 149
  • 3
  • 13
  • 1
    That's available with [DBCC CHECKIDENT](https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkident-transact-sql?view=sql-server-ver15), but why? You *shouldn't* have to perform anything like that except in data migration scenarios. If you want to insert some predefined values, you should create the table with an `IDENTITY SEED` of 51 from the start, and insert the predefined values with [IDENTITY_INSERT ON](https://learn.microsoft.com/en-us/sql/t-sql/statements/set-identity-insert-transact-sql?view=sql-server-ver15) – Panagiotis Kanavos Jun 29 '20 at 10:00
  • In general, you shouldn't try to use SQL Server the same way you used MySQL. MySQL, until 8.0, was *very* limited in analytics, windowing function, etc and allowed behaviours that even its own docs discouraged, like allowing `GROUP BY` queries with non-aggregated fields in `SELECT`. Instead of using "magic" variables to calculate running totals for example, you can use `SUM() OVER(...)` in SQL Server (and MySQL 8 now) – Panagiotis Kanavos Jun 29 '20 at 10:07

0 Answers0