Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 314
  • Last Modified:

reset identity column to 0

Hi Experts
i am trying to reset the value of identity column to start again from 0 or 1
i am using

DBCC CHECKIDENT ('courses.tbl_courses', reseed, 0)

Open in new window


but its not working, still the first column coming 401

What is wrong with the statement? did i miss any thing?
0
AZZA-KHAMEES
Asked:
AZZA-KHAMEES
  • 2
  • 2
  • 2
  • +2
2 Solutions
 
Dave BaldwinFixer of ProblemsCommented:
If you have existing rows, an Identity column (or autoincrement in other servers) will find the highest existing number and add one to that.  No matter what you tell it to start at.  It will never really 'reset' to 0 because it will scan the column and find the current highest number and add 1 to it.
0
 
AZZA-KHAMEESAuthor Commented:
thank you for the reply
i was able to do this by following the steps in this link

How to RESET identity columns in SQL Server

since i need a new data in the table, i had to remove the relations between the tables and delete the data
and it worked

thank you
0
 
coderdnCommented:
In case the column is just an identity column(and not primary key), the command
DBCC CHECKIDENT (....)

Open in new window

should reset the identity to 1. While if the column is primary key as well, then this command does not reset the identity, next identity would be generated according to existing maximum identity.
Seems like your case is the second one.
0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
Dave BaldwinFixer of ProblemsCommented:
You're welcome, glad to help.
0
 
Anthony PerkinsCommented:
Dave,

If you have existing rows, an Identity column (or autoincrement in other servers) will find the highest existing number and add one to that.
You may want to double check that.  You can reset an IDENTITY column to any value using DBCC CHECKIDENT(), regardless of the existing rows.
0
 
Anthony PerkinsCommented:
While if the column is primary key as well, then this command does not reset the identity, next identity would be generated according to existing maximum identity.
I am afraid that is not true.  The act of reseeding has no bearing on existing rows let alone a Primary Key.
0
 
HuaMinChenBusiness AnalystCommented:
One way to reset identity column, is to truncate the table, and it means you have to back up the whole table first.
0
 
AZZA-KHAMEESAuthor Commented:
thank you
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now