Microsoft SQL Server - Lesson 24: Sequences

	Sequences

Introduction to Sequences

Overview

A sequence is a series of numbers that are continually generated and assigned to a column of a table. This works like an identity column. The difference is that, if you need an identity, you must create it in a column of a table and if you need the same type of identity on a column of another table, you must create the identity in the column of the other table. On the other hand, a sequence is a programmatic object, like a function, that you create at the database level and you can apply that sequence to any table you want.

Practical Learning: Introducing Sequances

Start Microsoft SQL Server connect
Right-click the name of the server and click New Query

Visually Creating a Sequence

To visually create a sequence, in the Object Explorer, expand the desired database and the Programmability nodes. Right-click Sequences and click New Sequence...

Visually Creating a Sequence

This would present the New Sequence dialog box with some default (basic) values:

New Sequence

We will review all the options of this dialog box in the next sections.

Programmatically Creating a Sequence

The Transact-SQL syntax to create a sequence is:

CREATE SEQUENCE [schema_name . ] sequence_name
    [ AS [ built_in_integer_type | user-defined_integer_type ] ]
    [ START WITH <constant> ]
    [ INCREMENT BY  <constant> ]
    [ { MINVALUE [  <constant> ] } | { NO MINVALUE } ]
    [ { MAXVALUE [  <constant> ] } | { NO MAXVALUE } ]
    [ CYCLE | { NO CYCLE } ]
    [ { CACHE [  <constant> ] } | { NO CACHE } ]
    [ ; ]

You start with the CREATE SEQUENCE expression.

Characteristics of a Sequence

A sequence shares many characteristics with an identity column but adds some others. Therefore, the characteristics of a sequence are:

Name: A sequence is a database object. As such, it must have a name
Schema: Again, a sequence is an object created in a database. Therefore, it must belong to a schema. If you have (created) a schema, you can use it and precede the name of the sequence with it. As done for all objects of a database, if you don't specify a schema, dbo would be used as default
Data Type: As seen for an identity, a sequence is a series of natural numeric values. The default is a bigint (large integer) type. Otherwise, you can make it another valid type such as tinyint, smallint, or int. Alternatively, you can apply a float-point type (decimal or numeric). In this case, you should specify the precision value.
In the Data Type combo box, select int
Startint Point: When creating a sequence, you should indicate where the series should start. You have many options. You can indicate that you want the series to start at 1 and continue up. On the other hand:
- If you create a sequence of type tinyint, you can set the starting value at 0. This may indicate that you want the value to count from 0 and up. Otherwise, you can specify that you want the starting value to be 255 (not more than that). This may indicate that you want the series to count down. The starting point is optional. If you don't specify it, the starting point is 0
- If you create a sequence of type smallint, you can set the starting value between -32,768 (not less than that) and 32,767 (not more than that). You would later indicate whether you want the series to go up or down. If you don't specify a starting point, the default start value is -32768
- If you create a sequence of type int, you can set the starting value between -2,147,483,648 (not less than that) and 2,147,483,647 (not more than that). If you don't specify the starting value, the default to use is fixed at -2,147,483,648
- If you create a sequence of type bigint, you can set the starting value between -9,223,372,036,854,775,808 (not less than that) and 9,223,372,036,854,775,807 (not more than that). If you don't specify the starting value, the default startup is at -9,223,372,036,854,775,808
Increment Value: You should also indicate by what value the number would increment or decrement. Once again, you have various options. If you want the increment to proceed up, the incrementing value must be positive. If you want to decrement, apply a negative value. The increment value is optional. If you don't specify it, by default, the series wouold increment by 1
Boundaries: Unlike an identity, a sequence can have boundaries: one value by which the sequence cannot go under, and one value that the sequence cannot surpass. These are the minimum and the maximum values respectively. They follow this logic:
- Minimum Value: If you don't specify this value, the minimum value is the starting point value. Otherwise:
  - If you create a sequence of type tinyint, the minimum value can be 0
  - If you create a sequence of type smallint, the minimum value can be -32,768
  - If you create a sequence of type int, the minimum value can be -2,147,483,648
  - If you create a sequence of type bigint, the minimum value can be -9,223,372,036,854,775,808
- Maximum Value:
  - If you create a sequence of type tinyint, if you don't specify this value, the default maximum value is 255 or you can specify a value between the starting point and 255
  - If you create a sequence of type smallint, if you don't specify this value, the default maximum value is 32,767 or you can specify a value between the starting point and 32,767
  - If you create a sequence of type int, if you don't specify this value, the maximum value can be -2,147,483,648
  - If you create a sequence of type bigint, if you don't specify this value, the maximum value is 9,223,372,036,854,775,807 or you can specify a value between the starting point and 9,223,372,036,854,775,807
