1 00:00:00,890 --> 00:00:01,950 Hello again. 2 00:00:02,630 --> 00:00:03,910 I know what you've been thinking. 3 00:00:04,840 --> 00:00:09,970 What about sequences and what is a sequence in a sequel server? 4 00:00:10,540 --> 00:00:16,770 Well, a sequence is an object in its culture and it's used to generate a number sequence. 5 00:00:17,140 --> 00:00:23,050 So this can be really useful when we need to create a unique number to act as a primary key. 6 00:00:23,440 --> 00:00:25,660 So how can we create a sequence? 7 00:00:27,420 --> 00:00:32,910 What do you think the great sequence command seems pretty likely, so, yeah, we can use that to create 8 00:00:32,910 --> 00:00:33,490 sequences. 9 00:00:34,200 --> 00:00:41,670 There are just a few properties in this statement, so we'll have a look at each property one by one 10 00:00:42,390 --> 00:00:51,920 data type built in integer type, tiny and small, end and big end decimal, etc. or user defined integer 11 00:00:51,930 --> 00:00:52,230 time. 12 00:00:52,980 --> 00:00:54,780 And the default is big int. 13 00:00:56,220 --> 00:00:57,030 Start with. 14 00:00:58,130 --> 00:01:06,080 The start with value is nothing but the first value that is going to be returned by the sequence object. 15 00:01:07,310 --> 00:01:08,930 So it's not really enough in her. 16 00:01:11,220 --> 00:01:12,180 Increment by. 17 00:01:13,590 --> 00:01:22,830 The increment by value is nothing but the value to increment or decrement by the sequence object for 18 00:01:22,830 --> 00:01:23,730 each row. 19 00:01:24,240 --> 00:01:28,620 So if you specify a negative value, then the value is going to be decrement it. 20 00:01:29,550 --> 00:01:34,860 Menville, what do you think it specifies the value for the sequence object? 21 00:01:36,000 --> 00:01:43,320 No men value it specifies that there is no minimum value specified for the given sequence object. 22 00:01:44,520 --> 00:01:48,510 Max value, maximum value for the sequence object. 23 00:01:49,420 --> 00:01:55,600 And no max value, it means that there's absolutely no maximum value specified for that sequence object. 24 00:01:56,680 --> 00:02:04,000 Cycle, it specifies reset sequence object when the sequence object reaches the maximum or minimum value. 25 00:02:04,930 --> 00:02:05,500 Kash. 26 00:02:06,860 --> 00:02:11,780 The cash sequence values for performance default value is cash. 27 00:02:13,090 --> 00:02:16,990 So why don't we jump over to SSME and create a sequence together? 28 00:02:19,450 --> 00:02:22,420 The name of our sequence will be sequence object. 29 00:02:23,900 --> 00:02:32,060 The start with an increment by properties are enough for a default and simple sequence, so the starting 30 00:02:32,060 --> 00:02:35,990 point is number one and we will increase one by one. 31 00:02:37,030 --> 00:02:43,630 And we can execute it to create the sequence of these sequences are listed in the sequences folder, 32 00:02:43,630 --> 00:02:49,960 under the programmability folder in the database, and you should see the sequence object under the 33 00:02:49,960 --> 00:02:52,620 sequences folder and that's it. 34 00:02:53,740 --> 00:02:55,420 So we've created our sequence. 35 00:02:56,360 --> 00:03:01,010 Now, there are a few statements to get the values from the sequence. 36 00:03:02,100 --> 00:03:04,830 And the next value is one of them. 37 00:03:06,840 --> 00:03:13,410 Select next value for sequence commands that are used to get the next value of the sequences. 38 00:03:14,570 --> 00:03:16,880 So let's get the next value of the sequence with a query. 39 00:03:18,410 --> 00:03:23,690 The next value statement is used to reach the next value, the sequence like an iterator. 40 00:03:24,730 --> 00:03:31,080 So if we execute the query, we will get the number one, which is the starting point of the sequence, 41 00:03:32,230 --> 00:03:36,280 and whenever we execute the query, this number will increase. 42 00:03:38,590 --> 00:03:40,360 Now, how can we get the current value? 43 00:03:41,880 --> 00:03:48,330 Select current value from system sequences command, and we can use them to get the current value of 44 00:03:48,330 --> 00:03:48,960 the sequence. 45 00:03:50,310 --> 00:03:54,210 The name of the sequence should also be specified as a condition. 46 00:03:55,580 --> 00:03:57,530 So let's get the current value of the query. 47 00:03:58,310 --> 00:04:02,000 Now, it's possible that we can retrieve data from a sequence like a table. 48 00:04:02,900 --> 00:04:09,410 This select statement will retrieve all data from the sequence object and the system sequences. 49 00:04:11,240 --> 00:04:13,700 Execute the query and here is the data. 50 00:04:14,960 --> 00:04:16,490 And the name of the sequence is here. 51 00:04:18,100 --> 00:04:22,090 And the minimum value and the maximum value are also in the list. 52 00:04:27,510 --> 00:04:33,390 Now, he didn't define the men and max values for the sequence of day, so they are set to default values. 53 00:04:38,550 --> 00:04:42,960 And here it is, the current value of the sequence is seven. 54 00:04:44,940 --> 00:04:53,070 So now let's get just the current value from the sequence executed and here is the number seven, which 55 00:04:53,070 --> 00:04:55,680 is the current value of the sequence object. 56 00:04:57,020 --> 00:05:00,620 So sequences can be reset to start point. 57 00:05:01,710 --> 00:05:07,710 The altar sequence command is used to reset the sequence, and we should also specify the name of the 58 00:05:07,710 --> 00:05:12,240 sequence that we want to reset its value to and the start point. 59 00:05:14,360 --> 00:05:18,200 Now, I hope you remember the current value of the sequence object with seven. 60 00:05:19,190 --> 00:05:23,510 Now, we may need to reset this value, let's say, to start back at number one. 61 00:05:25,390 --> 00:05:29,500 So here's our query, execute it to reset the value of the sequence. 62 00:05:31,070 --> 00:05:38,930 Now, let's get the current value of the sequence object, we should see the number one Tyrepac. 63 00:05:41,320 --> 00:05:51,070 The sequences can be used in an insert statement, what next value for sequence commands will increase 64 00:05:51,070 --> 00:05:53,950 the sequence value on every single execute a query. 65 00:05:54,730 --> 00:05:56,350 It acts like a primary key. 66 00:05:57,570 --> 00:06:02,250 So why don't we understand this with an example so as you may recall. 67 00:06:03,580 --> 00:06:09,880 We have created a table named departments and transferred it to human resources as schema, and the 68 00:06:09,880 --> 00:06:15,310 previous part Departments Table has two columns, idea and department name. 69 00:06:16,150 --> 00:06:20,290 So now let's insert new rows into this table with a sequence. 70 00:06:21,580 --> 00:06:27,190 Next value sequence will be number one because we have already reset sequence object. 71 00:06:28,450 --> 00:06:30,730 The department name will be just a letter as. 72 00:06:32,480 --> 00:06:34,490 Execute the query to insert the row. 73 00:06:35,450 --> 00:06:40,880 And here it is next, value has been inserted as I.D. number. 74 00:06:42,530 --> 00:06:47,810 So when we insert a few more rows, next value will be number two for the PR department. 75 00:06:47,840 --> 00:06:50,440 And number three for the K department. 76 00:06:51,110 --> 00:06:54,190 So let's execute and display the data at the table. 77 00:06:57,530 --> 00:07:01,670 And here they are, sequence value is increasing in order. 78 00:07:04,060 --> 00:07:12,670 Now, the decrement sequence is a sequence which will decrease by its incremented value, so if the 79 00:07:12,670 --> 00:07:16,900 increment value is negative, it will be a decrements sequence. 80 00:07:18,020 --> 00:07:24,050 Why don't we have a look with an example, so in this example, the name of our sequence will be decs 81 00:07:24,050 --> 00:07:24,700 sequence. 82 00:07:25,130 --> 00:07:29,480 Now as an integer, it means that the sequence will be integer. 83 00:07:30,110 --> 00:07:34,070 So the deck sequence will start at 100 and decrease by one. 84 00:07:35,590 --> 00:07:38,290 Because incremental value is minus one. 85 00:07:40,100 --> 00:07:42,050 So it's executed to create the sequence. 86 00:07:43,580 --> 00:07:47,420 Now, let's get the next value of it with select next value statement. 87 00:07:50,440 --> 00:07:52,720 And what do you know, it decreases one by one. 88 00:07:55,820 --> 00:08:00,100 Of course, it's possible that we can set the men and max value for a sequence. 89 00:08:01,070 --> 00:08:07,610 We will not be able to see other values out of this range, so let's go ahead and create a new sequence. 90 00:08:08,780 --> 00:08:14,240 And we'll call it Min Max sequence, it'll start at one hundred and increment. 91 00:08:15,190 --> 00:08:21,010 Value is 20 men, value is 100, max value is 200. 92 00:08:22,140 --> 00:08:23,460 Or execute, executed to save. 93 00:08:26,690 --> 00:08:29,730 Let's get the next valley of them in max sequence sequence. 94 00:08:34,170 --> 00:08:38,580 So the first value of the sequence is one hundred, because it is the start point. 95 00:08:39,820 --> 00:08:43,150 Now, what will happen if we try to exceed the max value? 96 00:08:45,700 --> 00:08:53,130 We get an error, which is about min max sequence has reached its min max value, restart the sequence 97 00:08:53,140 --> 00:08:56,050 object to allow new values to be generated. 98 00:08:57,100 --> 00:09:06,160 Now the cycle property will solve this problem and get the sequence to make a new start from the start 99 00:09:06,160 --> 00:09:06,490 point. 100 00:09:06,910 --> 00:09:11,920 So let's alter the min max sequence sequence and the cycle property. 101 00:09:12,900 --> 00:09:13,890 And executed. 102 00:09:17,160 --> 00:09:24,810 Great, so now when the next value exceeds the max value, the sequence will get a new start from the 103 00:09:24,810 --> 00:09:28,620 starting point and that way we won't get any errors. 104 00:09:31,400 --> 00:09:33,380 Now, what about dropping the sequence? 105 00:09:35,320 --> 00:09:40,660 You guessed it, the drop command is used to drop or delete a sequence. 106 00:09:42,360 --> 00:09:46,230 We've now created three sequences, so now let's drop them. 107 00:09:49,470 --> 00:09:51,480 Let's drop max sequence. 108 00:09:55,370 --> 00:09:58,790 Execute and refresh a folder, bubby. 109 00:10:01,440 --> 00:10:06,120 Drop dead sequence and execute and refresh the folder. 110 00:10:07,420 --> 00:10:08,470 Bubby. 111 00:10:10,270 --> 00:10:13,930 So we're going to learn user management in the following section. 112 00:10:14,650 --> 00:10:15,580 I'll see you there.