{"id":8218,"date":"2019-01-19T10:41:31","date_gmt":"2019-01-19T10:41:31","guid":{"rendered":"https:\/\/dbtut.com\/?p=8218"},"modified":"2019-01-19T10:43:26","modified_gmt":"2019-01-19T10:43:26","slug":"sequence-in-sql-servertsql","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2019\/01\/19\/sequence-in-sql-servertsql\/","title":{"rendered":"SEQUENCE in SQL Server(TSQL)"},"content":{"rendered":"

 <\/p>\n

Sequence is a table-independent structure similar to the identity logic in SQL Server. You can use the sequence in more than one table.<\/p>\n

You may want to read the article “Identitiy Insert On SQL Server<\/a>” for more information on Identity.<\/p>\n

With Sequence, numeric and sequential numbers are generated from certain values. Sequential numbers can be generated for different tables using the same sequence in multiple tables.<\/p>\n

It would be more appropriate to go through an example to know the Sequence better.<\/p>\n

For example, in the following example, we create a sequence.<\/p>\n

CREATE SEQUENCE SeqExample --Sequence name is SeqExample\n AS INTEGER --Data type is INT\n START WITH 1 --Starts from 1\n INCREMENT BY 1 --Sequence value increases one by one\n MINVALUE 1 --min value is 1\n MAXVALUE 99 --max value is 99\n NO CYCLE --When the Sequence value reaches 99, it does not start again from 1\nNO CACHE; --it does not keep the sequence numbers in the cache.<\/pre>\n
If we created CACHE 30 instead of NO CACHE, it would cache numbers from 1 to 30, and when it reached 30, it would cache the numbers from 30 to 60 again.<\/p>\n
Giving a CACHE value to make less IO seems to be good in terms of performance, but it can be seen that it doesn’t noticeably improve performance, but the numbers in the cache may be lost if the server unexpectedly shuts down.<\/p>\n
After creating the Sequence, let’s start using it.<\/p>\n
We can use it as follows during the table creation.<\/p>\n
CREATE TABLE SeqTable (\n    ID       int not null CONSTRAINT seqconst DEFAULT NEXT value FOR SeqExample,\n    City    varchar(20) not null\n)<\/pre>\n
Or we can add it to an existing table as follows.<\/p>\n
ALTER TABLE SeqTable\nADD CONSTRAINT seqconst\nDEFAULT NEXT value FOR SeqExample\nFOR ID<\/pre>\n
Since we’re linking the sequence to the table when we create the table, the above ALTER TABLE statement will give an error as follows.<\/p>\n
Msg 1781, Level 16, State 1, Line 1<\/i><\/p>\n
Column already has a DEFAULT bound to it.<\/i><\/p>\n
Msg 1750, Level 16, State 0, Line 1<\/i><\/p>\n
Could not create constraint or index. See previous errors.<\/i><\/p>\n
\"\"<\/p>\n
Or, instead of adding to the table, we can get this value from the sequence in the insert clause.<\/p>\n
Although we’re linking the sequence to the table while creating the Table, the following query won’t fail.<\/p>\n
Even if there is a sequence linked to the table, we can add a record to the table as follows using same or another sequence.<\/p>\n
INSERT INTO [dbo].[SeqTable] ([ID],[City]) VALUES   (NEXT VALUE FOR SeqExample,'ANKARA')\nINSERT INTO [dbo].[SeqTable] ([ID],[City]) VALUES   (NEXT VALUE FOR SeqExample,'ISTANBUL')<\/pre>\n
We can re-program the Sequence to start after a certain value by ALTER.<\/p>\n
ALTER SEQUENCE SeqExample\nRESTART WITH 70;<\/pre>\n
After we’ve changed the Sequence, let’s add another record.<\/p>\n
INSERT INTO [dbo].[SeqTable] ([ID],[City]) VALUES   (NEXT VALUE FOR SeqExample,'IZMIR')\nSelect * FROM [dbo].[SeqTable]<\/pre>\n
As you can see, since we added the IZMIR record after reprograming the sequence, the ID value of IZMIR was 70.<\/p>\n
\"\"<\/p>\n
You can see the existing sequences as follows.<\/p>\n
SELECT name,current_value FROM sys.sequences<\/pre>\n
\"\"<\/p>\n\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
  Sequence is a table-independent structure similar to the identity logic in SQL Server. You can use the sequence in more than one table. You may want to read the article “Identitiy Insert On SQL Server” for more information on Identity. With Sequence, numeric and sequential numbers are generated from certain values. Sequential numbers can …<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[3,1596],"tags":[1703,1709,1704,1707,1702,1706,1705,1708,1701],"class_list":["post-8218","post","type-post","status-publish","format-standard","","category-mssql","category-tsql","tag-identitiy-and-sequence","tag-increment-by","tag-restart-sequence","tag-sequence-cycle","tag-sequence-in-tsql","tag-sequence-max-value","tag-sequence-min-value","tag-sequnce-cache","tag-sql-server-sequence"],"aioseo_notices":[],"yoast_head":"\nSEQUENCE in SQL Server(TSQL) - Database Tutorials<\/title>\n<meta name=\"description\" content=\"SEQUENCE in SQL Server(TSQL)\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbtut.com\/index.php\/2019\/01\/19\/sequence-in-sql-servertsql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SEQUENCE in SQL Server(TSQL) - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"SEQUENCE in SQL Server(TSQL)\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2019\/01\/19\/sequence-in-sql-servertsql\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2019-01-19T10:41:31+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-01-19T10:43:26+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/01\/img_5c42fdfb30792.png\" \/>\n<meta name=\"author\" content=\"dbtut\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"dbtut\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/19\/sequence-in-sql-servertsql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/19\/sequence-in-sql-servertsql\/\"},\"author\":{\"name\":\"dbtut\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\"},\"headline\":\"SEQUENCE in SQL Server(TSQL)\",\"datePublished\":\"2019-01-19T10:41:31+00:00\",\"dateModified\":\"2019-01-19T10:43:26+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/19\/sequence-in-sql-servertsql\/\"},\"wordCount\":357,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/19\/sequence-in-sql-servertsql\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/01\/img_5c42fdfb30792.png\",\"keywords\":[\"Identitiy and Sequence\",\"INCREMENT BY\",\"Restart Sequence\",\"sequence cycle\",\"Sequence in TSQL\",\"Sequence max value\",\"Sequence min value\",\"sequnce cache\",\"SQL Server Sequence\"],\"articleSection\":[\"MSSQL\",\"TSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/01\/19\/sequence-in-sql-servertsql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/19\/sequence-in-sql-servertsql\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/19\/sequence-in-sql-servertsql\/\",\"name\":\"SEQUENCE in SQL Server(TSQL) - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/19\/sequence-in-sql-servertsql\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/19\/sequence-in-sql-servertsql\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/01\/img_5c42fdfb30792.png\",\"datePublished\":\"2019-01-19T10:41:31+00:00\",\"dateModified\":\"2019-01-19T10:43:26+00:00\",\"description\":\"SEQUENCE in SQL Server(TSQL)\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/19\/sequence-in-sql-servertsql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/01\/19\/sequence-in-sql-servertsql\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/19\/sequence-in-sql-servertsql\/#primaryimage\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/01\/img_5c42fdfb30792.png\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/01\/img_5c42fdfb30792.png\",\"width\":599,\"height\":290},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/19\/sequence-in-sql-servertsql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SEQUENCE in SQL Server(TSQL)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\",\"name\":\"dbtut\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"caption\":\"dbtut\"},\"description\":\"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.\",\"sameAs\":[\"http:\/\/NurullahCAKIR\"],\"url\":\"https:\/\/dbtut.com\/index.php\/author\/dbtut\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SEQUENCE in SQL Server(TSQL) - Database Tutorials","description":"SEQUENCE in SQL Server(TSQL)","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/dbtut.com\/index.php\/2019\/01\/19\/sequence-in-sql-servertsql\/","og_locale":"en_US","og_type":"article","og_title":"SEQUENCE in SQL Server(TSQL) - Database Tutorials","og_description":"SEQUENCE in SQL Server(TSQL)","og_url":"https:\/\/dbtut.com\/index.php\/2019\/01\/19\/sequence-in-sql-servertsql\/","og_site_name":"Database Tutorials","article_published_time":"2019-01-19T10:41:31+00:00","article_modified_time":"2019-01-19T10:43:26+00:00","og_image":[{"url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/01\/img_5c42fdfb30792.png","type":"","width":"","height":""}],"author":"dbtut","twitter_card":"summary_large_image","twitter_misc":{"Written by":"dbtut","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2019\/01\/19\/sequence-in-sql-servertsql\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/01\/19\/sequence-in-sql-servertsql\/"},"author":{"name":"dbtut","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408"},"headline":"SEQUENCE in SQL Server(TSQL)","datePublished":"2019-01-19T10:41:31+00:00","dateModified":"2019-01-19T10:43:26+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/01\/19\/sequence-in-sql-servertsql\/"},"wordCount":357,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/01\/19\/sequence-in-sql-servertsql\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/01\/img_5c42fdfb30792.png","keywords":["Identitiy and Sequence","INCREMENT BY","Restart Sequence","sequence cycle","Sequence in TSQL","Sequence max value","Sequence min value","sequnce cache","SQL Server Sequence"],"articleSection":["MSSQL","TSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2019\/01\/19\/sequence-in-sql-servertsql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2019\/01\/19\/sequence-in-sql-servertsql\/","url":"https:\/\/dbtut.com\/index.php\/2019\/01\/19\/sequence-in-sql-servertsql\/","name":"SEQUENCE in SQL Server(TSQL) - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/01\/19\/sequence-in-sql-servertsql\/#primaryimage"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/01\/19\/sequence-in-sql-servertsql\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/01\/img_5c42fdfb30792.png","datePublished":"2019-01-19T10:41:31+00:00","dateModified":"2019-01-19T10:43:26+00:00","description":"SEQUENCE in SQL Server(TSQL)","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/01\/19\/sequence-in-sql-servertsql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2019\/01\/19\/sequence-in-sql-servertsql\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/index.php\/2019\/01\/19\/sequence-in-sql-servertsql\/#primaryimage","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/01\/img_5c42fdfb30792.png","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/01\/img_5c42fdfb30792.png","width":599,"height":290},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2019\/01\/19\/sequence-in-sql-servertsql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"SEQUENCE in SQL Server(TSQL)"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408","name":"dbtut","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","caption":"dbtut"},"description":"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.","sameAs":["http:\/\/NurullahCAKIR"],"url":"https:\/\/dbtut.com\/index.php\/author\/dbtut\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/8218","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=8218"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/8218\/revisions"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=8218"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=8218"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=8218"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}