Cycle: This option asks the database engine to restart the series if it reaches the maximum value. In this case, the series would restart from the minimum value (not from the starting value) following the previous described logic
Cache: In some cases, generating a sequence can utilise many resources on the computer. To assist the database engine, you can ask it to use the cache memory to keep track of the numbers in the sequence

Practical Learning: Creating Sequences

In the Object Explorer, under University6 (the University6 database was created in the previous lesson), expand Programmability
Right-click Sequences and click New Sequence...
In the Sequence Name, type SeqSchedules
On the right side of the Sequence Schema text box, click the browse button
In the Enter the Object Names text box, type Academics
Click Check Names and notice that [Academics] has been selected
Click OK
In the Data Type combo box, select int
In the Start Value text box, type 10101
In the Increment By text box, type 1
Click OK
Click inside the Query Editor and press Ctrl + A

To programmatically create a sequence and a table that would use it, type the following code:

USE University6;
GO

CREATE SEQUENCE Academics.SeqStudents
    AS Int
    START WITH   1
    INCREMENT BY 1;
GO

To execute, press F5

Using a Sequence

After creating a sequence, it becomes an object you can use in any new table. Because a sequence generates (unique incremental) values that a column would use, when creating the field on a table, specify its data type as the same or compatible type that the sequence is using. Here is an example:

CREATE TABLE Inventory.StoreItems
(
    ItemNumber int,
    ItemName nvarchar(60),
    UnitPrice money
);
GO

A sequence is used during data entry. When specifying the value of its column, type a formula as:

NEXT VALUE FOR [schema_name . ] sequence_name

The database engine would then find the next number in the sequence and assign it to the column. Here are examples:

CREATE SCHEMA Inventory;
GO
CREATE SEQUENCE Inventory.ItemsCodes
    AS int
	START WITH 10001
	INCREMENT BY 1;
GO
CREATE TABLE Inventory.StoreItems
(
	ItemNumber int,
	ItemName nvarchar(60),
	UnitPrice money
);
GO
INSERT INTO Inventory.StoreItems
VALUES(NEXT VALUE FOR Inventory.ItemsCodes, N'Short Sleeve Shirt', 34.95),
      (NEXT VALUE FOR Inventory.ItemsCodes, N'Tweed Jacket', 155.00),
      (NEXT VALUE FOR Inventory.ItemsCodes, N'Evaded Mini-Skirt', 72.45),
      (NEXT VALUE FOR Inventory.ItemsCodes, N'Lombardi Men''s Shoes', 79.95);
GO

This would produce:

ItemNumber	ItemName	UnitPrice
10001	Short Sleeve Shirt	34.95
10002	Tweed Jacket	155.00
10003	Evaded Mini-Skirt	72.45
10004	Lombardi Men's Shoes	79.95

Practical Learning: Using Sequences

Click inside the Query Editor and press Ctrl + A

Type the following:

USE University6;
GO

INSERT INTO Academics.UndergraduateSchedules(UndergraduateScheduleID, SemesterID, StartDate, EndDate, TeacherNumber, CourseCode, CourseDeliveryTypeCode, LocationCode)
VALUES(NEXT VALUE FOR Academics.SeqSchedules, 101, N'20120109', N'20120205', N'820384', N'LBRS 100', N'L', N'ONLNE'),
      (NEXT VALUE FOR Academics.SeqSchedules, 101, N'20120109', N'20120205', N'979384', N'LBRS 100', N'L', N'ONLNE');
GO
INSERT INTO Academics.UndergraduateSchedules(UndergraduateScheduleID, SemesterID, StartDate, EndDate, StartTime, EndTime, Weekdays, TeacherNumber, CourseCode, CourseDeliveryTypeCode, LocationCode, RoomNumber)
VALUES(NEXT VALUE FOR Academics.SeqSchedules, 101, N'20120109', N'20120318', N'08:15', N'10:10', N'M-W', N'293804', N'WRTG 101', N'F', N'LATAC', N'204'),
      (NEXT VALUE FOR Academics.SeqSchedules, 101, N'20120109', N'20120318', N'10:25', N'12:20', N'M-W', N'293804', N'WRTG 101', N'F', N'LATAC', N'104'),
      (NEXT VALUE FOR Academics.SeqSchedules, 101, N'20120109', N'20120226', N'08:15', N'10:10', N'M-W-F', N'293804', N'WRTG 101', N'F', N'LATAC', N'102'),
      (NEXT VALUE FOR Academics.SeqSchedules, 101, N'20120109', N'20120226', N'10:25', N'12:20', N'M-W-F', N'293804', N'WRTG 101', N'F', N'LATAC', N'102'),
      (NEXT VALUE FOR Academics.SeqSchedules, 101, N'20120109', N'20120318', N'10:25', N'12:20', N'M-W', N'520203', N'MATH 106', N'F', N'LATAC', N'102');
