<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html> <head profile="http://internetalchemy.org/2003/02/profile"> <link rel="foaf" type="application/rdf+xml" title="FOAF" href="http://www.openlinksw.com/dataspace/uda/about.rdf" /> <link rel="schema.dc" href="http://purl.org/dc/elements/1.1/" /> <meta name="dc.subject" content="SQL" /> <meta name="dc.subject" content="SQL Reference" /> <meta name="dc.subject" content="Select" /> <meta name="dc.subject" content="Update" /> <meta name="dc.subject" content="delete" /> <meta name="dc.subject" content="Select Statement" /> <meta name="dc.subject" content="SQL Syntax" /> <meta name="dc.subject" content="Syntax" /> <meta name="dc.title" content="8. SQL Reference" /> <meta name="dc.subject" content="8. SQL Reference" /> <meta name="dc.creator" content="OpenLink Software Documentation Team ; " /> <meta name="dc.copyright" content="OpenLink Software, 1999 - 2009" /> <link rel="top" href="index.html" title="OpenLink Virtuoso Universal Server: Documentation" /> <link rel="search" href="/doc/adv_search.vspx" title="Search OpenLink Virtuoso Universal Server: Documentation" /> <link rel="parent" href="sqlreference.html" title="Chapter Contents" /> <link rel="prev" href="DROPXMLSCHEMA.html" title="DROP XML SCHEMA Statement" /> <link rel="next" href="insertSTMT.html" title="INSERT Statement" /> <link rel="shortcut icon" href="../images/misc/favicon.ico" type="image/x-icon" /> <link rel="stylesheet" type="text/css" href="doc.css" /> <link rel="stylesheet" type="text/css" href="/doc/translation.css" /> <title>8. SQL Reference</title> <meta http-equiv="Content-Type" content="text/xhtml; charset=UTF-8" /> <meta name="author" content="OpenLink Software Documentation Team ; " /> <meta name="copyright" content="OpenLink Software, 1999 - 2009" /> <meta name="keywords" content="SQL; SQL Reference; Select; Update; delete; Select Statement; SQL Syntax; Syntax; " /> <meta name="GENERATOR" content="OpenLink XSLT Team" /> </head> <body> <div id="header"> <a name="sequenceobjects" /> <img src="../images/misc/logo.jpg" alt="" /> <h1>8. SQL Reference</h1> </div> <div id="navbartop"> <div> <a class="link" href="sqlreference.html">Chapter Contents</a> | <a class="link" href="DROPXMLSCHEMA.html" title="DROP XML SCHEMA Statement">Prev</a> | <a class="link" href="insertSTMT.html" title="INSERT Statement">Next</a> </div> </div> <div id="currenttoc"> <form method="post" action="/doc/adv_search.vspx"> <div class="search">Keyword Search: <br /> <input type="text" name="q" /> <input type="submit" name="go" value="Go" /> </div> </form> <div> <a href="http://www.openlinksw.com/">www.openlinksw.com</a> </div> <div> <a href="http://docs.openlinksw.com/">docs.openlinksw.com</a> </div> <br /> <div> <a href="index.html">Book Home</a> </div> <br /> <div> <a href="contents.html">Contents</a> </div> <div> <a href="preface.html">Preface</a> </div> <br /> <div class="selected"> <a href="sqlreference.html">SQL Reference</a> </div> <br /> <div> <a href="sqlrefDATATYPES.html">Datatypes</a> </div> <div> <a href="udt.html">User Defined Types</a> </div> <div> <a href="sqlrefxmldatatype.html">XML Column Type</a> </div> <div> <a href="catidentifiers.html">Identifier Case & Quoting</a> </div> <div> <a href="wideidentifiers.html">Wide Character Identifiers</a> </div> <div> <a href="QUALIFIEDNAMES.html">Qualified Names</a> </div> <div> <a href="litsbraceescs.html">Literals, Brace Escapes</a> </div> <div> <a href="CREATETABLE.html">CREATE TABLE Statement</a> </div> <div> <a href="DROPTABLE.html">DROP TABLE Statement</a> </div> <div> <a href="CREATEINDEX.html">CREATE INDEX Statement</a> </div> <div> <a href="DROPINDEX.html">DROP INDEX Statement</a> </div> <div> <a href="ALTERTABLE.html">ALTER TABLE Statement</a> </div> <div> <a href="CREATEVIEW.html">CREATE VIEW Statement</a> </div> <div> <a href="CREATEXMLSCHEMA.html">CREATE XML SCHEMA Statement</a> </div> <div> <a href="DROPXMLSCHEMA.html">DROP XML SCHEMA Statement</a> </div> <div class="selected"> <a href="sequenceobjects.html">Sequence Objects</a> </div> <div> <a href="insertSTMT.html">INSERT Statement</a> </div> <div> <a href="updatestmt.html">UPDATE Statement</a> </div> <div> <a href="SELECTSTMT.html">SELECT Statement</a> </div> <div> <a href="COMMIT_ROLLBACK.html">COMMIT WORK, ROLLBACK WORK Statement</a> </div> <div> <a href="CHECKPOINT.html">CHECKPOINT, SHUTDOWN Statement</a> </div> <div> <a href="spasviewsandtables.html">Stored Procedures as Views & Derived Tables</a> </div> <div> <a href="GRANT.html">GRANT, REVOKE Statement</a> </div> <div> <a href="SETstmt.html">SET Statement</a> </div> <div> <a href="anytimequeries.html">Anytime Queries</a> </div> <div> <a href="besteffortunion.html">Best Effort Union</a> </div> <div> <a href="aggregates.html">Standard and User-Defined Aggregate Functions</a> </div> <div> <a href="sqloptimizer.html">Virtuoso SQL Optimization</a> </div> <div> <a href="sqlinverse.html">SQL Inverse Functions</a> </div> <div> <a href="GRAMMAR.html">SQL Grammar</a> </div> <div> <a href="BITMAPINDICES.html">Bitmap Indices</a> </div> <div> <a href="transitivityinsQL.html">Transitivity in SQL</a> </div> <div> <a href="sqlreffastphrasematch.html">Fast Phrase Match Processor</a> </div> <br /> </div> <div id="text"> <a name="sequenceobjects" /> <h2>8.16. Sequence Objects</h2> <p> Virtuoso supports sequence objects. These can be used to generate sequential numbers which can be used as unique identifiers. A sequence object is guaranteed never to give the same number twice. Each sequence has a name and a state. The state of a sequence is stored in the database at checkpoint time. Between checkpoints sequence states are logged so that a possible roll forward recovery will not lose information. </p> <p> The SQL functions <span class="computeroutput">sequence_next</span>() and <span class="computeroutput">sequence_set</span>() are used to access and set the state of sequences. These take the name of the sequence as argument. This is a server/wide unique string. There are no restrictions on the length or character set of the sequence </p> <p> Sequences do not have to be separately created. A sequence object will automatically be generated when first referenced by <span class="computeroutput">sequence_next</span>() or sequence_set. </p> <div> <pre class="programlisting">sequence_next (in name varchar) returns integer </pre> </div> <div> <pre class="programlisting">sequence_set (in name varchar, in state integer, in mode integer) returns integer </pre> </div> <p> Function <span class="computeroutput">sequence_next</span>() returns the current state of the specified sequence and atomically increments it by one. The next call will thus return a number one greater than the previous. The sequence is shared between all connections and all transactions. Using a sequence never involves locking. </p> <p> Function <span class="computeroutput">sequence_set</span>() sets and returns the state of a sequence object. The next call to <span class="computeroutput">sequence_next</span>() will return this same number. If mode equals 0, the state is set regardless of the previous state. If mode is non-zero, the state is set only if the new state is greater then the previous state. Calling sequence_set ('sequence'' , 0, 1) will always return the sequence's state without changing it. </p> <p> Each autoincrement column corresponds to an internal sequence object. The name of the sequence object is'DB.DBA.' plus the concatenation of the table's qualifier, owner, table name and column name, e.g. 'DB.DBA.db.dba.my_table.ai_column'. The user does not normally need to know about the sequence associated with an autoincrement column unless he or she wishes to change the sequence values using the <span class="computeroutput">sequence_set()</span> function and the sequence objects name.</p> <p> See the section on identity columns under create table and the function identity_value and the related ODBC statement option SQL_GETLASTSERIAL for more. </p> <table border="0" width="90%" id="navbarbottom"> <tr> <td align="left" width="33%"> <a href="DROPXMLSCHEMA.html" title="DROP XML SCHEMA Statement">Previous</a> <br />DROP XML SCHEMA Statement</td> <td align="center" width="34%"> <a href="sqlreference.html">Chapter Contents</a> </td> <td align="right" width="33%"> <a href="insertSTMT.html" title="INSERT Statement">Next</a> <br />INSERT Statement</td> </tr> </table> </div> <div id="footer"> <div>Copyright© 1999 - 2009 OpenLink Software All rights reserved.</div> <div id="validation"> <a href="http://validator.w3.org/check/referer"> <img src="http://www.w3.org/Icons/valid-xhtml10" alt="Valid XHTML 1.0!" height="31" width="88" /> </a> <a href="http://jigsaw.w3.org/css-validator/"> <img src="http://jigsaw.w3.org/css-validator/images/vcss" alt="Valid CSS!" height="31" width="88" /> </a> </div> </div> </body> </html>