{"id":1757,"date":"2014-02-06T00:00:00","date_gmt":"2014-02-06T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/sql-server-sequence-basics\/"},"modified":"2021-08-16T15:01:59","modified_gmt":"2021-08-16T15:01:59","slug":"sql-server-sequence-basics","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/learn\/sql-server-sequence-basics\/","title":{"rendered":"SQL Server SEQUENCE Basics"},"content":{"rendered":"
\n

SQL Server now has the ANSI\/ISO Standard CREATE SEQUENCE<\/code> statement. Hooray! The bad news is that most programmers will not be aware of the significance of the underlying math. A SEQUENCE <\/code>is not an IDENTITY<\/code>. That propriety feature in T-SQL is a table property inherited from the old Sybase\/UNIX days. It counts the number of insertion attempts, not even successes, on one particular machine. This goes back to how files were managed on UNIX systems in the 1970s. In those systems, you needed record numbers to locate the data, so T-SQL exposed this count as IDENTITY<\/code>. Also, when a record was deleted, a bit flag on each record might be set to signal a garbage collection routine to remove this data during a file utility clean up. <\/p>\n

Obviously, IDENTITY<\/code> increments its value from physical insertion to physical insertion; no big jumps, no gaps. Again, IDENTITY<\/code> is not a column; it is a table property. That means it cannot be a key by definition.<\/i> Remember a key is a subset of columns<\/i><\/b> that are unique in every row of a table. It is not an integer; you cannot do meaningful math on it. It is non-deterministic. If you insert a set of more than one row into a table, the rows will be inserted one at time in an order determined by the indexes, internal state of the hardware and number of sessions open at the time. <\/p>\n

We have, for a long time, created a table of integers that has all sorts of uses. It starts at 1 and goes to some sufficiently large value. Its name depends on the author of the table. You will see “Numbers”, “Sequence”, “Tally_Table” and “Series” among others. Obviously, we can no longer use “Sequence”; it is a reserved word! I used it myself, and so I’ll have to re-write my books from now on. Darn. I always thought that “Numbers” was too generic; which numbers?? I never liked “Tally”, since a tally is a current score or amount, a running total, or count. It is not a set, but a process. I settled on “Series” for my stuff since a series is a completed (infinite) set, but it is not the right word. <\/p>\n

Now, let’s do math.<\/p>\n

What is the Difference Between a Sequence and a Series? <\/b> <\/h3>\n

A sequence<\/b> is a list of numbers. The order in which the numbers are listed is important,<\/i> so for instance {1, 2, 3, 4, 5, …} is one sequence, and {2, 1, 4, 3, 6, 5, …} is an entirely different sequence<\/i>. This is important. It means we can talk about the i-th member <\/i>of the sequence. <\/p>\n

A series<\/b> is a sum<\/i> of numbers. A classic example is: 1 + 1\/2 + 1\/4 + 1\/8 + 1\/16 + … which converge to 2 (or you can loosely say it will equal 2 at infinite). The order of terms can change the limit to which the series converges or it might not (“absolutely convergent”). Okay, too much math for a database article.<\/p>\n

Sequences and series are closely related to each other. But a sequence is not summed. <\/p>\n

Tag Numbers versus Sequence <\/b> <\/h3>\n

A tag number is string, usually of fixed length, made up of digits. The digits are in sequence to make them easy to sort and to generate. Think of a serial number on a manufactured product. The most common example is a vehicle identification number (VIN). This is a unique code is used by the automotive industry<\/a> to identify individual motor vehicles<\/a>,  as defined in ISO<\/a> 3833. The positions 12 to 17 are a sequence prefixed by codes for the Manufacturer Identifier, some vehicle attributes, a check digit, model year, and plant code. <\/p>\n

The check digit is a major reason we like tag numbers. This is a topic in itself, but the idea is that we can take each digit in the tag number and run it thru a formula as an integer. We get a result, usually another digit, and we attach it to the tag number. For example, the Luhn algorithm is a common method <\/p>\n

defined by ISO\/IEC 7812-1. We like it because it is simple to put in hardware. <\/p>\n

1.      Compute the sum of the digits .<\/p>\n

2.      Take the units digit from this total.<\/p>\n

3.      Subtract it from 10. <\/p>\n

It’s not a great check digit, but it catches most <\/i>of the common input errors – missing digits, extra digits, wrong digits and pairwise transposes. <\/p>\n

Now let’s into the SQL.<\/b><\/p>\n

The CREATE SEQUENCE Statement <\/b> <\/h2>\n

This statement creates a schema level object that is accessible to any user. It is not part of a table. It is not a procedure that belong to one user. The idea is that a user can invoke the SEQUENCE<\/code> with a special syntax that is used wherever an integer value of the SEQUENCE<\/code> data type would work. <\/p>\n

If you want a physical model, imagine you are in the butcher store. You walk in and pull a service ticket number from a roll of tickets on the counter. Sequence numbers are generated outside the scope of the current transaction, just like the tickets. The numbers are consumed whether the transaction using the ticket number is actually served (committed) or they walk out (rolled back). <\/p>\n

 NEXT VALUE FOR [<database_name>.] [<schema_name>.] <sequence_name>\n [OVER (<over_order_by_clause>)]\n<\/pre>\n
I will explain the OVER() <\/code>clause later; just keep the simple butcher store model in mind for now. Let’s  look at the roll of tickets (i.e. CREATE SEQUENCE<\/code>). It has to be declared with parameters that define behavior.   <\/b><\/p>\n
CREATE SEQUENCE [<schema_name>.] <sequence_name>\n [AS [built_in_integer_type | user-defined_integer_type]]\n [START WITH <constant>]\n [INCREMENT BY <constant>]\n [{MINVALUE [<constant>]} | {NO MINVALUE}]\n [{MAXVALUE [<constant>]} | {NO MAXVALUE}]\n [CYCLE | {NO CYCLE}]\n [{CACHE [<constant>]} | {NO CACHE}]\n [;]\n \n<\/pre>\n
Let’s go thru the BNF in detail. The <sequence_name><\/code> and its qualifiers explains itself. This is pure SQL.  Sequence numbers are generated outside the scope of the current transaction. They are consumed whether the transaction  using the sequence number is committed or rolled back. Think about the butcher shop ticket that gets dropped on the  floor. <\/p>\n
A sequence can be defined as any integer type: That means TINYINT, SMALLINT, INTEGER and BIGINT;<\/code> but it  also allows DECIMAL (s, 0)<\/code> and NUMERIC(s, 0)<\/code> data types. You can also a user-defined data type that is based on one of  the allowed types. Do not do that if you value portability.<\/p>\n
If no data type is provided, BIGINT<\/code> is the default. Do not use this unless you really need   a range of  -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807), which makes ‘Big Data’ seem like note on the back of the hand. As with most defaults, this is the largest, safest value in the problem space.  <\/p>\n
START WITH <constant><\/pre>\n
This is the first value returned by the sequence. The START value must be a value between the minimum and  maximum values. The default start value for a new sequence is the minimum value for an ascending sequence and the  maximum value for a descending sequence. Note this is a constant; no function calls. <\/p>\n
INCREMENT BY <constant><\/pre>\n
Value used to increment (or decrement if negative) the value of the sequence for each call to the  NEXT  VALUE FOR<\/code>. If the increment is a negative value, the sequence is descending; otherwise, it is ascending. The increment  cannot be 0, obviously. The default increment for a new sequence is 1. Again, this is a constant and not a function  call. <\/p>\n
[MINVALUE <constant> | NO MINVALUE]<\/pre>\n
Specifies the bounds for the sequence. The default minimum value for a new sequence is the minimum value of  the data type of the sequence. This is zero for the TINYINT<\/code> data type and a negative number for all other data types.<\/p>\n
NO MINVALUE<\/code> is probably a really bad design choice . You probably wanted zero or one.  <\/p>\n
[MAXVALUE <constant> | NO MAXVALUE<\/pre>\n
Specifies the bounds for the sequence. The default maximum value for a new sequence is the maximum value of  the data type of the sequence. <\/p>\n
NO MAXVALUE<\/code> is probably another bad design. If you are going to use this to create a tag number, you have  to worry about overflowing the length of your string.  <\/p>\n
If you are casting the numeric data type to strings for tag numbers, you will want to be sure that the  numbers do not overflow in the conversion. And you probably do not need the upper limit of a data type.  <\/p>\n
[CYCLE | NO CYCLE]<\/pre>\n
Property that specifies whether the sequence should restart from the minimum value (or maximum for  descending sequences) or throw an exception when its minimum or maximum value is exceeded. The default cycle option for  new sequences is NO CYCLE<\/code>. Note that cycling restarts from the minimum or maximum value, not from the start value.<\/i><\/p>\n
[CACHE [<constant>] | NO CACHE]<\/pre>\n
I am not going to explain Cache management. This is an implementation detail and I do programming.  <\/p>\n
In many financial applications, you cannot afford gaps. For example in Italy, invoice numbers have to be in  a strict sequence by law.<\/i> But you can lose all of the numbers in cache in the event of a major failure, so while  it is faster, it is also dangerous. Remember that today “ROI” means “Risk of Incarceration” <\/p>\n
OVER (ORDER BY ..) <\/code>Clause<\/b><\/h3>\n
 <\/b>Remember earlier in this article when I mentioned that how IDENTITY<\/code> in assigned to each element of a a set  insertion was not deterministic? SEQUENCE<\/code> has a way to fix this problem; you can order the sequence! If another process  is accessing the sequence object at the same time, the numbers returned could have gaps.<\/p>\n
An OVER<\/code> clause applied to the NEXT VALUE FOR<\/code> function does not support the  PARTITION BY<\/code> or the [ROW |  RANGE] <\/code>subclauses for obvious reasons. The following additional rules apply when using the NEXT VALUE FOR<\/code> function with  the OVER<\/code> clause:<\/p>\n
Multiple calls to the NEXT VALUE FOR<\/code> function for the same sequence generator in a single statement must <\/i>all use the same OVER clause definition. Again, this is obvious. <\/p>\n
Multiple calls to the NEXT VALUE FOR<\/code> function that reference different sequence generators in a single  statement can have different OVER clause definitions. They are separate schema objects. <\/p>\n
If all calls to the NEXT VALUE FOR<\/code> function in a SELECT<\/code> statement specifies the  OVER <\/code>clause, an ORDER BY<\/code>  clause may be used in the SELECT<\/code> statement. Again, the SEQUENCE<\/code> is a separate schema object. <\/p>\n
The OVER<\/code> clause is allowed with the NEXT VALUE FOR<\/code> function when used in a  SELECT<\/code> statement or INSERT ...  SELECT ... <\/code>statement. The NEXT VALUE FOR<\/code> function is not allowed in UPDATE <\/code>or MERGE <\/code>statements.<\/p>\n
Programming Examples <\/b> <\/h2>\n
To get a feel for how this works, create a simple sequence and play with it.  <\/p>\n
 \nCREATE SEQUENCE Invoice_Seq\n AS INTEGER\n START WITH 1\n INCREMENT BY 1\n MINVALUE 1\n MAXVALUE 99\n NO CYCLE; \n<\/pre>\n
 Now, just play with it. Just hit   ”SELECT NEXT VALUE FOR Invoice_Seq;<\/code>” a few times, or in SSMS execute …<\/p>\n
SELECT NEXT VALUE FOR Invoice_Seq;\n\tGO 99\nSELECT NEXT VALUE FOR Invoice_Seq;\n \n<\/pre>\n
 After you hit 99 and then invoke the next value, you will get this message.<\/p>\n
 \nMsg 11728, Level 16, State 1, Line 1\nThe sequence object 'Invoice_Seq' has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.\n \n<\/pre>\n
  We did not talk about this “restart” option yet. It is a straightforward ALTER<\/code> statement.  <\/p>\n
ALTER SEQUENCE <sequence name> RESTART [WITH <constant>];<\/pre>\n
  The WITH<\/code> option lets you assign the new starting value. If you do not give a specific value, the default is  the original starting value. In this example, we can use: <\/p>\n
  ALTER SEQUENCE Invoice_Seq RESTART WITH 3;\n  SELECT NEXT VALUE FOR Invoice_Seq;\n<\/pre>\n
  this will return 3. Most of the situations where you want to re-start a sequence can be done with the  CYCLE<\/code>  clause. <\/p>\n
  The use of the ORDER BY<\/code> clause can let you build groupings. Create a sequence groups of ten:<\/p>\n
CREATE SEQUENCE Ten_Seq\n AS INTEGER\n START WITH 1\n INCREMENT BY 1\n MINVALUE 1\n MAXVALUE 10\n CYCLE; \n<\/pre>\n
 Now use it in a query that breaks the students into groups of ten <\/p>\n
 SELECT student_id, student_name,\n       (NEXT VALUE FOR Ten_Seq OVER(ORDER BY student_id))\n       AS student_grouping\n  FROM Students;\n<\/pre>\n
  Converting the sequence into, say, a four digit invoice serial number is pretty simple. It is important to  use VARCHAR(n).<\/code>  <\/p>\n
  SELECT ‘IN:’ + RIGHT (‘0000’ + CAST (NEXT VALUE FOR Invoice_Seq AS VARCHAR(5)), 4);<\/p>\n
  But a fun example is that of using the same sequence in more than one table. Let’s go back the original  service ticket example. When you come into the shop, you pull a ticket.   <\/p>\n
\t\tCREATE SEQUENCE Service_Ticket_Seq\n AS INTEGER\n START WITH 1\n INCREMENT BY 1\n MINVALUE 1\n MAXVALUE 100\n CYCLE; \n \n<\/pre>\n
 We have two departments in this delicatessen, meats and fish. If you have been to  a Kosher market, you know that these two areas are kept separate. A ticket can be used in only one department.  <\/p>\n
 CREATE TABLE Meats\n(ticket_seq INTEGER NOT NULL PRIMARY KEY,\n meat_type VARCHAR(15) NOT NULL);\n \nCREATE TABLE Fish\n(ticket_seq INTEGER NOT NULL PRIMARY KEY,\n fish_type VARCHAR(15) NOT NULL);\n \nCREATE PROCEDURE Ticket_Service\n(@in_market_code CHAR(1),\n @in_product_name VARCHAR(15))\nAS\nBEGIN \nDECLARE @local_ticket_seq INTEGER;\nSET @local_ticket_seq = NEXT VALUE FOR Service_Ticket_Seq; \nIF @in_market_code = 'M'\nINSERT INTO Meats\nVALUES (@local_ticket_seq, @in_product_name);\nELSE IF @in_market_code = 'F'\n   INSERT INTO Fish\n   VALUES (@local_ticket_seq, @in_product_name);\nEND; \n<\/pre>\n
 Now, let’ call the procedure a few times:  <\/p>\n
EXEC Ticket_Service 'M', 'Brisket';\nEXEC Ticket_Service 'F', 'Lox';\nEXEC Ticket_Service 'M', 'Chicken';\nEXEC Ticket_Service 'M', 'Brisket';\nEXEC Ticket_Service 'F', 'Sturgeon'; \nEXEC Ticket_Service 'F', 'Haddock';\n<\/pre>\n
  And now let’s see how this works.  <\/p>\n
 SELECT * FROM Meats;<\/pre>\n\n\n\n\n\n
\n
 \t\t1<\/p>\n<\/td>\n
\n
 \t\tBrisket<\/p>\n<\/td>\n<\/tr>\n
\n
 \t\t3<\/p>\n<\/td>\n
\n
 \t\tChicken<\/p>\n<\/td>\n<\/tr>\n
\n
 \t\t4<\/p>\n<\/td>\n
\n
 \t\tBrisket<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
 SELECT * FROM Fish;<\/pre>\n\n\n\n\n\n
\n
 \t\t2<\/p>\n<\/td>\n
\n
 \t\tLox<\/p>\n<\/td>\n<\/tr>\n
\n
 \t\t5<\/p>\n<\/td>\n
\n
 \t\tSturgeon<\/p>\n<\/td>\n<\/tr>\n
\n
 \t\t6<\/p>\n<\/td>\n
\n
 \t\tHaddock<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
  If I UNION<\/code> the two tables, I get a complete sequence and I am sure their intersection is empty. Doing this  without a SEQUENCE<\/code> is a lot harder. But I did resort to if-then-else flow control because I have two different tables.  UGH!<\/p>\n
I can put a SEQUENCE<\/code> in the DEFAULT<\/code> clause of the DDL for table:<\/p>\n
CREATE TABLE Service_Tickets\n(ticket_nbr INTEGER DEFAULT NEXT VALUE FOR Service_Ticket_Seq,\n department_code CHAR(1) NOT NULL\n   CHECK (department_code IN ('M', 'F'))); \n \n<\/pre>\n
 Now play with this code.  <\/p>\n
 \nINSERT INTO Service_Tickets (department_code)\nVALUES ('M');\n \nSELECT * FROM Service_Tickets;\n<\/pre>\n
  That is cool! But do not stop here. Let’s re-do the Meats and Fish tables with this feature, because  we can then do inserts without the need for a procedure and still be sure of an  empty intersection: <\/p>\n
CREATE TABLE Meats\n(ticket_seq INTEGER DEFAULT NEXT VALUE FOR Service_Ticket_Seq \n       PRIMARY KEY,\n meat_type VARCHAR(15) NOT NULL);\n \nCREATE TABLE Fish\n(ticket_seq INTEGER DEFAULT NEXT VALUE FOR Service_Ticket_Seq \n       PRIMARY KEY,\n fish_type VARCHAR(15) NOT NULL);\n<\/pre>\n
  Now, to get you started,  try these statements. I dropped the kosher theme \ud83d\ude42<\/p>\n
INSERT INTO Meats (meat_type) VALUES ('pig');\nINSERT INTO Fish (fish_type) VALUES ('squid');\nSELECT * FROM Meats;\nSELECT * FROM Fish;\n<\/pre>\n
  There are no guarantees as to how the sequence numbers will be assigned; it is pretty much first-come,  first-served in the system. Did you notice that the sequences are the PRIMARY KEY<\/code>? <\/p>\n
INSERT INTO Meats VALUES (8, 'Cat');<\/pre>\n
will give us:<\/p>\n
Msg 2627, Level 14, State 1, Line 1\nViolation of PRIMARY KEY constraint 'PK__Meats__467BFD06C5684404'. Cannot insert duplicate key in object 'dbo.Meats'. The duplicate key value is (8).\nThe statement has been terminated.\n<\/pre>\n
And this is all declarative code! <\/p>\n<\/p><\/div>\n","protected":false},"excerpt":{"rendered":"
The SEQUENCE statement  introduced in SQL Server 2012 brings the ANSI SQL 2003 standard method of generating IDs. This is a great relief to database professionals as it solves some problems what are awkward to solve with the IDENTITY property. Joe Celko explains the basics of using a SEQUENCE…<\/p>\n","protected":false},"author":96214,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143525],"tags":[4242,4168,4149,4150,5134,4151],"coauthors":[6781],"class_list":["post-1757","post","type-post","status-publish","format-standard","hentry","category-learn","tag-basics","tag-database","tag-learn-sql-server","tag-sql","tag-sql-prompt","tag-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1757","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/96214"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1757"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1757\/revisions"}],"predecessor-version":[{"id":40997,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1757\/revisions\/40997"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1757"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1757"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1757"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1757"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}