GO

To execute, press F5
Click inside the Query Editor and press Ctrl + A

To see a list of schedules, type the following code:

SELECT schd.*
FROM Academics.UndergraduateSchedules schd;
GO

To execute the statement, press F5

Details on Sequences

Sharing a Sequence

A sequence can be shared by many tables. This means that, after creating a sequence, you can apply it on any table that needs that series of numbers. When using a sequence from one table to another, if you use the NEXT VALUE FOR routine, the series would continue from where it left up. This is not an anomaly. It is by design, so that various tables can share the same sequence.

Resetting a Sequence

Resetting a sequence consists of restarting it from a certain point. To do this, use the following formula:

ALTER SEQUENCE [schema_name. ] sequence_name
    [ RESTART [ WITH constant ] ]
    [ INCREMENT BY constant ]
    [ { MINVALUE constant } | { NO MINVALUE } ]
    [ { MAXVALUE constant } | { NO MAXVALUE } ]
    [ CYCLE | { NO CYCLE } ]
    [ { CACHE [ constant ] } | { NO CACHE } ]
    [ ; ]

A sequence is a programmatic object, like a function, that you create at the database level and you can apply that sequence to any table you want.

Practical Learning: Resetting a Sequence

Click inside the Query Editor and press Ctrl + A

Type the following:

USE University6;
GO

ALTER SEQUENCE Academics.SeqSchedules
RESTART WITH 10201
INCREMENT BY 1;
GO

INSERT INTO Academics.UndergraduateSchedules(UndergraduateScheduleID, SemesterID, StartDate, EndDate, StartTime, EndTime, Weekdays, TeacherNumber, CourseCode, CourseDeliveryTypeCode, LocationCode, RoomNumber)
VALUES(NEXT VALUE FOR Academics.SeqSchedules, 102, N'20120514', N'20120819', N'08:15', N'10:10', N'M-W', N'293804', N'WRTG 101', N'F', N'LATAC', N'204');
GO
INSERT INTO Academics.UndergraduateSchedules(UndergraduateScheduleID, SemesterID, StartDate, EndDate, StartTime, EndTime, Weekdays, TeacherNumber, CourseCode, CourseDeliveryTypeCode, LocationCode, RoomNumber)
VALUES(NEXT VALUE FOR Academics.SeqSchedules, 102, N'20120514', N'20120819', N'10:25', N'12:20', N'M-W', N'603925', N'EDPD 100', N'F', N'HMNSS', N'101');
GO
INSERT INTO Academics.UndergraduateSchedules(UndergraduateScheduleID, SemesterID, StartDate, EndDate, StartTime, EndTime, Weekdays, TeacherNumber, CourseCode, CourseDeliveryTypeCode, LocationCode, RoomNumber)
VALUES(NEXT VALUE FOR Academics.SeqSchedules, 102, N'20120514', N'20120819', N'08:15', N'10:10', N'T-H', N'520203', N'MATH 106', N'F', N'LATAC', N'104');
GO
INSERT INTO Academics.UndergraduateSchedules(UndergraduateScheduleID, SemesterID, StartDate, EndDate, TeacherNumber, CourseCode, CourseDeliveryTypeCode, LocationCode)
VALUES(NEXT VALUE FOR Academics.SeqSchedules, 102, N'20120514', N'20120205', N'820384', N'LBRS 100', N'L', N'ONLNE');
GO
INSERT INTO Academics.UndergraduateSchedules(UndergraduateScheduleID, SemesterID, StartDate, EndDate, StartTime, EndTime, Weekdays, TeacherNumber, CourseCode, CourseDeliveryTypeCode, LocationCode, RoomNumber)
VALUES(NEXT VALUE FOR Academics.SeqSchedules, 102, N'20120514', N'20120819', N'08:15', N'10:10', N'M-W', N'160205', N'CMSC 101', N'F', N'SEAMT', N'210');
GO
INSERT INTO Academics.UndergraduateSchedules(UndergraduateScheduleID, SemesterID, StartDate, EndDate, StartTime, EndTime, Weekdays, TeacherNumber, CourseCode, CourseDeliveryTypeCode, LocationCode, RoomNumber)
VALUES(NEXT VALUE FOR Academics.SeqSchedules, 102, N'20120514', N'20120819', N'13:25', N'15:20', N'M-W', N'297940', N'MATH 115', N'F', N'NAASB', N'102');
GO
INSERT INTO Academics.UndergraduateSchedules(UndergraduateScheduleID, SemesterID, StartDate, EndDate, StartTime, EndTime, Weekdays, TeacherNumber, CourseCode, CourseDeliveryTypeCode, LocationCode, RoomNumber)
VALUES(NEXT VALUE FOR Academics.SeqSchedules, 102, N'20120514', N'20120819', N'08:15', N'10:10', N'T-H', N'283029', N'HIST 140', N'F', N'HMNSS', N'103');
GO

