How to reset the value of an identity column with MS SQL Server
What is an identity column?
- Identity is the way of autonumbering the rows by SQL Server.
- The identity value is generated for any table for current session and the current scope.
Current value of identity
Returns the last identity value generated for any table for current session and the current scope.
select @@identity as 'Last used num'; Last used num --------------------------------------- NULL (1 row(s) affected)
- No database is open.
- There was no activity.
Data used
use adventureworkslt;
go
SELECT productid as 'ID', name as 'Product',
productnumber as 'Part #',
listprice as 'Price'
from saleslt.product
order by productid;
select @@identity as 'Current Number';
go
DBCC CHECKIDENT ('saleslt.product', NORESEED);
go
ID Product Part # Price ----------- -------------------------------------------------- ------------------------- --------------------- 680 HL Road Frame - Black, 58 FR-R92B-58 1431.50 706 HL Road Frame - Red, 58 FR-R92R-58 1431.50 707 Sport-100 Helmet, Red HL-U509-R 34.99 708 Sport-100 Helmet, Black HL-U509 34.99 709 Mountain Bike Socks, M SO-B909-M 9.50 710 Mountain Bike Socks, L SO-B909-L 9.50 711 Sport-100 Helmet, Blue HL-U509-B 34.99 712 AWC Logo Cap CA-1098 8.99 713 Long-Sleeve Logo Jersey, S LJ-0192-S 49.99 714 Long-Sleeve Logo Jersey, M LJ-0192-M 49.99 715 Long-Sleeve Logo Jersey, L LJ-0192-L 49.99 716 Long-Sleeve Logo Jersey, XL LJ-0192-X 49.99 717 HL Road Frame - Red, 62 FR-R92R-62 1431.50 718 HL Road Frame - Red, 44 FR-R92R-44 1431.50 719 HL Road Frame - Red, 48 FR-R92R-48 1431.50 720 HL Road Frame - Red, 52 FR-R92R-52 1431.50 721 HL Road Frame - Red, 56 FR-R92R-56 1431.50 722 LL Road Frame - Black, 58 FR-R38B-58 337.22 723 LL Road Frame - Black, 60 FR-R38B-60 337.22 724 LL Road Frame - Black, 62 FR-R38B-62 337.22 725 LL Road Frame - Red, 44 FR-R38R-44 337.22 726 LL Road Frame - Red, 48 FR-R38R-48 337.22 727 LL Road Frame - Red, 52 FR-R38R-52 337.22 728 LL Road Frame - Red, 58 FR-R38R-58 337.22 729 LL Road Frame - Red, 60 FR-R38R-60 337.22 730 LL Road Frame - Red, 62 FR-R38R-62 337.22 731 ML Road Frame - Red, 44 FR-R72R-44 594.83 732 ML Road Frame - Red, 48 FR-R72R-48 594.83 733 ML Road Frame - Red, 52 FR-R72R-52 594.83 734 ML Road Frame - Red, 58 FR-R72R-58 594.83 735 ML Road Frame - Red, 60 FR-R72R-60 594.83 736 LL Road Frame - Black, 44 FR-R38B-44 337.22 737 LL Road Frame - Black, 48 FR-R38B-48 337.22 738 LL Road Frame - Black, 52 FR-R38B-52 337.22 739 HL Mountain Frame - Silver, 42 FR-M94S-42 1364.50 740 HL Mountain Frame - Silver, 44 FR-M94S-44 1364.50 741 HL Mountain Frame - Silver, 48 FR-M94S-52 1364.50 742 HL Mountain Frame - Silver, 46 FR-M94S-46 1364.50 743 HL Mountain Frame - Black, 42 FR-M94B-42 1349.60 744 HL Mountain Frame - Black, 44 FR-M94B-44 1349.60 745 HL Mountain Frame - Black, 48 FR-M94B-48 1349.60 746 HL Mountain Frame - Black, 46 FR-M94B-46 1349.60 747 HL Mountain Frame - Black, 38 FR-M94B-38 1349.60 748 HL Mountain Frame - Silver, 38 FR-M94S-38 1364.50 749 Road-150 Red, 62 BK-R93R-62 3578.27 750 Road-150 Red, 44 BK-R93R-44 3578.27 751 Road-150 Red, 48 BK-R93R-48 3578.27 752 Road-150 Red, 52 BK-R93R-52 3578.27 753 Road-150 Red, 56 BK-R93R-56 3578.27 754 Road-450 Red, 58 BK-R68R-58 1457.99 755 Road-450 Red, 60 BK-R68R-60 1457.99 756 Road-450 Red, 44 BK-R68R-44 1457.99 757 Road-450 Red, 48 BK-R68R-48 1457.99 758 Road-450 Red, 52 BK-R68R-52 1457.99 759 Road-650 Red, 58 BK-R50R-58 782.99 760 Road-650 Red, 60 BK-R50R-60 782.99 761 Road-650 Red, 62 BK-R50R-62 782.99 762 Road-650 Red, 44 BK-R50R-44 782.99 763 Road-650 Red, 48 BK-R50R-48 782.99 764 Road-650 Red, 52 BK-R50R-52 782.99 765 Road-650 Black, 58 BK-R50B-58 782.99 766 Road-650 Black, 60 BK-R50B-60 782.99 767 Road-650 Black, 62 BK-R50B-62 782.99 768 Road-650 Black, 44 BK-R50B-44 782.99 769 Road-650 Black, 48 BK-R50B-48 782.99 770 Road-650 Black, 52 BK-R50B-52 782.99 771 Mountain-100 Silver, 38 BK-M82S-38 3399.99 772 Mountain-100 Silver, 42 BK-M82S-42 3399.99 773 Mountain-100 Silver, 44 BK-M82S-44 3399.99 774 Mountain-100 Silver, 48 BK-M82S-48 3399.99 775 Mountain-100 Black, 38 BK-M82B-38 3374.99 776 Mountain-100 Black, 42 BK-M82B-42 3374.99 777 Mountain-100 Black, 44 BK-M82B-44 3374.99 778 Mountain-100 Black, 48 BK-M82B-48 3374.99 779 Mountain-200 Silver, 38 BK-M68S-38 2319.99 780 Mountain-200 Silver, 42 BK-M68S-42 2319.99 781 Mountain-200 Silver, 46 BK-M68S-46 2319.99 782 Mountain-200 Black, 38 BK-M68B-38 2294.99 783 Mountain-200 Black, 42 BK-M68B-42 2294.99 784 Mountain-200 Black, 46 BK-M68B-46 2294.99 785 Mountain-300 Black, 38 BK-M47B-38 1079.99 786 Mountain-300 Black, 40 BK-M47B-40 1079.99 787 Mountain-300 Black, 44 BK-M47B-44 1079.99 788 Mountain-300 Black, 48 BK-M47B-48 1079.99 789 Road-250 Red, 44 BK-R89R-44 2443.35 790 Road-250 Red, 48 BK-R89R-48 2443.35 791 Road-250 Red, 52 BK-R89R-52 2443.35 792 Road-250 Red, 58 BK-R89R-58 2443.35 793 Road-250 Black, 44 BK-R89B-44 2443.35 794 Road-250 Black, 48 BK-R89B-48 2443.35 795 Road-250 Black, 52 BK-R89B-52 2443.35 796 Road-250 Black, 58 BK-R89B-58 2443.35 797 Road-550-W Yellow, 38 BK-R64Y-38 1120.49 798 Road-550-W Yellow, 40 BK-R64Y-40 1120.49 799 Road-550-W Yellow, 42 BK-R64Y-42 1120.49 800 Road-550-W Yellow, 44 BK-R64Y-44 1120.49 801 Road-550-W Yellow, 48 BK-R64Y-48 1120.49 802 LL Fork FK-1639 148.22 803 ML Fork FK-5136 175.49 804 HL Fork FK-9939 229.49 805 LL Headset HS-0296 34.20 806 ML Headset HS-2451 102.29 807 HL Headset HS-3479 124.73 808 LL Mountain Handlebars HB-M243 44.54 809 ML Mountain Handlebars HB-M763 61.92 810 HL Mountain Handlebars HB-M918 120.27 811 LL Road Handlebars HB-R504 44.54 812 ML Road Handlebars HB-R720 61.92 813 HL Road Handlebars HB-R956 120.27 814 ML Mountain Frame - Black, 38 FR-M63B-38 348.76 815 LL Mountain Front Wheel FW-M423 60.745 816 ML Mountain Front Wheel FW-M762 209.025 817 HL Mountain Front Wheel FW-M928 300.215 818 LL Road Front Wheel FW-R623 85.565 819 ML Road Front Wheel FW-R762 248.385 820 HL Road Front Wheel FW-R820 330.06 821 Touring Front Wheel FW-T905 218.01 822 ML Road Frame-W - Yellow, 38 FR-R72Y-38 594.83 823 LL Mountain Rear Wheel RW-M423 87.745 824 ML Mountain Rear Wheel RW-M762 236.025 825 HL Mountain Rear Wheel RW-M928 327.215 826 LL Road Rear Wheel RW-R623 112.565 827 ML Road Rear Wheel RW-R762 275.385 828 HL Road Rear Wheel RW-R820 357.06 829 Touring Rear Wheel RW-T905 245.01 830 ML Mountain Frame - Black, 40 FR-M63B-40 348.76 831 ML Mountain Frame - Black, 44 FR-M63B-44 348.76 832 ML Mountain Frame - Black, 48 FR-M63B-48 348.76 833 ML Road Frame-W - Yellow, 40 FR-R72Y-40 594.83 834 ML Road Frame-W - Yellow, 42 FR-R72Y-42 594.83 835 ML Road Frame-W - Yellow, 44 FR-R72Y-44 594.83 836 ML Road Frame-W - Yellow, 48 FR-R72Y-48 594.83 837 HL Road Frame - Black, 62 FR-R92B-62 1431.50 838 HL Road Frame - Black, 44 FR-R92B-44 1431.50 839 HL Road Frame - Black, 48 FR-R92B-48 1431.50 840 HL Road Frame - Black, 52 FR-R92B-52 1431.50 841 Men's Sports Shorts, S SH-M897-S 59.99 842 Touring-Panniers, Large PA-T100 125.00 843 Cable Lock LO-C100 25.00 844 Minipump PU-0452 19.99 845 Mountain Pump PU-M044 24.99 846 Taillights - Battery-Powered LT-T990 13.99 847 Headlights - Dual-Beam LT-H902 34.99 848 Headlights - Weatherproof LT-H903 44.99 849 Men's Sports Shorts, M SH-M897-M 59.99 850 Men's Sports Shorts, L SH-M897-L 59.99 851 Men's Sports Shorts, XL SH-M897-X 59.99 852 Women's Tights, S TG-W091-S 74.99 853 Women's Tights, M TG-W091-M 74.99 854 Women's Tights, L TG-W091-L 74.99 855 Men's Bib-Shorts, S SB-M891-S 89.99 856 Men's Bib-Shorts, M SB-M891-M 89.99 857 Men's Bib-Shorts, L SB-M891-L 89.99 858 Half-Finger Gloves, S GL-H102-S 24.49 859 Half-Finger Gloves, M GL-H102-M 24.49 860 Half-Finger Gloves, L GL-H102-L 24.49 861 Full-Finger Gloves, S GL-F110-S 37.99 862 Full-Finger Gloves, M GL-F110-M 37.99 863 Full-Finger Gloves, L GL-F110-L 37.99 864 Classic Vest, S VE-C304-S 63.50 865 Classic Vest, M VE-C304-M 63.50 866 Classic Vest, L VE-C304-L 63.50 867 Women's Mountain Shorts, S SH-W890-S 69.99 868 Women's Mountain Shorts, M SH-W890-M 69.99 869 Women's Mountain Shorts, L SH-W890-L 69.99 870 Water Bottle - 30 oz. WB-H098 4.99 871 Mountain Bottle Cage BC-M005 9.99 872 Road Bottle Cage BC-R205 8.99 873 Patch Kit/8 Patches PK-7098 2.29 874 Racing Socks, M SO-R809-M 8.99 875 Racing Socks, L SO-R809-L 8.99 876 Hitch Rack - 4-Bike RA-H123 120.00 877 Bike Wash - Dissolver CL-9009 7.95 878 Fender Set - Mountain FE-6654 21.98 879 All-Purpose Bike Stand ST-1401 159.00 880 Hydration Pack - 70 oz. HY-1023-70 54.99 881 Short-Sleeve Classic Jersey, S SJ-0194-S 53.99 882 Short-Sleeve Classic Jersey, M SJ-0194-M 53.99 883 Short-Sleeve Classic Jersey, L SJ-0194-L 53.99 884 Short-Sleeve Classic Jersey, XL SJ-0194-X 53.99 885 HL Touring Frame - Yellow, 60 FR-T98Y-60 1003.91 886 LL Touring Frame - Yellow, 62 FR-T67Y-62 333.42 887 HL Touring Frame - Yellow, 46 FR-T98Y-46 1003.91 888 HL Touring Frame - Yellow, 50 FR-T98Y-50 1003.91 889 HL Touring Frame - Yellow, 54 FR-T98Y-54 1003.91 890 HL Touring Frame - Blue, 46 FR-T98U-46 1003.91 891 HL Touring Frame - Blue, 50 FR-T98U-50 1003.91 892 HL Touring Frame - Blue, 54 FR-T98U-54 1003.91 893 HL Touring Frame - Blue, 60 FR-T98U-60 1003.91 894 Rear Derailleur RD-2308 121.46 895 LL Touring Frame - Blue, 50 FR-T67U-50 333.42 896 LL Touring Frame - Blue, 54 FR-T67U-54 333.42 897 LL Touring Frame - Blue, 58 FR-T67U-58 333.42 898 LL Touring Frame - Blue, 62 FR-T67U-62 333.42 899 LL Touring Frame - Yellow, 44 FR-T67Y-44 333.42 900 LL Touring Frame - Yellow, 50 FR-T67Y-50 333.42 901 LL Touring Frame - Yellow, 54 FR-T67Y-54 333.42 902 LL Touring Frame - Yellow, 58 FR-T67Y-58 333.42 903 LL Touring Frame - Blue, 44 FR-T67U-44 333.42 904 ML Mountain Frame-W - Silver, 40 FR-M63S-40 364.09 905 ML Mountain Frame-W - Silver, 42 FR-M63S-42 364.09 906 ML Mountain Frame-W - Silver, 46 FR-M63S-46 364.09 907 Rear Brakes RB-9231 106.50 908 LL Mountain Seat/Saddle SE-M236 27.12 909 ML Mountain Seat/Saddle SE-M798 39.14 910 HL Mountain Seat/Saddle SE-M940 52.64 911 LL Road Seat/Saddle SE-R581 27.12 912 ML Road Seat/Saddle SE-R908 39.14 913 HL Road Seat/Saddle SE-R995 52.64 914 LL Touring Seat/Saddle SE-T312 27.12 915 ML Touring Seat/Saddle SE-T762 39.14 916 HL Touring Seat/Saddle SE-T924 52.64 917 LL Mountain Frame - Silver, 42 FR-M21S-42 264.05 918 LL Mountain Frame - Silver, 44 FR-M21S-44 264.05 919 LL Mountain Frame - Silver, 48 FR-M21S-48 264.05 920 LL Mountain Frame - Silver, 52 FR-M21S-52 264.05 921 Mountain Tire Tube TT-M928 4.99 922 Road Tire Tube TT-R982 3.99 923 Touring Tire Tube TT-T092 4.99 924 LL Mountain Frame - Black, 42 FR-M21B-42 249.79 925 LL Mountain Frame - Black, 44 FR-M21B-44 249.79 926 LL Mountain Frame - Black, 48 FR-M21B-48 249.79 927 LL Mountain Frame - Black, 52 FR-M21B-52 249.79 928 LL Mountain Tire TI-M267 24.99 929 ML Mountain Tire TI-M602 29.99 930 HL Mountain Tire TI-M823 35.00 931 LL Road Tire TI-R092 21.49 932 ML Road Tire TI-R628 24.99 933 HL Road Tire TI-R982 32.60 934 Touring Tire TI-T723 28.99 935 LL Mountain Pedal PD-M282 40.49 936 ML Mountain Pedal PD-M340 62.09 937 HL Mountain Pedal PD-M562 80.99 938 LL Road Pedal PD-R347 40.49 939 ML Road Pedal PD-R563 62.09 940 HL Road Pedal PD-R853 80.99 941 Touring Pedal PD-T852 80.99 942 ML Mountain Frame-W - Silver, 38 FR-M63S-38 364.09 943 LL Mountain Frame - Black, 40 FR-M21B-40 249.79 944 LL Mountain Frame - Silver, 40 FR-M21S-40 264.05 945 Front Derailleur FD-2342 91.49 946 LL Touring Handlebars HB-T721 46.09 947 HL Touring Handlebars HB-T928 91.57 948 Front Brakes FB-9873 106.50 949 LL Crankset CS-4759 175.49 950 ML Crankset CS-6583 256.49 951 HL Crankset CS-9183 404.99 952 Chain CH-0234 20.24 953 Touring-2000 Blue, 60 BK-T44U-60 1214.85 954 Touring-1000 Yellow, 46 BK-T79Y-46 2384.07 955 Touring-1000 Yellow, 50 BK-T79Y-50 2384.07 956 Touring-1000 Yellow, 54 BK-T79Y-54 2384.07 957 Touring-1000 Yellow, 60 BK-T79Y-60 2384.07 958 Touring-3000 Blue, 54 BK-T18U-54 742.35 959 Touring-3000 Blue, 58 BK-T18U-58 742.35 960 Touring-3000 Blue, 62 BK-T18U-62 742.35 961 Touring-3000 Yellow, 44 BK-T18Y-44 742.35 962 Touring-3000 Yellow, 50 BK-T18Y-50 742.35 963 Touring-3000 Yellow, 54 BK-T18Y-54 742.35 964 Touring-3000 Yellow, 58 BK-T18Y-58 742.35 965 Touring-3000 Yellow, 62 BK-T18Y-62 742.35 966 Touring-1000 Blue, 46 BK-T79U-46 2384.07 967 Touring-1000 Blue, 50 BK-T79U-50 2384.07 968 Touring-1000 Blue, 54 BK-T79U-54 2384.07 969 Touring-1000 Blue, 60 BK-T79U-60 2384.07 970 Touring-2000 Blue, 46 BK-T44U-46 1214.85 971 Touring-2000 Blue, 50 BK-T44U-50 1214.85 972 Touring-2000 Blue, 54 BK-T44U-54 1214.85 973 Road-350-W Yellow, 40 BK-R79Y-40 1700.99 974 Road-350-W Yellow, 42 BK-R79Y-42 1700.99 975 Road-350-W Yellow, 44 BK-R79Y-44 1700.99 976 Road-350-W Yellow, 48 BK-R79Y-48 1700.99 977 Road-750 Black, 58 BK-R19B-58 539.99 978 Touring-3000 Blue, 44 BK-T18U-44 742.35 979 Touring-3000 Blue, 50 BK-T18U-50 742.35 980 Mountain-400-W Silver, 38 BK-M38S-38 769.49 981 Mountain-400-W Silver, 40 BK-M38S-40 769.49 982 Mountain-400-W Silver, 42 BK-M38S-42 769.49 983 Mountain-400-W Silver, 46 BK-M38S-46 769.49 984 Mountain-500 Silver, 40 BK-M18S-40 564.99 985 Mountain-500 Silver, 42 BK-M18S-42 564.99 986 Mountain-500 Silver, 44 BK-M18S-44 564.99 987 Mountain-500 Silver, 48 BK-M18S-48 564.99 988 Mountain-500 Silver, 52 BK-M18S-52 564.99 989 Mountain-500 Black, 40 BK-M18B-40 539.99 990 Mountain-500 Black, 42 BK-M18B-42 539.99 991 Mountain-500 Black, 44 BK-M18B-44 539.99 992 Mountain-500 Black, 48 BK-M18B-48 539.99 993 Mountain-500 Black, 52 BK-M18B-52 539.99 994 LL Bottom Bracket BB-7421 53.99 995 ML Bottom Bracket BB-8107 101.24 996 HL Bottom Bracket BB-9108 121.49 997 Road-750 Black, 44 BK-R19B-44 539.99 998 Road-750 Black, 48 BK-R19B-48 539.99 999 Road-750 Black, 52 BK-R19B-52 539.99 (295 row(s) affected) Current Number --------------------------------------- NULL (1 row(s) affected) Checking identity information: current identity value '999', current column value '999'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
- All the rows have been incremented by one.
- It does not start at 1.
- The next insert should be 1,000.
DBCC CHECKIDENT ('saleslt.product', NORESEED);
- The checkident noreseed: displays the CURRENT VALUE.
- The checkident noreseed: does NOT display the NEXT VALUE.
Reset the identity value
DBCC CHECKIDENT ('saleslt.product', RESEED, 2500);
- The reseed value is NOT the next number that will be used.
- The reseed value is the seed for the next number, ie: 2500 therefore the next number used will be 2501.
- If you want to use 2500 as the next value, you will need:
DBCC CHECKIDENT ('saleslt.product', RESEED, 2499);
Verification of the identity renumbering
There is only one way of doing it properly, enter a new value and see:
insert saleslt.product (name, productnumber, color, standardcost, listprice, sellstartdate)
values ('Test Bike - 41', 'BK-TST-41','Green',1125,1575,'06/01/2007');
go
SELECT productid as 'ID', name as 'Product',
productnumber as 'Part #',
listprice as 'Price'
from saleslt.product
where productid > 994
order by productid;
go
DBCC CHECKIDENT ('saleslt.product', NORESEED);
go
select @@identity as 'Last Num used';
go
ID Product Part # Price ----------- -------------------------------------------------- ------------------------- --------------------- 995 ML Bottom Bracket BB-8107 101.24 996 HL Bottom Bracket BB-9108 121.49 997 Road-750 Black, 44 BK-R19B-44 539.99 998 Road-750 Black, 48 BK-R19B-48 539.99 999 Road-750 Black, 52 BK-R19B-52 539.99 2501 Test Bike - 41 BK-TST-41 1575.00 (6 row(s) affected) Checking identity information: current identity value '2501', current column value '2501'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. Last Num used --------------------------------------- 2501 (1 row(s) affected)
- The IDENTITY attribute does not guaranty that the value of that column will start at 1 and be consecutively incremented.
- The IDENTITY attribute only guaranties that the values of that column are incremented.