{"id":27723,"date":"2022-11-28T12:16:56","date_gmt":"2022-11-28T02:16:56","guid":{"rendered":"https:\/\/database.guide\/?p=27723"},"modified":"2022-12-01T22:29:33","modified_gmt":"2022-12-01T12:29:33","slug":"how-to-reset-a-sequence-in-sql-server","status":"publish","type":"post","link":"https:\/\/database.guide\/how-to-reset-a-sequence-in-sql-server\/","title":{"rendered":"How to Reset a Sequence in SQL Server"},"content":{"rendered":"\n

In SQL Server, we can use sequences to generate sequence numbers that increment by a specified amount. This means that any new number generated by the sequence will be the next sequential increment as specified in the sequence’s definition.<\/p>\n\n\n\n

Normally, this is exactly what we want. We want each number to adhere to the increment that we specified when defining the sequence. <\/p>\n\n\n\n

But what if we want to reset the sequence, so that the numbering starts all over again? In other words, we want to restart the sequence from the beginning. Or what if we want to reset the sequence so that it increments in a different range? <\/p>\n\n\n\n

Fortunately, we can reset a sequence with the ALTER SEQUENCE<\/code> statement.<\/p>\n\n\n\n\n\n\n\n

Example<\/h2>\n\n\n\n

Suppose we create a sequence like this:<\/p>\n\n\n\n

CREATE SEQUENCE Sequence1\n    START WITH 1\n    INCREMENT BY 1;<\/code><\/pre>\n\n\n\n
And start using it:<\/p>\n\n\n\n
SELECT \n    NEXT VALUE FOR Sequence1 AS CustomerNumber,\n    CustomerName\nFROM Customers;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
CustomerNumber  CustomerName      \n--------------  ------------------\n1               Palm Pantry       \n2               Tall Poppy        \n3               Crazy Critters    \n4               Oops Media        \n5               Strange Names Inc.<\/pre>\n\n\n\n
Here, I used the sequence to number the rows returned in a result set of a query. In this case there were five rows, and so the sequence incremented from 1 up to 5 (because when I created the sequence, I specified START WITH 1<\/code> and INCREMENT BY 1<\/code>).<\/p>\n\n\n\n
Now, if I run the same query again, the sequence will pick up from where it left off:<\/p>\n\n\n\n
SELECT \n    NEXT VALUE FOR Sequence1 AS CustomerNumber,\n    CustomerName\nFROM Customers;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
CustomerNumber  CustomerName      \n--------------  ------------------\n6               Palm Pantry       \n7               Tall Poppy        \n8               Crazy Critters    \n9               Oops Media        \n10              Strange Names Inc.<\/pre>\n\n\n\n
This may or may not be the desired result. However, if we wanted the numbering to restart from 1 again, we can use the ALTER SEQUENCE<\/code> statement to reset the sequence.<\/p>\n\n\n\n
Here’s how we can reset the sequence:<\/p>\n\n\n\n
ALTER SEQUENCE Sequence1 RESTART WITH 1;<\/code><\/pre>\n\n\n\n
We use the RESTART WITH<\/code> argument to reset – or restart – the sequence. In this case I specified that it should restart with 1. We can alternatively just use RESTART<\/code> without specifying the WITH<\/code> part. When we do this, it will restart based on the original CREATE SEQUENCE<\/code> options.<\/p>\n\n\n\n
Now let’s run the SELECT<\/a><\/code> query again:<\/p>\n\n\n\n
SELECT \n    NEXT VALUE FOR Sequence1 AS CustomerNumber,\n    CustomerName\nFROM Customers;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
CustomerNumber  CustomerName      \n--------------  ------------------\n1               Palm Pantry       \n2               Tall Poppy        \n3               Crazy Critters    \n4               Oops Media        \n5               Strange Names Inc.<\/pre>\n\n\n\n
As expected, the sequence has been reset as specified.<\/p>\n","protected":false},"excerpt":{"rendered":"
In SQL Server, we can use sequences to generate sequence numbers that increment by a specified amount. This means that any new number generated by the sequence will be the next sequential increment as specified in the sequence’s definition. Normally, this is exactly what we want. We want each number to adhere to the increment … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[10,164,61],"class_list":["post-27723","post","type-post","status-publish","format-standard","hentry","category-sql-server","tag-how-to","tag-sequences","tag-t-sql"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/27723","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=27723"}],"version-history":[{"count":5,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/27723\/revisions"}],"predecessor-version":[{"id":27735,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/27723\/revisions\/27735"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=27723"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=27723"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=27723"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}