{"id":714,"date":"2013-04-14T22:39:26","date_gmt":"2013-04-14T17:09:26","guid":{"rendered":"http:\/\/beginsql.wordpress.com\/?p=714"},"modified":"2015-08-02T18:45:17","modified_gmt":"2015-08-02T13:15:17","slug":"sequence-in-sql-server-2012","status":"publish","type":"post","link":"https:\/\/sqlhints.com\/2013\/04\/14\/sequence-in-sql-server-2012\/","title":{"rendered":"SEQUENCE IN SQL SERVER 2012"},"content":{"rendered":"<p>This article basically focuses towards the introduction of Sequences with extensive list of examples. To know about Sequence Limitations and Sequence Cache Management and internals you may like to visit the articles: <a href=\"https:\/\/sqlhints.com\/2013\/08\/18\/sequence-limitations-and-restrictions-sql-server-2012\/\" title=\"Sequence Limitations And Restrictions\" target=\"_blank\">Sequence Limitations and restrictions with Extensive List of examples<\/a> and <a href=\"https:\/\/sqlhints.com\/2013\/08\/19\/sequence-cache-management-sql-server-2012\/\" title=\"Sequence Cache Management and Internals\" target=\"_blank\">Sequence Cache Management and Internals with examples.<\/a><\/p>\n<p><strong>SEQUENCE<\/strong> is one of the new feature\u00a0introduced\u00a0in Sql Server 2012. Sequence is a user-defined object and as name suggests it generates sequence of numeric values according to the properties with which it is created. It is similar to Identity column, but there are many difference between them. Some of the major differences between them are:<\/p>\n<ul>\n<li>Sequence is used to generate\u00a0database-wide\u00a0sequential number, but identity column is tied to a table.<\/li>\n<li>Sequence is not associated with a table.<\/li>\n<li>Same sequence can be used in multiple tables.<\/li>\n<li>It can be used in insert statement to insert identity values, it can also be used in T-Sql Scripts.<\/li>\n<\/ul>\n<p><strong>[ALSO READ] <a href=\"https:\/\/sqlhints.com\/2015\/08\/01\/difference-between-sequence-and-identity-in-sql-server\/\">Sequence Vs Identity<\/a><br \/>\n<\/strong><br \/>\nNow let us understand the sequence concept using below extensive list of examples:<\/p>\n<p><strong>Create a Sequence with Initial value 1 and Increment by 1<\/strong><\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1; highlight: []; html-script: false\">CREATE SEQUENCE [DBO].[SequenceExample] AS INT\r\n START WITH 1\r\n INCREMENT BY 1\r\nGO<\/pre>\n<p><span style=\"color: #000000;\"><strong>Using Sequence in an Insert Statement<\/strong><\/span><\/p>\n<pre class=\"brush: sql; gutter: false; first-line: 1; highlight: []; html-script: false\">CREATE TABLE dbo.Employee(ID INT,Name VARCHAR(100))\r\nGO\r\n--Insert records into Employee table with Sequence object\r\nINSERT INTO dbo.Employee VALUES\r\n(NEXT VALUE FOR DBO.SequenceExample,&#039;BASAVARAJ BIRADAR&#039;), \r\n(NEXT VALUE FOR DBO.SequenceExample,&#039;SHREE BIRADAR&#039;),\r\n(NEXT VALUE FOR DBO.SequenceExample,&#039;PRATHAM BIRADAR&#039;)\r\nGO\r\n-- CHECK THE RECORDS INSERTED IN THE TABLE\r\nSELECT * FROM dbo.Employee WITH(NOLOCK)\r\nGO<\/pre>\n<p><span style=\"color: blue;\">Result:<\/span><br \/>\nID Name<br \/>\n&#8212;&#8212;&#8212;&#8211; &#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8211;<br \/>\n1 BASAVARAJ BIRADAR<br \/>\n2 SHREE BIRADAR<br \/>\n3 PRATHAM BIRADAR<\/p>\n<p><strong>Associate Sequence object to a table<\/strong><\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1; highlight: []; html-script: false\">CREATE TABLE dbo.Customer\r\n(ID INT DEFAULT(NEXT VALUE FOR DBO.SequenceExample),\r\n                               Name VARCHAR(100))\r\nGO\r\nINSERT INTO dbo.Customer(Name)\r\nVALUES(&#039;PINKU BIRADAR&#039;),\r\n(&#039;MONTY BIRADAR&#039;)\r\nGO\r\n-- CHECK THE RECORDS INSERTED IN THE TABLE\r\nSELECT * FROM dbo.Customer WITH(NOLOCK)\r\nGO<\/pre>\n<p><span style=\"color: blue;\">Result:<\/span><br \/>\nID Name<br \/>\n&#8212;&#8212;&#8212;&#8211; &#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8211;<br \/>\n4 PINKU BIRADAR<br \/>\n5 MONTY BIRADAR<\/p>\n<p><strong>[ALSO READ]: <a href=\"https:\/\/sqlhints.com\/2013\/08\/18\/sequence-limitations-and-restrictions-sql-server-2012\/\" target=\"_blank\">SEQUENCE Limitations and Restrictions<\/a><\/strong><\/p>\n<p><strong>Getting Next Sequence Value in A SELECT Statement<\/strong><\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1; highlight: []; html-script: false\">\r\nSELECT (NEXT VALUE FOR DBO.SequenceExample) \r\n                            AS SequenceValue\r\nGO 3<\/pre>\n<p><em>Note: Here\u00a0<a href=\"https:\/\/sqlhints.com\/2011\/10\/06\/go-statement-can-also-be-used-to-excute-batch-of-t-sql-statement-multiple-times\/\" target=\"_blank\">GO 3<\/a>\u00a0statement executes the statement above it 3 times.<\/em><\/p>\n<p><span style=\"color: blue;\">Result:<\/span><br \/>\nBeginning execution loop<br \/>\n&#8212;&#8212;&#8212;&#8211;<br \/>\n6<br \/>\n(1 row(s) affected)<\/p>\n<p>&#8212;&#8212;&#8212;&#8211;<br \/>\n7<br \/>\n(1 row(s) affected)<\/p>\n<p>&#8212;&#8212;&#8212;&#8211;<br \/>\n8<br \/>\n(1 row(s) affected)<br \/>\nBatch execution completed 3 times.<\/p>\n<p><strong>[ALSO READ]: <a href=\"https:\/\/sqlhints.com\/2013\/08\/19\/sequence-cache-management-sql-server-2012\/\" target=\"_blank\">Sequence Cache management and Internals<\/a><\/strong><\/p>\n<p><strong>Getting Sequence Next Value in a variable<\/strong><\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1; highlight: []; html-script: false\">\r\nDECLARE @EmpID AS INT \r\n        = NEXT VALUE FOR DBO.SequenceExample\r\nSELECT @EmpID AS &#039;Employee Id&#039;<\/pre>\n<p><span style=\"color: blue;\">Result:<\/span><br \/>\nEmployee Id<br \/>\n&#8212;&#8212;&#8212;&#8211;<br \/>\n9<\/p>\n<p><strong>Re-Setting the Sequence Number<\/strong><\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1; highlight: []; html-script: false\">ALTER SEQUENCE DBO.SequenceExample\r\nRESTART WITH 1 ;\r\nGO\r\n-- Verify whether sequence number is re-set\r\nSELECT (NEXT VALUE FOR DBO.SequenceExample) \r\n                           AS SequenceValue\r\nGO<\/pre>\n<p><span style=\"color: blue;\">Result:<\/span><\/p>\n<p>SequenceValue<br \/>\n&#8212;&#8212;&#8212;&#8211;<br \/>\n1<br \/>\n<strong><br \/>\n<\/strong><strong>How to get the current value of the Sequence<\/strong><\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1; highlight: []; html-script: false\">SELECT Current_Value \r\nFROM SYS.Sequences \r\nWHERE name=&#039;SequenceExample&#039;\r\nGO<\/pre>\n<p><span style=\"color: blue;\">Result:<\/span><br \/>\nCurrent_Value<br \/>\n&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;<br \/>\n1<\/p>\n<p><strong>Create Sequence Syntax:<\/strong><\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1; highlight: []; html-script: false\">CREATE SEQUENCE [schema_name . ] sequence_name\r\n [ AS [built_in_integer_type | user-defined_integer_type]]\r\n [ START WITH  ]\r\n [ INCREMENT BY  ]\r\n [ { MINVALUE [  ] } | { NO MINVALUE } ]\r\n [ { MAXVALUE [  ] } | { NO MAXVALUE } ]\r\n [ CYCLE | { NO CYCLE } ]\r\n [ { CACHE [  ] } | { NO CACHE } ]\r\n [ ; ]<\/pre>\n<p><strong>Where:<\/strong><br \/>\n<strong>Start with:<\/strong>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0the initial value to start with sequence.<br \/>\n<strong>Increment by:<\/strong>\u00a0 \u00a0the step by which the values will get incremented or decremented.<br \/>\n<strong>Minvalue:<\/strong>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 the minimum value of the sequence.<br \/>\n<strong>Maxvalue:<\/strong>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0the maximum value of the sequence.<br \/>\n<strong>Cycle \/ No Cycle:<\/strong>\u00a0 to recycle the sequence once it reaches to the maximum or minimum (if increment by is a negative number).<br \/>\n<strong>Cache \/ No Cache:<\/strong>\u00a0 to pre-allocate the number of sequences specified by the given value.<\/p>\n<p><strong>[ALSO READ]:<br \/>\n<a href=\"https:\/\/sqlhints.com\/2013\/08\/18\/sequence-limitations-and-restrictions-sql-server-2012\/\" target=\"_blank\">SEQUENCE Limitations and Restrictions<\/a><br \/>\n<a href=\"https:\/\/sqlhints.com\/2013\/08\/19\/sequence-cache-management-sql-server-2012\/\" target=\"_blank\">Sequence Cache management and Internals<\/a><\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"<p>This article basically focuses towards the introduction of Sequences with extensive list of examples. To know about Sequence Limitations and Sequence Cache Management and internals you may like to visit the articles: Sequence Limitations and restrictions with Extensive List of examples and Sequence Cache Management and Internals with examples. SEQUENCE is one of the new &hellip; <a href=\"https:\/\/sqlhints.com\/2013\/04\/14\/sequence-in-sql-server-2012\/\" class=\"more-link\">Continue reading <span class=\"screen-reader-text\">SEQUENCE IN SQL SERVER 2012<\/span> <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[3,5],"tags":[730,735,734,737,732,731,35,725,733,39,729,724,736,727,723,728,55,726],"class_list":["post-714","post","type-post","status-publish","format-standard","hentry","category-sql-server","category-sql-server-2012-sql-server","tag-associate-sequence-object-to-a-table","tag-create-sequence-syntax","tag-current-value-of-the-sequence","tag-definition-of-sequence","tag-getting-next-sequence-value","tag-getting-next-sequence-value-in-a-select-statement","tag-new-feature-in-sql-server-2012","tag-next-value-for","tag-re-setting-the-sequence-number","tag-sequence","tag-sequence-in-insert-statement","tag-sequence-in-sql-server","tag-sequence-syntax","tag-sql-next-value-for","tag-sql-sequence","tag-sql-sequence-in-insert-statement","tag-sql-server-2012","tag-sql-what-is-a-sequence"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p3xNAz-bw","_links":{"self":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/714","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/comments?post=714"}],"version-history":[{"count":22,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/714\/revisions"}],"predecessor-version":[{"id":1624,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/714\/revisions\/1624"}],"wp:attachment":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/media?parent=714"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/categories?post=714"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/tags?post=714"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}