{"id":56677,"date":"2019-05-23T14:26:34","date_gmt":"2019-05-23T08:56:34","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=56677"},"modified":"2021-12-04T13:02:24","modified_gmt":"2021-12-04T07:32:24","slug":"sql-sequences-in-sap-hana","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/sql-sequences-in-sap-hana\/","title":{"rendered":"SQL Sequences in SAP HANA – The Best Resource for SQL Syntax & Elements"},"content":{"rendered":"

Now, it’s turn for the SQL sequences in SAP HANA. We have covered the topics right from the basics of SQL sequences to the use of sequences in SAP HANA Studio. Let’s quickly start the tutorial.<\/p>\n

What are SQL Sequences in SAP HANA?<\/h3>\n

SQL sequences are integer values which generate automatically on executing SQL statement.<\/em> A sequence can be any trail of numbers like 100, 101, 102, and so on.<\/p>\n

We use SQL sequences in SAP HANA databases and applications where there is a need to generate a unique ID on every new record entered in a database. SQL sequences are incremental list of values that you create as per the requirements of the user.<\/p>\n

You can generate employee IDs, serial numbers etc. You can generate them in ascending or descending order. The sequences are stored as database objects specific to schemas in SAP HANA.<\/p>\n

There is a separate folder as \u201cSequences\u201d under a schema node where all the sequences stores related to that schema.<\/p>\n

SQL Sequence Syntax<\/h3>\n

You can create SQL sequences using the statement of the syntax given in this section.<\/p>\n

CREATE SEQUENCE <sequence_name> [<sequence_parameter_list>] [RESET BY <subquery>]<\/pre>\n
Sequences are not a part of a database table but are used in tables and applications to generate automated series of numbers. The sequence statement can return two values, current value or next value.<\/p>\n
When you use the keyword CURRVAL<\/strong>, then you will get the current value of a sequence as a result. But, when you use keyword NEXTVAL<\/strong>, you will get the succeeding value (next value) of the current value.<\/p>\n
Syntax Elements for SQL Statements<\/h4>\n
Below are some important syntax elements which are used in the statement to create SQL statements to perform specific tasks.<\/p>\n
    \n
  • <sequence_name><\/strong> assigns the name of the sequence.<\/li>\n
  • [<sequence_parameter_list>]<\/strong> specifies one or more sequence parameters.<\/li>\n
  • START WITH <start_value><\/strong> assigns the starting value of the sequence.<\/li>\n
  • INCREMENT BY <increment_value><\/strong> decides by how much a value\u00a0will be incremented every time a new record is generated. If our start value is 100 and we set increment value at 2, then the next value will be 102. By default, the increment value sets as 1.<\/li>\n
  • MAXVALUE <max_value><\/strong> assigns the maximum value by which a sequence can be increased. The maximum value allowed by the system is 4611686018427387902<\/em>.<\/li>\n
  • NO MAXVALUE<\/strong> specifies that there is no preferred maximum value. And so, the maximum value can go up to 4611686018427387902<\/em> for ascending order and -1<\/em> for descending order.<\/li>\n
  • MINVALUE<min_value><\/strong> assigns the minimum value which a sequence can generate. The minimum value allowed by the system is -4611686018427387902<\/em>.<\/li>\n
  • NO MINVALUE<\/strong> specifies that there is no preferred minimum value. And so, the minimum value can be 1<\/em> in ascending order.<\/li>\n
  • CYCLE<\/strong> specifies that the sequence will again start with the start_value<\/em> after reaching the max_value<\/em> or min_value<\/em> thus creating a loop or a cycle of values.<\/li>\n
  • NO CYCLE<\/strong> specifies that the sequence will not restart with the start value after reaching the maximum or minimum value. NO CYCLE is set by default.<\/li>\n
  • CACHE<cache_size><\/strong> specifies the range of sequence values to be cached in a node. The cache_size<\/em> is an integer value.<\/li>\n
  • NO CACHE<\/strong> specifies that there is no selected range of sequence values to cache. NO CACHE is set by default.<\/li>\n
  • RESET BY<subquery><\/strong> directs the system to automatically execute the subquery to restart the sequence values generation upon restarting the database.<\/li>\n<\/ul>\n
    How to Use SQL Sequences in SAP HANA Studio?<\/h3>\n
    Now, let us learn how to use the sequences in SAP HANA Studio. You can generate automated sequences as columns in data tables in SAP HANA. In this section, we\u2019ll explain the use of SQL sequences with the help of an example.<\/p>\n
    1. Open the SAP HANA Studio<\/strong>. Make sure you are working on Administration Console in HANA Studio. To create an SQL statement for sequence, open the SQL editor<\/strong> in SAP HANA Studio<\/strong>.<\/p>\n
    2. Write the SQL statement to create a sequence. Suppose, we have created a sequence and named it \u201cDTF.DTFSEQUENCE<\/em>\u201d. And, specified the start value as 100. You can also mention the increment value in the same statement. By default, it is 1. Click on the green Execute<\/strong> button (on top of the editor) to execute this statement.<\/p>\n
    3. Creating a Sequence<\/strong><\/p>\n
    The statement used in our example is:<\/p>\n
    CREATE SEQUENCE DTF.DTFSEQUENCE START WITH 100<\/pre>\n
    The statement will execute successfully. You can check the successful creation of the sequence by refreshing the \u201cSequences<\/em>\u201d node under your schema. You will find the name of the newly created sequence there.<\/p>\n
    4. Inserting a Sequence in a Table Column<\/strong><\/p>\n
    Now, to insert this sequence in a table as a column, write the following script:<\/p>\n
    Select\u201cDTF\u201d.\u201cDTFSEQUENCE\u201d.nextval,\u201cDTF\u201d.\u201cDTFSTUDENTS\u201d.\u201dNAME\u201d FROM \u201cDTF\u201d.\u201dDTFSTUDENTS\u201d<\/pre>\n
    Here, we have selected our sequence by its name DTF.DTFSEQUENCE<\/em>, a table (DTFSTUDENTS<\/em>) from the schema (DTF<\/em>) and a column (NAME<\/em>).<\/p>\n
    5. Execute this statement and you will get a new column with the sequence created as we wanted.<\/p>\n
    In the table DTFSTUDENTS<\/em>, a column having sequence of numbers starting from 100 and going on to 150 (if there are total 50 students) generates automatically from the SQL sequence statement. A new sequence number will generate with every new row (student entry in our case) added in the table.<\/p>\n
    Summary<\/h3>\n
    This was all in our tutorial on SQL sequences in SAP HANA. We hope you found it useful. Here, we learned what are SQL sequences, the syntax of the statement to create sequences and how to actually use them in SAP HANA Studio.<\/p>\n
    Liked the article? Enter your feedback or queries in the comment section. We will be glad to hear from you.<\/p>\n","protected":false},"excerpt":{"rendered":"
    Now, it’s turn for the SQL sequences in SAP HANA. We have covered the topics right from the basics of SQL sequences to the use of sequences in SAP HANA Studio. Let’s quickly start...<\/p>\n","protected":false},"author":6,"featured_media":56681,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[18002],"tags":[19818,19819],"class_list":["post-56677","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sap-hana","tag-sap-hana-sql-sequences","tag-sql-sequence-syntax"],"yoast_head":"\nSQL Sequences in SAP HANA - The Best Resource for SQL Syntax & Elements - DataFlair<\/title>\n<meta name=\"description\" content=\"Grab this tutorial to learn the need of SQL sequences in SAP HANA, SQL sequence syntax and elements and process to use SQL sequence in SAP HANA. We use SQL sequences in SAP HANA databases and applications where there is a need to generate a unique ID on every new record entered in a database.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/data-flair.training\/blogs\/sql-sequences-in-sap-hana\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Sequences in SAP HANA - The Best Resource for SQL Syntax & Elements - DataFlair\" \/>\n<meta property=\"og:description\" content=\"Grab this tutorial to learn the need of SQL sequences in SAP HANA, SQL sequence syntax and elements and process to use SQL sequence in SAP HANA. We use SQL sequences in SAP HANA databases and applications where there is a need to generate a unique ID on every new record entered in a database.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/data-flair.training\/blogs\/sql-sequences-in-sap-hana\/\" \/>\n<meta property=\"og:site_name\" content=\"DataFlair\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/DataFlairWS\/\" \/>\n<meta property=\"article:published_time\" content=\"2019-05-23T08:56:34+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-12-04T07:32:24+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2019\/05\/SQL-Sequences-in-SAP-HANA-1.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"802\" \/>\n\t<meta property=\"og:image:height\" content=\"420\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"DataFlair Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:site\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"DataFlair Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Sequences in SAP HANA - The Best Resource for SQL Syntax & Elements - DataFlair","description":"Grab this tutorial to learn the need of SQL sequences in SAP HANA, SQL sequence syntax and elements and process to use SQL sequence in SAP HANA. We use SQL sequences in SAP HANA databases and applications where there is a need to generate a unique ID on every new record entered in a database.","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:\/\/data-flair.training\/blogs\/sql-sequences-in-sap-hana\/","og_locale":"en_US","og_type":"article","og_title":"SQL Sequences in SAP HANA - The Best Resource for SQL Syntax & Elements - DataFlair","og_description":"Grab this tutorial to learn the need of SQL sequences in SAP HANA, SQL sequence syntax and elements and process to use SQL sequence in SAP HANA. We use SQL sequences in SAP HANA databases and applications where there is a need to generate a unique ID on every new record entered in a database.","og_url":"https:\/\/data-flair.training\/blogs\/sql-sequences-in-sap-hana\/","og_site_name":"DataFlair","article_publisher":"https:\/\/www.facebook.com\/DataFlairWS\/","article_published_time":"2019-05-23T08:56:34+00:00","article_modified_time":"2021-12-04T07:32:24+00:00","og_image":[{"width":802,"height":420,"url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2019\/05\/SQL-Sequences-in-SAP-HANA-1.jpg","type":"image\/jpeg"}],"author":"DataFlair Team","twitter_card":"summary_large_image","twitter_creator":"@DataFlairWS","twitter_site":"@DataFlairWS","twitter_misc":{"Written by":"DataFlair Team","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/data-flair.training\/blogs\/sql-sequences-in-sap-hana\/#article","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/sql-sequences-in-sap-hana\/"},"author":{"name":"DataFlair Team","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/2c58ecb4f73a39f0ef993f1ddfcd7b89"},"headline":"SQL Sequences in SAP HANA – The Best Resource for SQL Syntax & Elements","datePublished":"2019-05-23T08:56:34+00:00","dateModified":"2021-12-04T07:32:24+00:00","mainEntityOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/sql-sequences-in-sap-hana\/"},"wordCount":941,"commentCount":0,"publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/sql-sequences-in-sap-hana\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2019\/05\/SQL-Sequences-in-SAP-HANA-1.jpg","keywords":["SAP HANA SQL Sequences","SQL Sequence Syntax"],"articleSection":["SAP HANA Tutorials"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/data-flair.training\/blogs\/sql-sequences-in-sap-hana\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/data-flair.training\/blogs\/sql-sequences-in-sap-hana\/","url":"https:\/\/data-flair.training\/blogs\/sql-sequences-in-sap-hana\/","name":"SQL Sequences in SAP HANA - The Best Resource for SQL Syntax & Elements - DataFlair","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/sql-sequences-in-sap-hana\/#primaryimage"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/sql-sequences-in-sap-hana\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2019\/05\/SQL-Sequences-in-SAP-HANA-1.jpg","datePublished":"2019-05-23T08:56:34+00:00","dateModified":"2021-12-04T07:32:24+00:00","description":"Grab this tutorial to learn the need of SQL sequences in SAP HANA, SQL sequence syntax and elements and process to use SQL sequence in SAP HANA. We use SQL sequences in SAP HANA databases and applications where there is a need to generate a unique ID on every new record entered in a database.","breadcrumb":{"@id":"https:\/\/data-flair.training\/blogs\/sql-sequences-in-sap-hana\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/data-flair.training\/blogs\/sql-sequences-in-sap-hana\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/sql-sequences-in-sap-hana\/#primaryimage","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2019\/05\/SQL-Sequences-in-SAP-HANA-1.jpg","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2019\/05\/SQL-Sequences-in-SAP-HANA-1.jpg","width":802,"height":420,"caption":"SQL Sequences in SAP HANA Topics"},{"@type":"BreadcrumbList","@id":"https:\/\/data-flair.training\/blogs\/sql-sequences-in-sap-hana\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Blog Home","item":"https:\/\/data-flair.training\/blogs\/"},{"@type":"ListItem","position":2,"name":"SAP HANA Tutorials","item":"https:\/\/data-flair.training\/blogs\/category\/sap-hana\/"},{"@type":"ListItem","position":3,"name":"SQL Sequences in SAP HANA – The Best Resource for SQL Syntax & Elements"}]},{"@type":"WebSite","@id":"https:\/\/data-flair.training\/blogs\/#website","url":"https:\/\/data-flair.training\/blogs\/","name":"DataFlair","description":"Learn Today. Lead Tomorrow.","publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/data-flair.training\/blogs\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/data-flair.training\/blogs\/#organization","name":"DataFlair","url":"https:\/\/data-flair.training\/blogs\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","width":106,"height":48,"caption":"DataFlair"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/DataFlairWS\/","https:\/\/x.com\/DataFlairWS","https:\/\/www.linkedin.com\/company\/dataflair-web-services-pvt-ltd\/","https:\/\/www.youtube.com\/user\/DataFlairWS"]},{"@type":"Person","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/2c58ecb4f73a39f0ef993f1ddfcd7b89","name":"DataFlair Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","caption":"DataFlair Team"},"description":"The DataFlair Team provides industry-driven content on programming, Java, Python, C++, DSA, AI, ML, data Science, Android, Flutter, MERN, Web Development, and technology. Our expert educators focus on delivering value-packed, easy-to-follow resources for tech enthusiasts and professionals.","url":"https:\/\/data-flair.training\/blogs\/author\/dfteam2\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/56677","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/comments?post=56677"}],"version-history":[{"count":10,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/56677\/revisions"}],"predecessor-version":[{"id":104892,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/56677\/revisions\/104892"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media\/56681"}],"wp:attachment":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media?parent=56677"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/categories?post=56677"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/tags?post=56677"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}