ALTER SEQUENCE Academics.SeqSchedules
RESTART WITH 10301
INCREMENT BY 1;
GO

INSERT INTO Academics.UndergraduateSchedules(UndergraduateScheduleID, SemesterID, StartDate, EndDate, StartTime, EndTime, Weekdays, TeacherNumber, CourseCode, CourseDeliveryTypeCode, LocationCode, RoomNumber)
VALUES(NEXT VALUE FOR Academics.SeqSchedules, 103, N'20120920', N'20121014', N'08:15', N'10:10', N'M-W', N'293804', N'WRTG 101', N'F', N'LATAC', N'101');
GO
INSERT INTO Academics.UndergraduateSchedules(UndergraduateScheduleID, SemesterID, StartDate, EndDate, StartTime, EndTime, Weekdays, TeacherNumber, CourseCode, CourseDeliveryTypeCode, LocationCode, RoomNumber)
VALUES(NEXT VALUE FOR Academics.SeqSchedules, 103, N'20120920', N'20121014', N'10:25', N'12:20', N'M-W', N'640840', N'MATH 106', N'F', N'LATAC', N'106');
GO
INSERT INTO Academics.UndergraduateSchedules(UndergraduateScheduleID, SemesterID, StartDate, EndDate, StartTime, EndTime, Weekdays, TeacherNumber, CourseCode, CourseDeliveryTypeCode, LocationCode, RoomNumber)
VALUES(NEXT VALUE FOR Academics.SeqSchedules, 103, N'20120920', N'20121014', N'13:25', N'15:20', N'M-W', N'283029', N'HIST 140', N'F', N'HMNSS', N'103');
GO
INSERT INTO Academics.UndergraduateSchedules(UndergraduateScheduleID, SemesterID, StartDate, EndDate, StartTime, EndTime, Weekdays, TeacherNumber, CourseCode, CourseDeliveryTypeCode, LocationCode, RoomNumber)
VALUES(NEXT VALUE FOR Academics.SeqSchedules, 103, N'20120920', N'20121014', N'13:25', N'15:20', N'T-H', N'283029', N'HIST 140', N'F', N'HMNSS', N'103');
GO

ALTER SEQUENCE Academics.SeqSchedules
RESTART WITH 10401
INCREMENT BY 1;
GO

INSERT INTO Academics.UndergraduateSchedules(UndergraduateScheduleID, SemesterID, StartDate, EndDate, StartTime, EndTime, Weekdays, TeacherNumber, CourseCode, CourseDeliveryTypeCode, LocationCode, RoomNumber)
VALUES(NEXT VALUE FOR Academics.SeqSchedules, 104, N'20130107', N'20130217', N'08:15', N'10:10', N'M-W', N'283029', N'HIST 140', N'F', N'HMNSS', N'105');
GO
INSERT INTO Academics.UndergraduateSchedules(UndergraduateScheduleID, SemesterID, StartDate, EndDate, StartTime, EndTime, Weekdays, TeacherNumber, CourseCode, CourseDeliveryTypeCode, LocationCode, RoomNumber)
VALUES(NEXT VALUE FOR Academics.SeqSchedules, 104, N'20130107', N'20130217', N'15:35', N'17:30', N'M-W', N'249382', N'GVPS 140', N'F', N'HMNSS', N'107');
GO

To execute, press F5
Click inside the Query Editor and press Ctrl + A

To see a list of schedules, type the following code:

SELECT schd.UndergraduateScheduleID,
       schd.SemesterID, 
       schd.StartDate, 
       schd.EndDate, 
       schd.TeacherNumber, 
       schd.CourseCode, 
       schd.CourseDeliveryTypeCode, 
       schd.LocationCode
FROM Academics.UndergraduateSchedules schd;
GO

To execute the statement, press F5

Setting a Sequence as Default

So far, to specify the value of a column with sequence, we were calling NEXT VALUE FOR. If you know that you will keep caling a sequence to provide the values of a column, you can set that sequence as the default value of the column. If you do this, you can omit the column in the INSERT statement. Here is an example:

