{"id":2227,"date":"2020-01-30T05:01:05","date_gmt":"2020-01-30T05:01:05","guid":{"rendered":"http:\/\/kusuaks7\/?p=1832"},"modified":"2024-01-22T13:21:50","modified_gmt":"2024-01-22T13:21:50","slug":"what-is-a-sequence-how-to-use-a-sequence-in-ms-sql-server","status":"publish","type":"post","link":"https:\/\/www.experfy.com\/blog\/bigdata-cloud\/what-is-a-sequence-how-to-use-a-sequence-in-ms-sql-server\/","title":{"rendered":"What Is a Sequence? How to Use a Sequence in MS SQL Server"},"content":{"rendered":"\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tIn this post, I will explain how Sequence works in SQL Server that has started to be used since the 2012 version.\u00a0In the Oracle database, sequences have been used for a long time, but in MSSQL Server they have just started to be used with version 2012.\u00a0The\u00a0SEQUENCE\u00a0object is most commonly used to obtain the value for a surrogate key in tables, most commonly used in the same way as\u00a0IDENTITY\u00a0is used.\u00a0To clarify immediately, SEQUENCE is not the same as IDENTITY and is not a replacement for IDENTITY.\u00a0IDENTITY is the property of a specific column on a concrete table and SEQUENCE is an object.\u00a0SEQUENCE was added because of some non-IDENTITY features that were still needed.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t

What are the traits?<\/em><\/h3><\/h3>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tFirst, SEQUENCE is a separate object and is not bound to a specific table and one SEQUENCE object can be used on multiple tables in parallel.\u00a0Second, SEQUENCE can be altered. Then the SEQUENCE object can define the rank etc\u2026\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tJust that the same SEQUENCE object can be used on two tables as the primary key was enough for me.\u00a0Let me remind you that IDENTITY is most commonly used as the primary key in spreadsheets.\u00a0The problem was that such a primary key could not be used in PARTIALIZED VIEWs that could be greatly improved by performance.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tBut let\u2019s go in order.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tThe syntax for SEQUENCE is:\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tCREATE SEQUENCE [schema_name . ] sequence_name\n[ AS [ built_in_integer_type | user-defined_integer_type ] ]\n[ START WITH \u00a0]\n[ INCREMENT BY \u00a0]\n[ { MINVALUE [ \u00a0] } | { NO MINVALUE } ]\n[ { MAXVALUE [ \u00a0] } | { NO MAXVALUE } ]\n[ CYCLE | { NO CYCLE } ]\n[ { CACHE [ \u00a0] } | { NO CACHE } ]\n[ ; ]<\/span>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\nSyntax details can be seen on\u00a0MSDN<\/a>.\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tThe code for creating one SEQUENCE object is given in Example 1.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tUSE Test;\nCREATE SEQUENCE dbo.seq_My_Test AS INT\nSTART WITH 1\nINCREMENT BY 1;<\/span>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tExample 1. Creating a SEQUENCE object<\/strong>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tThe new SEQUENCE object can also be seen in the Microsoft SQL Server Management Studio.\n

Created a SEQUENCE object in Microsoft SQL Server Management Studio<\/p>\nIf you right-click on the \u201cSequences\u201d folder in Microsoft SQL Server Management Studio or on an existing SEQUENCE object, you will be presented with a popup menu with options for creating and maintaining SEQUENCE objects.\n

\u00a0Popup menu for creating and maintaining SEQUENCE objects<\/p>\nIn order to get values from the SEQUENCE object, we created in example 1, NEXT VALUE FOR can be used as given in example 2.\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t

\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\nSELECT NEXT VALUE FOR dbo.seq_My_Test;\nSELECT NEXT VALUE FOR dbo.seq_My_Test;<\/span>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\nExample 2. Using a SEQUENCE object to get values<\/strong>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tThe result of executing the TSQL code from Example 2\u00a0 The result of invoking the SEQUENCE object from Example 2\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tSince I already mentioned the use of the same SEQUENCE object on two tables, in example 3 the TSQL code was created to create two tables and insert data into them using the SEQUENCE object.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\nCREATE TABLE dbo.My_Table1( \u00a0 Seq_ID INT NOT NULL, NameSurname NVARCHAR(50) NOT NULL );<\/span>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tCREATE TABLE dbo.My_Table2 ( \u00a0 Seq_ID INT NOT NULL, NameSurname NVARCHAR(50) NOT NULL );\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tINSERT INTO dbo.My_Table1(Seq_ID, ImeIPrezime) VALUES(NEXT VALUE FOR dbo.seq_My_Test, N’Ela Ibric’);\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\nINSERT INTO dbo.My_Table2 (Seq_ID, ImeIPrezime) VALUES(NEXT VALUE FOR dbo.seq_My_Test, N’Alen Ibric’);\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\nSELECT * FROM My_Table1\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tSELECT * FROM My_Table2\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\nExample 3. Using the same SEQUENCE object in two tables<\/strong>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\nThe existing SEQUENCE facility is very easy to restart if the need arises. The TSQL restart of the SEQUENCE object is given in Example 4.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\nALTER SEQUENCE seq_My_Test RESTART WITH 1<\/span>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tExample 4. Restarting a SEQUENCE object<\/strong>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tA SEQUENCE object can be created with the rank of values that a particular SEQUENCE can generate. The TSQL code for creating a SEQUENCE ranked object is given in Example 5.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tCREATE SEQUENCE seqRang_My_Test AS int\nSTART WITH 1\nINCREMENT BY 1\nMINVALUE 1\nMAXVALUE 100<\/span>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tExample 5. Creating a defined-ranked SEQUENCE object<\/strong>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\nUsing the sp_sequence_get_range function obtains a ranking of values from a specific SEQUENCE object (example 6).\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tDECLARE @first_value sql_variant ,@last_value sql_variant<\/span>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tEXEC sp_sequence_get_range\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\n@sequence_name = N’seqRang_My_Test’ ,\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\n@range_size = 10 ,\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\n@range_first_value = @first_value \u00a0OUTPUT ,\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tSELECT @first_value AS FirstNumber,\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t@last_value as LastNumber\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tExample 6. Obtaining a value rank from a SEQUENCE object<\/strong>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tWhen a specific SEQUENCE object is called a sufficient number of times to exceed the maximum value that was defined when creating a SEQUENCE object, then an error will occur as in Figure 3.\n

Figure 3. An error occurring when a particular SEQUENCE object exceeds a defined maximum value<\/p>\nFinally, the properties of a particular SEQUENCE object can be checked using the system view as given in Example 6.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t

\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tSELECT * FROM sys.sequences WHERE name = ‘seq_My_Test’;<\/span>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tExample 7. TSQL code to obtain the properties of a particular SEQUENCE object.<\/strong>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\nThe properties of a particular SEQUENCE object can also be accessed through Microsoft SQL Management Studio through the \u201cProperties\u201d option from the popup menu of a specific SEQUENCE object (right-click on a specific SEQUENCE object). Through the form given in Figure 4, the properties of a particular SEQUENCE object can also be changed.\n

\u00a0Properties of the SEQUENCE object form<\/p>\nThis is all about sequences that are now being used extensively in the MSSQL server. I don\u2019t know if this is something in response to ORACLE by Microsoft that has been using this sequencing technique for a long time<\/em>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<\/div>\n\t\t","protected":false},"excerpt":{"rendered":"

This post explains how Sequence works in SQL Server that has started to be used since the 2012 version. First, SEQUENCE is a separate object and is not bound to a specific table and one SEQUENCE object can be used on multiple tables in parallel. Second, SEQUENCE can be altered. Then the SEQUENCE object can define the rank etc  The SEQUENCE object is most commonly used to obtain the value for a surrogate key in tables, most commonly used in the same way as IDENTITY is used.  <\/p>\n","protected":false},"author":685,"featured_media":23546,"comment_status":"open","ping_status":"open","sticky":false,"template":"single-post-2.php","format":"standard","meta":{"content-type":"","footnotes":""},"categories":[187],"tags":[94],"ppma_author":[3475],"class_list":["post-2227","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-bigdata-cloud","tag-data-science"],"authors":[{"term_id":3475,"user_id":685,"is_guest":0,"slug":"alen-ibric","display_name":"Alen \u0130bri\u00e7","avatar_url":"https:\/\/www.experfy.com\/blog\/wp-content\/uploads\/2020\/04\/medium_7b9fc7fb-1f17-4554-9f19-b942b50e055c-150x150.jpg","user_url":"http:\/\/www.bilkent.edu.tr\/","last_name":"\u0130bri\u00e7","first_name":"Alen","job_title":"","description":"Alen \u0130bri\u00e7<\/a> is Senior Computer Engineer at Bilkent University, Turkey."}],"_links":{"self":[{"href":"https:\/\/www.experfy.com\/blog\/wp-json\/wp\/v2\/posts\/2227","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.experfy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.experfy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.experfy.com\/blog\/wp-json\/wp\/v2\/users\/685"}],"replies":[{"embeddable":true,"href":"https:\/\/www.experfy.com\/blog\/wp-json\/wp\/v2\/comments?post=2227"}],"version-history":[{"count":7,"href":"https:\/\/www.experfy.com\/blog\/wp-json\/wp\/v2\/posts\/2227\/revisions"}],"predecessor-version":[{"id":35579,"href":"https:\/\/www.experfy.com\/blog\/wp-json\/wp\/v2\/posts\/2227\/revisions\/35579"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.experfy.com\/blog\/wp-json\/wp\/v2\/media\/23546"}],"wp:attachment":[{"href":"https:\/\/www.experfy.com\/blog\/wp-json\/wp\/v2\/media?parent=2227"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.experfy.com\/blog\/wp-json\/wp\/v2\/categories?post=2227"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.experfy.com\/blog\/wp-json\/wp\/v2\/tags?post=2227"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.experfy.com\/blog\/wp-json\/wp\/v2\/ppma_author?post=2227"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}