Reset or Set the value of the auto increment index in SQL Server

byAlex R. •February 08, 2011

If an auto incremented index is defined in a table, then when we delete all the data from this table, the new values of the “id” column are incremented from the last inserted value event if it is no longer in the table.

What if we copy a database and there is a requirement to keep the “id” column identical?

Then we use this command:

DBCC CHECKIDENT (tableName, RESEED, newValue)

Tags SQL Server

<script type="text/javascript" src="https://www.blogger.com/static/v1/widgets/984859869-widgets.js"></script>
<script type='text/javascript'>
window['__wavt'] = 'AOuZoY75ZYun3m4KavoN8YGjGjAzEdeo9A:1731968675872';_WidgetManager._Init('//www.blogger.com/rearrange?blogID\x3d2139279071038304164','//alexrait.blogspot.com/2011/02/reset-or-set-value-of-auto-increment.html','2139279071038304164');
_WidgetManager._SetDataContext([{'name': 'blog', 'data': {'blogId': '2139279071038304164', 'title': 'Coding with Alex', 'url': 'https://alexrait.blogspot.com/2011/02/reset-or-set-value-of-auto-increment.html', 'canonicalUrl': 'https://alexrait.blogspot.com/2011/02/reset-or-set-value-of-auto-increment.html', 'homepageUrl': 'https://alexrait.blogspot.com/', 'searchUrl': 'https://alexrait.blogspot.com/search', 'canonicalHomepageUrl': 'https://alexrait.blogspot.com/', 'blogspotFaviconUrl': 'https://alexrait.blogspot.com/favicon.ico', 'bloggerUrl': 'https://www.blogger.com', 'hasCustomDomain': false, 'httpsEnabled': true, 'enabledCommentProfileImages': true, 'gPlusViewType': 'FILTERED_POSTMOD', 'adultContent': false, 'analyticsAccountNumber': 'UA-202988341-1', 'encoding': 'UTF-8', 'locale': 'en', 'localeUnderscoreDelimited': 'en', 'languageDirection': 'ltr', 'isPrivate': false, 'isMobile': false, 'isMobileRequest': false, 'mobileClass': '', 'isPrivateBlog': false, 'isDynamicViewsAvailable': true, 'feedLinks': '\x3clink rel\x3d\x22alternate\x22 type\x3d\x22application/atom+xml\x22 title\x3d\x22Coding with Alex - Atom\x22 href\x3d\x22https://alexrait.blogspot.com/feeds/posts/default\x22 /\x3e\n\x3clink rel\x3d\x22alternate\x22 type\x3d\x22application/rss+xml\x22 title\x3d\x22Coding with Alex - RSS\x22 href\x3d\x22https://alexrait.blogspot.com/feeds/posts/default?alt\x3drss\x22 /\x3e\n\x3clink rel\x3d\x22service.post\x22 type\x3d\x22application/atom+xml\x22 title\x3d\x22Coding with Alex - Atom\x22 href\x3d\x22https://www.blogger.com/feeds/2139279071038304164/posts/default\x22 /\x3e\n\n\x3clink rel\x3d\x22alternate\x22 type\x3d\x22application/atom+xml\x22 title\x3d\x22Coding with Alex - Atom\x22 href\x3d\x22https://alexrait.blogspot.com/feeds/8032149004103494066/comments/default\x22 /\x3e\n', 'meTag': '', 'adsenseClientId': 'ca-pub-9348007134072555', 'adsenseHostId': 'ca-host-pub-1556223355139109', 'adsenseHasAds': false, 'adsenseAutoAds': false, 'boqCommentIframeForm': true, 'loginRedirectParam': '', 'view': '', 'dynamicViewsCommentsSrc': '//www.blogblog.com/dynamicviews/4224c15c4e7c9321/js/comments.js', 'dynamicViewsScriptSrc': '//www.blogblog.com/dynamicviews/51f2c5635a23dea1', 'plusOneApiSrc': 'https://apis.google.com/js/platform.js', 'disableGComments': true, 'interstitialAccepted': false, 'sharing': {'platforms': [{'name': 'Get link', 'key': 'link', 'shareMessage': 'Get link', 'target': ''}, {'name': 'Facebook', 'key': 'facebook', 'shareMessage': 'Share to Facebook', 'target': 'facebook'}, {'name': 'BlogThis!', 'key': 'blogThis', 'shareMessage': 'BlogThis!', 'target': 'blog'}, {'name': 'X', 'key': 'twitter', 'shareMessage': 'Share to X', 'target': 'twitter'}, {'name': 'Pinterest', 'key': 'pinterest', 'shareMessage': 'Share to Pinterest', 'target': 'pinterest'}, {'name': 'Email', 'key': 'email', 'shareMessage': 'Email', 'target': 'email'}], 'disableGooglePlus': true, 'googlePlusShareButtonWidth': 0, 'googlePlusBootstrap': '\x3cscript type\x3d\x22text/javascript\x22\x3ewindow.___gcfg \x3d {\x27lang\x27: \x27en\x27};\x3c/script\x3e'}, 'hasCustomJumpLinkMessage': true, 'jumpLinkMessage': 'Blog Posts', 'pageType': 'item', 'postId': '8032149004103494066', 'pageName': 'Reset or Set the value of the auto increment index in SQL Server', 'pageTitle': 'Coding with Alex: Reset or Set the value of the auto increment index in SQL Server', 'metaDescription': 'Reset the autoincrement value for an Identity column'}}, {'name': 'features', 'data': {}}, {'name': 'messages', 'data': {'edit': 'Edit', 'linkCopiedToClipboard': 'Link copied to clipboard!', 'ok': 'Ok', 'postLink': 'Post Link'}}, {'name': 'template', 'data': {'name': 'custom', 'localizedName': 'Custom', 'isResponsive': true, 'isAlternateRendering': false, 'isCustom': true}}, {'name': 'view', 'data': {'classic': {'name': 'classic', 'url': '?view\x3dclassic'}, 'flipcard': {'name': 'flipcard', 'url': '?view\x3dflipcard'}, 'magazine': {'name': 'magazine', 'url': '?view\x3dmagazine'}, 'mosaic': {'name': 'mosaic', 'url': '?view\x3dmosaic'}, 'sidebar': {'name': 'sidebar', 'url': '?view\x3dsidebar'}, 'snapshot': {'name': 'snapshot', 'url': '?view\x3dsnapshot'}, 'timeslide': {'name': 'timeslide', 'url': '?view\x3dtimeslide'}, 'isMobile': false, 'title': 'Reset or Set the value of the auto increment index in SQL Server', 'description': 'Reset the autoincrement value for an Identity column', 'url': 'https://alexrait.blogspot.com/2011/02/reset-or-set-value-of-auto-increment.html', 'type': 'item', 'isSingleItem': true, 'isMultipleItems': false, 'isError': false, 'isPage': false, 'isPost': true, 'isHomepage': false, 'isArchive': false, 'isLabelSearch': false, 'postId': 8032149004103494066}}, {'name': 'widgets', 'data': [{'title': 'Default Thumbnail', 'type': 'Image', 'sectionId': 'pbt-panel', 'id': 'Image52'}, {'title': 'JSON Variables', 'type': 'HTML', 'sectionId': 'pbt-panel', 'id': 'HTML50'}, {'title': 'Coding with Alex', 'type': 'Image', 'sectionId': 'main-logo', 'id': 'Image50'}, {'title': '', 'type': 'LinkList', 'sectionId': 'xdfcb-main-menu', 'id': 'LinkList200'}, {'title': 'Links', 'type': 'LinkList', 'sectionId': 'headerbar', 'id': 'LinkList201'}, {'title': 'Follow Us', 'type': 'LinkList', 'sectionId': 'headerbar', 'id': 'LinkList202'}, {'title': 'Blog Posts', 'type': 'Blog', 'sectionId': 'main', 'id': 'Blog1', 'posts': [{'id': '8032149004103494066', 'title': 'Reset or Set the value of the auto increment index in SQL Server', 'showInlineAds': false}], 'headerByline': {'regionName': 'header1', 'items': [{'name': 'share', 'label': ''}, {'name': 'author', 'label': 'by'}, {'name': 'timestamp', 'label': '\u2022'}]}, 'footerBylines': [{'regionName': 'footer1', 'items': [{'name': 'comments', 'label': '$type\x3d{blogger}'}, {'name': 'icons', 'label': ''}]}, {'regionName': 'footer2', 'items': [{'name': 'labels', 'label': 'Tags'}]}], 'allBylineItems': [{'name': 'share', 'label': ''}, {'name': 'author', 'label': 'by'}, {'name': 'timestamp', 'label': '\u2022'}, {'name': 'comments', 'label': '$type\x3d{blogger}'}, {'name': 'icons', 'label': ''}, {'name': 'labels', 'label': 'Tags'}]}, {'title': 'You might like', 'type': 'HTML', 'sectionId': 'xdfcb-related-posts', 'id': 'HTML51'}, {'title': 'Coding with Alex (Header)', 'type': 'Header', 'sectionId': 'sidebar', 'id': 'Header1'}, {'title': 'Main Tags', 'type': 'Label', 'sectionId': 'sidebar', 'id': 'Label2'}, {'title': 'Total Pageviews', 'type': 'Stats', 'sectionId': 'sidebar', 'id': 'Stats1'}, {'title': 'Popular Posts', 'type': 'PopularPosts', 'sectionId': 'sidebar', 'id': 'PopularPosts2', 'posts': [{'title': 'Configure Log4Net in a .NET core application', 'id': 5202891952012580662}, {'title': 'A skeleton for a .NET console application with dependency injection', 'id': 3036110136314605278}, {'title': 'List of websites that offer free icons in ICO, PNG or SVG formats.', 'id': 3121387169778765423}, {'title': 'SHA256 support in windows XP', 'id': 3238970205216479489}]}, {'title': 'Most Popular Posts', 'type': 'PopularPosts', 'sectionId': 'sidebar', 'id': 'PopularPosts1', 'posts': [{'title': 'Configure Log4Net in a .NET core application', 'id': '5202891952012580662'}, {'title': 'A skeleton for a .NET console application with dependency injection', 'id': '3036110136314605278'}, {'title': 'List of websites that offer free icons in ICO, PNG or SVG formats.', 'id': '3121387169778765423'}, {'title': 'SHA256 support in windows XP', 'id': '3238970205216479489'}, {'title': 'The mystery behind file extensions', 'id': '3638078121481613638'}, {'title': 'MahApp MetroWindow project setup', 'id': '5152215348388033849'}]}, {'title': 'About Us', 'type': 'Image', 'sectionId': 'xdfcb-about-section', 'id': 'Image51'}]}]);
_WidgetManager._RegisterWidget('_ImageView', new _WidgetInfo('Image52', 'pbt-panel', document.getElementById('Image52'), {'resize': true}, 'displayModeFull'));
_WidgetManager._RegisterWidget('_HTMLView', new _WidgetInfo('HTML50', 'pbt-panel', document.getElementById('HTML50'), {}, 'displayModeFull'));
_WidgetManager._RegisterWidget('_ImageView', new _WidgetInfo('Image50', 'main-logo', document.getElementById('Image50'), {'resize': true}, 'displayModeFull'));
_WidgetManager._RegisterWidget('_LinkListView', new _WidgetInfo('LinkList200', 'xdfcb-main-menu', document.getElementById('LinkList200'), {}, 'displayModeFull'));
_WidgetManager._RegisterWidget('_LinkListView', new _WidgetInfo('LinkList201', 'headerbar', document.getElementById('LinkList201'), {}, 'displayModeFull'));
_WidgetManager._RegisterWidget('_LinkListView', new _WidgetInfo('LinkList202', 'headerbar', document.getElementById('LinkList202'), {}, 'displayModeFull'));
_WidgetManager._RegisterWidget('_BlogView', new _WidgetInfo('Blog1', 'main', document.getElementById('Blog1'), {'cmtInteractionsEnabled': false, 'lightboxEnabled': true, 'lightboxModuleUrl': 'https://www.blogger.com/static/v1/jsbin/2646514562-lbx.js', 'lightboxCssUrl': 'https://www.blogger.com/static/v1/v-css/1964470060-lightbox_bundle.css'}, 'displayModeFull'));
_WidgetManager._RegisterWidget('_HTMLView', new _WidgetInfo('HTML51', 'xdfcb-related-posts', document.getElementById('HTML51'), {}, 'displayModeFull'));
_WidgetManager._RegisterWidget('_HeaderView', new _WidgetInfo('Header1', 'sidebar', document.getElementById('Header1'), {}, 'displayModeFull'));
_WidgetManager._RegisterWidget('_LabelView', new _WidgetInfo('Label2', 'sidebar', document.getElementById('Label2'), {}, 'displayModeFull'));
_WidgetManager._RegisterWidget('_StatsView', new _WidgetInfo('Stats1', 'sidebar', document.getElementById('Stats1'), {'title': 'Total Pageviews', 'showGraphicalCounter': false, 'showAnimatedCounter': false, 'showSparkline': true, 'statsUrl': '//alexrait.blogspot.com/b/stats?style\x3dBLACK_TRANSPARENT\x26timeRange\x3dALL_TIME\x26token\x3dAPq4FmBJkOC4-TnBMYhwdMmO2DpSjS8l1wtHqXhh7pDek9iIoS596yu1CQzBhZpKpymGkm4kWelGXvaI0xw77bxV6fULWr9gZA'}, 'displayModeFull'));
_WidgetManager._RegisterWidget('_PopularPostsView', new _WidgetInfo('PopularPosts2', 'sidebar', document.getElementById('PopularPosts2'), {}, 'displayModeFull'));
_WidgetManager._RegisterWidget('_PopularPostsView', new _WidgetInfo('PopularPosts1', 'sidebar', document.getElementById('PopularPosts1'), {}, 'displayModeFull'));
_WidgetManager._RegisterWidget('_ImageView', new _WidgetInfo('Image51', 'xdfcb-about-section', document.getElementById('Image51'), {'resize': true}, 'displayModeFull'));
</script>
</body>

Reset or Set the value of the auto increment index in SQL Server

Post a Comment