USE LambdaSquare1;
GO
CREATE TABLE Rentals.Registrations
(
	RegistrationID int not null
		DEFAULT (NEXT VALUE FOR Rentals.SeqRegistrations),
	RegistrationDate Date,
	EmployeeNumber int, -- Processed By
	TenantCode int, -- Processed For
	UnitNumber int not null,
	RentStartDate date,
	Notes nvarchar(max)
);
GO

After doing this, you can create the values of the column as done for an identity, by omiting the name of the column in the INSERT statement.

INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, MiddleName, LastName, BirthDate, Gender, EmailAddress, MajorID, MinorID, Username)
VALUES(N'88130480', N'Marie', N'Annette', N'Robinson', Administration.SetDateOfBirth(-6817), N'F', N'mrobinson@yahoo.com',    1021, 1004, N'mrobinson'),
      (N'24795711', N'Roger', N'Dermot',  N'Baker',    Administration.SetDateOfBirth(-6570), N'M', N'rbaker2020@hotmail.com', 1005, 1002, N'rbaker');
GO
INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, LastName, BirthDate, Gender, EmailAddress, MajorID, MinorID, Username)
VALUES(N'18073572', N'Patrick', N'Wisne', Administration.SetDateOfBirth(-11012), N'M', N'pwisdom@attmail.com', 1001, 1008, N'pwisne');
GO

Practical Learning: Setting a Sequence as Default

Click inside the the Query Editor and press Ctrl + A

Type the following:

USE University6;
GO

CREATE TABLE Academics.UndergraduateStudents
(
    StudentID int not null
	DEFAULT (NEXT VALUE FOR Academics.SeqStudents),
    StudentNumber nvarchar(8) not null,
    FirstName nvarchar(20),
    MiddleName nvarchar(20),
    LastName nvarchar(20),
    BirthDate date,
    Gender nvarchar(3) default N'N/A',
    MajorID int not null
);
GO

INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, MiddleName, LastName, BirthDate, Gender, MajorID)
VALUES(N'88130480', N'Marie', N'Annette', N'Robinson', Administration.SetDateOfBirth(-6817), N'F', 1021),
      (N'24795711', N'Roger', N'Dermot',  N'Baker',    Administration.SetDateOfBirth(-6570), N'M', 1005);
GO
INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, LastName, BirthDate, Gender, MajorID)
VALUES(N'18073572', N'Patrick', N'Wisne', Administration.SetDateOfBirth(-11012), N'M', 1001);
GO
INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, MiddleName, LastName, BirthDate, Gender, MajorID)
VALUES(N'22803048', N'Gary', N'Jonathan', N'Jones', Administration.SetDateOfBirth(-19926), N'M', 1019),
      (N'97394285', N'Jessica', N'Danielle', N'Weisburgh', Administration.SetDateOfBirth(-12081), N'F', 1009),
      (N'97596002', N'Laurent', N'Frank', N'Simonson', Administration.SetDateOfBirth(-17503), N'M', 1016),
      (N'94708257', N'Christopher', N'Sheldon', N'Dale', Administration.SetDateOfBirth(-6570),  N'M', 1006),
      (N'48009520', N'Diane', N'Kathy', N'Paglia', Administration.SetDateOfBirth(-13840), N'F', 1006),
      (N'13048039', N'Joseph', N'Christian', N'Riback', Administration.SetDateOfBirth(-7909),  N'M', 1011),
      (N'92270397', N'Patrick', N'Jonathan', N'Brzeniak', Administration.SetDateOfBirth(-17361), N'M', 1021);
GO
INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, LastName, BirthDate, Gender, MajorID)
VALUES(N'70840584', N'Tracy', N'Sikorowski', Administration.SetDateOfBirth(-11650), N'M', 1006);
GO

Right-click anywhere in the window and click Execute
Click inside the Query Editor and press Ctrl + A

To see a list of students, type the following:

SELECT Students.StudentID ID,
       Students.StudentNumber [Student #],
       Students.FirstName [First Name],
       Students.MiddleName [Middle Name],
       Students.LastName [Last Name],
       FORMAT(Students.BirthDate, N'D') [Birth Date],
       DATEDIFF(yyyy, BirthDate, SYSDATETIME()) Age,
       CASE Students.Gender
		WHEN N'M' THEN N'Male'
		WHEN N'F' THEN N'Female'
		ELSE N'Unknown'
       END Gender,
       MajorID Major
FROM Academics.UndergraduateStudents Students
ORDER BY Students.StudentID;
GO

To execute, press F5
Close Microsoft SQL Server
If asked whether you want to save the file, click No