• Home
  • MAD
    • IOS Series
    • Android Series
    • Flutter Series
    • Xamarin Series
  • Concept Series
    • Software Design
    • Software Arch
    • GIT & Github
    • System Design
    • Cloud
    • Database Integration
    • Push Notification
    • API Integration
    • Cocoa PODS
  • DSA
  • Interview
  • Tips&Tricks
  • YT
  • Home
  • MAD
    • IOS Series
    • Android Series
    • Flutter Series
    • Xamarin Series
  • Concept Series
    • Software Design
    • Software Arch
    • GIT & Github
    • System Design
    • Cloud
    • Database Integration
    • Push Notification
    • API Integration
    • Cocoa PODS
  • DSA
  • Interview
  • Tips&Tricks
  • YT
  • #News
  • #APPS
  • #Events
    • #WWDC
    • #I/O
    • #Ignite
  • #Let’s Talk

MyCodeTips mycodetips-newlogocopy1

  • Home
  • MAD
    • IOS Series
    • Android Series
    • Flutter Series
    • Xamarin Series
  • Concept Series
    • Software Design
    • Software Arch
    • GIT & Github
    • System Design
    • Cloud
    • Database Integration
    • Push Notification
    • API Integration
    • Cocoa PODS
  • DSA
  • Interview
  • Tips&Tricks
  • YT
Programming

RESEED Identity Column in Database Table – Rest Table Identity Value – SQL in Sixty Seconds

Identity Column in Database

Syntax

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

table_name
Is the name of the table for which to check the current identity value. The table specified must contain an identity column. Table names must follow the rules for identifiers. Two or three part names must be delimited, such as ‘Person.AddressType’ or [Person.AddressType].

NORESEED
Specifies that the current identity value shouldn’t be changed.

RESEED
Specifies that the current identity value should be changed.

new_reseed_value
Is the new value to use as the current value of the identity column.

WITH NO_INFOMSGS
Suppresses all informational messages.

Caller must own the schema that contains the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.

Here is the script :

USE tempdb
GO
-- Create Table
CREATE TABLE TestTable (ID INT IDENTITY(1,1), Col1 VARCHAR(100));
-- Insert Table
INSERT INTO TestTable (Col1)
SELECT 'First'
UNION ALL
SELECT 'Second'
UNION ALL
SELECT 'Third'
UNION ALL
SELECT 'Fourth'
UNION ALL
SELECT 'Fifth';
-- Select data
SELECT *
FROM TestTable
GO
-- Returns current Identity Value
DBCC CHECKIDENT ('TestTable', NORESEED);
-- Resets the current Identity value to maximum value
DBCC CHECKIDENT ('TestTable', RESEED, 11);
-- Insert Table
INSERT INTO TestTable (Col1)
SELECT 'First'
UNION ALL
SELECT 'Second'
UNION ALL
SELECT 'Third'
UNION ALL
SELECT 'Fourth'
UNION ALL
SELECT 'Fifth';
-- Select Table
SELECT *
FROM TestTable
ORDER BY ID;
-- Drop Table
DROP TABLE TestTable;

A. Resetting the current identity value, if it’s needed
The following example resets the current identity value, if it’s needed, of the specified table in the AdventureWorks2012 database.

SQL

USE AdventureWorks2012;
GO
DBCC CHECKIDENT (‘Person.AddressType’);
GO

B. Reporting the current identity value
The following example reports the current identity value in the specified table in the AdventureWorks2012 database, and doesn’t correct the identity value if it’s incorrect.

SQL

USE AdventureWorks2012;
GO
DBCC CHECKIDENT (‘Person.AddressType’, NORESEED);
GO

C. Forcing the current identity value to a new value
The following example forces the current identity value in the AddressTypeID column in the AddressType table to a value of 10. Because the table has existing rows, the next row inserted will use 11 as the value – the new current identity value defined for the column plus 1 (which is the column’s increment value).

SQL

USE AdventureWorks2012;
GO
DBCC CHECKIDENT (‘Person.AddressType’, RESEED, 10);
GO

D. Resetting the identity value on an empty table
The following example forces the current identity value in the ErrorLogID column in the ErrorLog table to a value of 1, after deleting all records from table. Because the table has no existing rows, the next row inserted will use 1 as the value, that is, the new current identity value, without adding the increment value defined for the column.

SQL

USE AdventureWorks2012;
GO
TRUNCATE TABLE dbo.ErrorLog
GO
DBCC CHECKIDENT (‘dbo.ErrorLog’, RESEED, 1);
GO

Liked it? Take a second to support Ranjan on Patreon!
become a patron button
  • Click to share on Reddit (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Tumblr (Opens in new window)
  • More
  • Click to share on Pocket (Opens in new window)
  • Click to share on Pinterest (Opens in new window)
Written by Ranjan - 1392 Views
Tags | SQL
AUTHOR
Ranjan

Namaste, My name is Ranjan, I am a graduate from NIT Rourkela. This website is basically about of what i learnt from my years of experience as a software engineer on software development specifically on mobile application development, design patterns/architectures, its changing scenarios, security, troubleshooting, tools, tips&tricks and many more.

You Might Also Like

1386376017 database

Tips for SQL Query Optimization

December 5, 2013
visual studio purple

Top New Features in Visual Studio 2012

June 4, 2013
mycodetips-newlogo2

How to update specific column in entire database using SQL

June 11, 2013
Next Post
Previous Post

Support us

mycodetips
mycodetips

Follow us @ LinkedIn 2850+

Subscribe for updates

Join 8,213 other subscribers

Latest Posts

  • YT-Featured-solidprinciples
    SOLID Principles of Software Design
  • IOS 16 Features
    Latest features in IOS 16
  • r-language
    How can R language be used for data analysis?
  • wordpress-coding-blog
    Guide To WordPress Coding Standards
  • YT-Featured-Algorithm
    What is Algorithm?
  • Frameworks of IOS
    Frameworks of IOS – Part ( I )
  • NSFileManager or NSPathUtilities
    NSFileManager or NSPathUtilities in Objective-C
  • Passing data between view controllers in Objective-C
    Passing data between view controllers in Objective-C
  • structures-classes-enum
    Structures and Classes in swift !
  • control-system-swift
    Control Flow in Swift
whiteboard

Whiteboard(PRO)

whiteboard

Whiteboard(lite)

alphabets

Kids Alphabet

techlynk

Techlynk

techbyte

Do2Day

techbyte

Techbyte

  • #about
  • #myapps
  • #contact
  • #privacy
  • #Advertise
  • #myQuestions

Android Android Studio API APP Programming Apps blogging CSS DATABASE dsa Features HTML HTML5 installation Interview Questions IOS iPhone javascript Mac objective-c OS Programming quicktips SDK SEO SQL swift Tips & Tricks Tools UI Web Wordpress Xcode

  • SOLID Principles of Software Design
  • Latest features in IOS 16
  • How can R language be used for data analysis?
  • Guide To WordPress Coding Standards
  • What is Algorithm?

©mycodetips.com