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/745881458-widgets.js"></script>
<script type='text/javascript'>
window['__wavt'] = 'AOuZoY4efNbSpXSeuEAc2yWcNHHLdnXjbg:1734956685913';_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/361ce9cf7a112c52', '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': 'Check if a file is locked in Windows', 'id': 1176128887106018334}, {'title': 'The mystery behind file extensions', 'id': 3638078121481613638}, {'title': 'Doing upsert (insert or update) in MariaDb', 'id': 6386931249993900423}, {'title': 'System.text.json generates camel case properties when serializing objects', 'id': 6214151723817772864}]}, {'title': 'Most Popular Posts', 'type': 'PopularPosts', 'sectionId': 'sidebar', 'id': 'PopularPosts1', 'posts': [{'title': 'Check if a file is locked in Windows', 'id': '1176128887106018334'}, {'title': 'The mystery behind file extensions', 'id': '3638078121481613638'}, {'title': 'Doing upsert (insert or update) in MariaDb', 'id': '6386931249993900423'}, {'title': 'System.text.json generates camel case properties when serializing objects', 'id': '6214151723817772864'}, {'title': 'OpenIDDict example with client credentials flow', 'id': '8256163416448296805'}, {'title': 'NLog - working with scopes', 'id': '2304731500038370121'}]}, {'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/3846405171-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\x3dAPq4FmBR9jaYRQN6KRAiGdGUVcLnbHqZ3qj5LT395eLB9M4bBgyN7IfKATcKu6LZkCqU6uOivetyKCsf0wLXfAmY26rkbgNRyA'}, '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