Skip to content
Fix Code Error

Reset identity seed after deleting records in SQL Server

March 13, 2021 by Code Error
Posted By: Anonymous

I have inserted records into a SQL Server database table. The table had a primary key defined and the auto increment identity seed is set to “Yes”. This is done primarily because in SQL Azure, each table has to have a primary key and identity defined.

But since I have to delete some records from the table, the identity seed for those tables will be disturbed and the index column (which is auto-generated with an increment of 1) will get disturbed.

How can I reset the identity column after I deleted the records so that the column has sequence in ascending numerical order?

The identity column is not used as a foreign key anywhere in database.

Solution

The DBCC CHECKIDENT management command is used to reset identity counter. The command syntax is:

DBCC CHECKIDENT (table_name [, { NORESEED | { RESEED [, new_reseed_value ]}}])
[ WITH NO_INFOMSGS ]

Example:

DBCC CHECKIDENT ('[TestTable]', RESEED, 0);
GO

It was not supported in previous versions of the Azure SQL Database but is supported now.


Thanks to Solomon Rutzky the docs for the command are now fixed.

Answered By: Anonymous

Related Articles

  • Can a "User Assigned Managed Identity" be used locally?
  • SQL query return data from multiple tables
  • Generating random strings with T-SQL
  • Database development mistakes made by application developers
  • SQL Server : trigger how to read value for Insert,…
  • How to use azure-sb in aurelia
  • error LNK2005: ✘✘✘ already defined in…
  • Ukkonen's suffix tree algorithm in plain English
  • Error: Each row of output must be identified by a…
  • How do I include certain conditions in SQL Count
  • Improve INSERT-per-second performance of SQLite
  • unique combinations of values in selected columns in…
  • Timer trigger Azure Function stopped working without…
  • How to Update Database from Assets Folder in App
  • Use SQL Server Management Studio to connect remotely…
  • How should a model be structured in MVC?
  • "Error in UseMethod("mutate") : no applicable method…
  • Use Azure Key Vault to get secrets
  • T-SQL How to create tables dynamically in stored procedures?
  • creating triggers for After Insert, After Update and…
  • Log record changes in SQL server in an audit table
  • In plain English, what does "git reset" do?
  • Query on usage of on-premises data gateway for…
  • What are the best practices for using a GUID as a…
  • ternary operator usage within v-bind class
  • Incorrect SAS URL produced for blob when using…
  • How do i set VM generation while creating disk using…
  • Update the Azure DevOps service endpoint…
  • Pytorch data.random_split() doesn't split randomly
  • Is there a way to pickup backlog item data from the…
  • Creating Azure VM by using VM admin password from…
  • SQL Server Trigger on Specified Columns
  • How to generate a random number in C++?
  • Unable to retrieve cosmosDB data using azure…
  • Azure: Web Apps - List Application Settings from…
  • How does PHP 'foreach' actually work?
  • For-each over an array in JavaScript
  • When should I use a table variable vs temporary…
  • Azure Function long duration and App Service Plan,…
  • How do you sign a Certificate Signing Request with…
  • python 3.2 UnicodeEncodeError: 'charmap' codec can't…
  • Count with CSS Animation: Animate only once instead…
  • creating a python 2 player game with functions & classes
  • How to launch a new VM with azure VM image using…
  • How to use Selenium in Databricks and accessing and…
  • Refresh Azure Storage Account Key Periodically using C# SDK
  • data.table vs dplyr: can one do something well the…
  • How to use a certificate for MS Graph authentication…
  • How do SO_REUSEADDR and SO_REUSEPORT differ?
  • How to create a temporary table in SSIS control flow…
  • The definitive guide to form-based website authentication
  • Start redis-server with config file
  • What's the difference between a temp table and table…
  • Why cat does not work with parameter -0 in xargs?
  • Azure web app to serve vuejs app
  • Azure AD: Send welcome email when user is assigned…
  • Creating an index on a table variable
  • Give Azure Table Storage access to Azure AD B2C users
  • What does on_delete do on Django models?
  • Azure global admin cannot(disabled) add roles under…
  • SQL Server "AFTER INSERT" trigger doesn't see the…
  • How do I count unique visitors to my site?
  • What's the best practice for primary keys in tables?
  • Delay content of test of Cloud Functions with Mocha
  • Generating a random & unique 8 character string…
  • MySQL foreign key constraints, cascade delete
  • Receiving the error Code: SubscriptionNotFound…
  • Why does C++ code for testing the Collatz conjecture…
  • OpenIddict Roles/Policy returns 403 Forbidden
  • Foreign key constraints: When to use ON UPDATE and ON DELETE
  • unable to deploy next js to azure
  • Moving SQL Data into Azure SQL PaaS
  • how to show progress bar(circle) in an activity…
  • Best practice multi language website
  • Deploying an Web API2.0 with angular application on azure
  • What's the difference between TRUNCATE and DELETE in SQL
  • Smart way to truncate long strings
  • java.sql.SQLException: - ORA-01000: maximum open…
  • I want to create a SQLite database like in the…
  • Reference — What does this symbol mean in PHP?
  • Forward X11 failed: Network error: Connection refused
  • how to turn a recursive algorithms into an iterative one
  • Difference between variable declaration syntaxes in…
  • (Vue.js and Laravel) Array won't refresh after deleting item
  • Is this request generated by EF Core buggy or is it my code?
  • Cosmos SQL db create item
  • How to separate a string column into multiple columns?
  • Entity Framework .Remove() vs. .DeleteObject()
  • How to automatically version npm package in Azure…
  • Relational room database: The class must be either…
  • Why not use tables for layout in HTML?
  • Backgrid filter not working in backbone app
  • How to update Identity Column in SQL Server?
  • Octave using 'for' statement to show two animations…
  • Logging best practices
  • Entity Framework code-first: migration fails with…
  • laravel Eloquent ORM delete() method
  • Reset/remove CSS styles for element only
  • MySQL Cannot Add Foreign Key Constraint
  • Patch compliance scanning on VM Scalesets

Disclaimer: This content is shared under creative common license cc-by-sa 3.0. It is generated from StackExchange Website Network.

Post navigation

Previous Post:

Extracting extension from filename in Python

Next Post:

Getting the class name of an instance?

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

.net ajax android angular arrays aurelia backbone.js bash c++ css dataframe ember-data ember.js excel git html ios java javascript jquery json laravel linux list mysql next.js node.js pandas php polymer polymer-1.0 python python-3.x r reactjs regex sql sql-server string svelte typescript vue-component vue.js vuejs2 vuetify.js

  • you shouldn’t need to use z-index
  • No column in target database, but getting “The schema update is terminating because data loss might occur”
  • Angular – expected call-signature: ‘changePassword’ to have a typedeftslint(typedef)
  • trying to implement NativeAdFactory imports deprecated method by default in flutter java project
  • What should I use to get an attribute out of my foreign table in Laravel?
© 2022 Fix Code Error