Hello again.
I know what you've been thinking.
What about sequences and what is a sequence in a sequel server?
Well, a sequence is an object in its culture and it's used to generate a number sequence.
So this can be really useful when we need to create a unique number to act as a primary key.
So how can we create a sequence?
What do you think the great sequence command seems pretty likely, so, yeah, we can use that to create
sequences.
There are just a few properties in this statement, so we'll have a look at each property one by one
data type built in integer type, tiny and small, end and big end decimal, etc. or user defined integer
time.
And the default is big int.
Start with.
The start with value is nothing but the first value that is going to be returned by the sequence object.
So it's not really enough in her.
Increment by.
The increment by value is nothing but the value to increment or decrement by the sequence object for
each row.
So if you specify a negative value, then the value is going to be decrement it.
Menville, what do you think it specifies the value for the sequence object?
No men value it specifies that there is no minimum value specified for the given sequence object.
Max value, maximum value for the sequence object.
And no max value, it means that there's absolutely no maximum value specified for that sequence object.
Cycle, it specifies reset sequence object when the sequence object reaches the maximum or minimum value.
Kash.
The cash sequence values for performance default value is cash.
So why don't we jump over to SSME and create a sequence together?
The name of our sequence will be sequence object.
The start with an increment by properties are enough for a default and simple sequence, so the starting
point is number one and we will increase one by one.
And we can execute it to create the sequence of these sequences are listed in the sequences folder,
under the programmability folder in the database, and you should see the sequence object under the
sequences folder and that's it.
So we've created our sequence.
Now, there are a few statements to get the values from the sequence.
And the next value is one of them.
Select next value for sequence commands that are used to get the next value of the sequences.
So let's get the next value of the sequence with a query.
The next value statement is used to reach the next value, the sequence like an iterator.
So if we execute the query, we will get the number one, which is the starting point of the sequence,
and whenever we execute the query, this number will increase.
Now, how can we get the current value?
Select current value from system sequences command, and we can use them to get the current value of
the sequence.
The name of the sequence should also be specified as a condition.
So let's get the current value of the query.
Now, it's possible that we can retrieve data from a sequence like a table.
This select statement will retrieve all data from the sequence object and the system sequences.
Execute the query and here is the data.
And the name of the sequence is here.
And the minimum value and the maximum value are also in the list.
Now, he didn't define the men and max values for the sequence of day, so they are set to default values.
And here it is, the current value of the sequence is seven.
So now let's get just the current value from the sequence executed and here is the number seven, which
is the current value of the sequence object.
So sequences can be reset to start point.
The altar sequence command is used to reset the sequence, and we should also specify the name of the
sequence that we want to reset its value to and the start point.
Now, I hope you remember the current value of the sequence object with seven.
Now, we may need to reset this value, let's say, to start back at number one.
So here's our query, execute it to reset the value of the sequence.
Now, let's get the current value of the sequence object, we should see the number one Tyrepac.
The sequences can be used in an insert statement, what next value for sequence commands will increase
the sequence value on every single execute a query.
It acts like a primary key.
So why don't we understand this with an example so as you may recall.
We have created a table named departments and transferred it to human resources as schema, and the
previous part Departments Table has two columns, idea and department name.
So now let's insert new rows into this table with a sequence.
Next value sequence will be number one because we have already reset sequence object.
The department name will be just a letter as.
Execute the query to insert the row.
And here it is next, value has been inserted as I.D. number.
So when we insert a few more rows, next value will be number two for the PR department.
And number three for the K department.
So let's execute and display the data at the table.
And here they are, sequence value is increasing in order.
Now, the decrement sequence is a sequence which will decrease by its incremented value, so if the
increment value is negative, it will be a decrements sequence.
Why don't we have a look with an example, so in this example, the name of our sequence will be decs
sequence.
Now as an integer, it means that the sequence will be integer.
So the deck sequence will start at 100 and decrease by one.
Because incremental value is minus one.
So it's executed to create the sequence.
Now, let's get the next value of it with select next value statement.
And what do you know, it decreases one by one.
Of course, it's possible that we can set the men and max value for a sequence.
We will not be able to see other values out of this range, so let's go ahead and create a new sequence.
And we'll call it Min Max sequence, it'll start at one hundred and increment.
Value is 20 men, value is 100, max value is 200.
Or execute, executed to save.
Let's get the next valley of them in max sequence sequence.
So the first value of the sequence is one hundred, because it is the start point.
Now, what will happen if we try to exceed the max value?
We get an error, which is about min max sequence has reached its min max value, restart the sequence
object to allow new values to be generated.
Now the cycle property will solve this problem and get the sequence to make a new start from the start
point.
So let's alter the min max sequence sequence and the cycle property.
And executed.
Great, so now when the next value exceeds the max value, the sequence will get a new start from the
starting point and that way we won't get any errors.
Now, what about dropping the sequence?
You guessed it, the drop command is used to drop or delete a sequence.
We've now created three sequences, so now let's drop them.
Let's drop max sequence.
Execute and refresh a folder, bubby.
Drop dead sequence and execute and refresh the folder.
Bubby.
So we're going to learn user management in the following section.
I'll see you there.