0

I am using Microsoft SQL server 2012 and i am trying to reset my table primary key IDs so its in a sequential manner, i know some will say its better to leave it as it is but i just want to find out a way to do it.

Question edited due to extra knowledge gained within the first 10mins of post

Captain Obvlious
  • 19,754
  • 5
  • 44
  • 74
Joseph Nguyen
  • 27
  • 5
  • You use SQL-Server but tag your question with MySQL and use MySQL code in your question – juergen d Oct 10 '14 at 11:45
  • Are you using MySQL or Mircosoft SQL server? – Turbo J Oct 10 '14 at 11:45
  • If you are able to understand that primary keys are **not sequential in the manner you think** (numbers incremented by 1) then you will know why you are not supposed to "reset" it. This question pops up every day, and the solution is not to tamper with a primary key, it's to create another column that you will update through a procedure or another process designed to specifically update that one column used to contain "nice" numbers that you are talking about. – N.B. Oct 10 '14 at 11:57
  • I didnt there was any different between MySQL or SQL server, i thought SQL has similar codes....my bad. – Joseph Nguyen Oct 10 '14 at 12:15
  • N.B sometimes i noticed the number incrementing oddly and doesnt make sense while i am creating a new table, i now there is a easy fix so i just want to know its done. whats the harm in trying to learn new things? – Joseph Nguyen Oct 10 '14 at 12:23
  • It doesn't increment oddly. If an insert fails, the database will discard the number. That's desired behavior. It has its reasons, and you are wrongly assuming something is wrong with the primary key. There's no harm in learning something new, which is why I told you that you are using wrong tool for the job - primary key MUST be left alone. Pretty number display is done the way I wrote. I'm not saying this because of nonexistent reason - databases operate in concurrent environment. Tampering with PK ruins that functionality and makes it unstable. – N.B. Oct 10 '14 at 12:33

1 Answers1

0

This is the command to use in SQL Server 2008, and should be the same for 2012:

Reset autoincrement in Microsoft SQL Server 2008 R2

DBCC CHECKIDENT ("YourTableNameHere", RESEED, 1);

And it's not very clear if you want it in MySQL or T-SQL syntax.

Community
  • 1
  • 1
yesman
  • 7,165
  • 15
  • 52
  • 117
  • I believe i was wrong to begin with, i am new to this so maybe thats why i wasnt clear on what i was searching for lol. those codes you provided did fixed the problem......Thanks now i know not all things hat has SQL in is the same – Joseph Nguyen Oct 10 '14 at 12:32
  • Nope, MySQL and SQL Server both have a different syntax. By the way, if you're relying on primary keys to have specific values, you might want to rethink your code. :) Also, please make sure you select this as the correct answer so people know they don't need to post here any more. – yesman Oct 10 '14 at 12:38
  • I can tell its going to be a rough ride for me into the IT world LOL – Joseph Nguyen Oct 10 '14 at 12:48
  • No worries, we all have to start somewhere. I have made mistakes ten times as bad in my code in my first years of school. You'll get better. ;) – yesman Oct 10 '14 at 12:50