1 00:01.640 --> 00:02.180 Okay. 2 00:02.180 --> 00:08.750 So today we have to start with the inline table valued functions. 3 00:08.930 --> 00:12.830 So actually this one is the alternative for you. 4 00:14.750 --> 00:21.980 We can say that alternative for you because one limitation in view is we cannot pass the parameters 5 00:21.980 --> 00:22.940 in the view. 6 00:22.970 --> 00:27.620 So that is why this inline table valued functions come into the picture. 7 00:27.710 --> 00:35.030 So in SQL server we can use this functions to achieve the functionality of parameterized parameterized 8 00:35.030 --> 00:35.840 view. 9 00:35.930 --> 00:41.000 These are like views but with ability to accept the parameters. 10 00:42.320 --> 00:44.810 In inline table valued function. 11 00:44.810 --> 00:48.380 The body of the function will have only a single Select statement. 12 00:49.370 --> 00:52.670 Over here we can write only a single Select statement. 13 00:52.670 --> 00:54.530 So if you see this example. 14 00:54.530 --> 00:55.910 Create a function. 15 00:57.140 --> 00:58.820 Create a function. 16 01:02.920 --> 01:07.360 that returns employee number, name, and salary by job. 17 01:07.390 --> 01:13.030 So over here we need to we need what will be our parameter. 18 01:13.030 --> 01:15.550 So our parameter will be a job right. 19 01:15.820 --> 01:18.100 Job will become our parameter. 20 01:21.040 --> 01:29.830 And we want to retrieve information like employee number name and salary and any of the job. 21 01:29.830 --> 01:32.560 So we have in the employee table. 22 01:42.850 --> 01:47.590 In the EMP table we have multiple jobs are there right. 23 01:47.590 --> 01:50.830 Like clerk salesman manager. 24 01:50.860 --> 01:52.810 So whenever any of these. 25 01:55.000 --> 01:58.140 Uh detail will be provided by the user. 26 01:58.140 --> 02:00.810 That data will be shown for this. 27 02:00.810 --> 02:04.620 Particular three columns will be shown for that particular job. 28 02:04.920 --> 02:08.640 So for that purpose we have to write like this create function. 29 02:08.730 --> 02:10.530 This will be function name. 30 02:10.800 --> 02:13.110 Then this will become our parameter. 31 02:13.140 --> 02:14.640 Job will become our. 32 02:16.710 --> 02:17.790 Parameter. 33 02:17.910 --> 02:20.550 Then we need to return something right in the function. 34 02:20.550 --> 02:23.970 We need to return something so it will return a table right. 35 02:24.000 --> 02:24.990 Table kind of thing. 36 02:24.990 --> 02:28.440 Because of three column and few rows it will return. 37 02:28.440 --> 02:31.830 So it will become one table as written. 38 02:31.830 --> 02:35.130 And in the return we have to mention select statement. 39 02:35.160 --> 02:43.260 Select what we want to select EMP number, name and salary from EMP table where job equal to at the 40 02:43.260 --> 02:43.860 rate job. 41 02:43.860 --> 02:51.780 So whatever the job will be entered by the particular user, the data will be shown for that only. 42 02:51.780 --> 02:52.500 So. 43 02:55.410 --> 02:58.480 If we want to drop the function, then we have to write like this. 44 02:58.480 --> 02:59.320 Drop function. 45 02:59.350 --> 03:00.460 Function name. 46 03:01.030 --> 03:04.000 Now let me create this function. 47 03:05.740 --> 03:10.510 Now see if we want to call the function. 48 03:16.870 --> 03:22.990 Then either we can write the select. 49 03:25.300 --> 03:31.630 Statement to call or in the join. 50 03:32.710 --> 03:37.510 Also we will able to write function. 51 03:40.210 --> 03:42.520 Here we do not need to write execute. 52 03:42.520 --> 03:45.310 So over here I haven't given any parameter. 53 03:45.310 --> 03:48.100 If you see over here I haven't given any parameter. 54 03:48.100 --> 03:50.380 So it will give me some error right. 55 03:51.280 --> 03:58.500 That parameters value will uh The parameters were not supplied for the function function name, and 56 03:58.500 --> 04:02.670 if I if I supplied like this. 57 04:02.670 --> 04:09.630 Select star from function name in in single brackets and close bracket, open bracket and close bracket. 58 04:09.660 --> 04:18.180 Then again it will be different error and if inefficient number of arguments were supplied for the procedure 59 04:18.180 --> 04:19.260 or function. 60 04:21.480 --> 04:23.820 Now I need to supply something, right? 61 04:23.850 --> 04:24.480 Whatever. 62 04:24.480 --> 04:26.430 The job is my parameter. 63 04:26.520 --> 04:28.950 So whatever the distinct jobs are there. 64 04:28.950 --> 04:31.170 So if I if you see over here. 65 04:33.480 --> 04:34.710 Distinct job. 66 04:34.710 --> 04:38.850 So whatever the distinct jobs are there I can supply. 67 04:38.880 --> 04:47.670 So if I supply like select function name then in bracket salesman. 68 04:47.670 --> 04:51.600 So this will give me the detail of salesman. 69 04:52.340 --> 04:52.940 Right. 70 04:52.940 --> 04:54.950 This will give me the detail of salesman. 71 04:54.950 --> 04:56.720 So if you see. 72 05:03.170 --> 05:07.190 Where job equal to. 73 05:11.030 --> 05:16.640 If I run both of these query then it will give me same number of rows, four rows in above query and 74 05:16.640 --> 05:18.320 four rows in below query. 75 05:18.470 --> 05:19.940 And again, one more thing. 76 05:19.940 --> 05:23.390 We have written only one single select statement over here, right? 77 05:24.020 --> 05:28.370 We cannot write multiple select statement in inline table valued function. 78 05:30.680 --> 05:31.610 Right. 79 05:35.600 --> 05:40.850 Now I can do the multiple tables also. 80 05:40.850 --> 05:47.900 So over here I have written the function create a function which displays employee ID, employee name, 81 05:47.900 --> 05:49.430 hire date and department name. 82 05:49.430 --> 05:49.700 Right. 83 05:49.730 --> 05:50.750 Department name. 84 05:50.780 --> 05:55.310 but department name column is available in the departments table. 85 05:55.340 --> 05:55.610 Right. 86 05:55.640 --> 05:56.570 Department table. 87 05:56.570 --> 05:57.410 So I. 88 05:57.710 --> 05:59.330 Everything will be same. 89 05:59.510 --> 06:04.010 Create function function name job as parameter returns table is written. 90 06:04.460 --> 06:07.370 Then select then. 91 06:07.370 --> 06:10.280 This is my simple join statement only. 92 06:10.490 --> 06:13.160 This is my simple join statement only. 93 06:13.160 --> 06:16.760 But now I want to specify a where clause right? 94 06:16.760 --> 06:24.350 So whichever the job we will mention that particular job related detail should be displayed. 95 06:31.970 --> 06:34.850 So let me create this function as well. 96 06:37.850 --> 06:44.840 Now if I mention over here function name and then clerk, then it will display only clerk data right. 97 06:44.840 --> 06:46.400 It will display only clerk data. 98 06:46.400 --> 06:48.680 In which department name also right. 99 06:50.700 --> 06:53.100 With department name also. 100 06:57.180 --> 06:58.440 Now. 101 07:01.290 --> 07:02.130 Function. 102 07:02.160 --> 07:05.280 We can give the default parameter while creating the function. 103 07:05.280 --> 07:06.750 So at that time. 104 07:07.620 --> 07:14.760 This is the same example as of above, but only one change I have done over here is where care of ten 105 07:14.760 --> 07:17.520 and over here I have given some default value. 106 07:17.700 --> 07:18.840 Over here I have. 107 07:18.870 --> 07:20.640 This is my default value right? 108 07:23.940 --> 07:28.140 So if I func uh then I do not need to pass any. 109 07:28.740 --> 07:30.630 Let me try. 110 07:36.420 --> 07:37.140 Okay. 111 07:37.170 --> 07:40.620 And to modify the function I can write the alter statement. 112 07:40.650 --> 07:41.820 Alter function. 113 07:41.850 --> 07:43.020 Function name. 114 07:43.020 --> 07:47.500 And then let me alter the existing function. 115 07:47.500 --> 07:49.450 So this is the alteration. 116 07:49.450 --> 07:52.090 And now if I mention over here default. 117 07:52.180 --> 07:54.310 Then it will give me clerk data only. 118 07:54.310 --> 07:56.380 It will give me automatically clerk data only. 119 07:56.410 --> 07:56.920 Why. 120 07:57.040 --> 07:59.770 Because I have over here mentioned default. 121 07:59.770 --> 08:01.090 But let me try. 122 08:03.310 --> 08:05.140 To mention the salesman. 123 08:06.940 --> 08:12.640 If I mention the salesman, then it will give me a salesman data. 124 08:12.640 --> 08:14.320 And if I mention the. 125 08:14.350 --> 08:20.890 Any other job ID like manager, then it will give me manager data. 126 08:20.890 --> 08:22.480 But if I mention like. 127 08:22.510 --> 08:25.900 Default, then what will happen? 128 08:28.750 --> 08:30.700 It will give me only clerk data. 129 08:30.730 --> 08:32.950 Because clerk is my default parameter, right? 130 08:32.980 --> 08:35.050 Clerk is my default parameter. 131 08:41.140 --> 08:41.950 Right. 132 08:43.690 --> 08:49.230 So This is what inline table valued function. 133 08:49.500 --> 08:51.780 Now let's see the synonym. 134 08:52.380 --> 08:54.090 Let's see the synonym. 135 08:54.090 --> 09:00.750 So a synonym is another name or alternative name for a table or view. 136 09:01.350 --> 09:06.240 Synonym is another name or alternative name for a table or view. 137 09:06.360 --> 09:13.230 If a table name is lengthy, then the developer can give a simple and short name to the table called 138 09:13.260 --> 09:15.990 a synonym, and wherever you want to use table name. 139 09:15.990 --> 09:19.230 Then, instead of using the table name, we can use synonym. 140 09:19.380 --> 09:25.410 So suppose there will be some table name in my database with very lengthy name right. 141 09:25.560 --> 09:36.060 Like select star from like dept then two zero something something then again something something. 142 09:36.060 --> 09:39.900 So this is a very lengthy name and difficult to remember. 143 09:40.080 --> 09:50.180 So in this particular case I can create a synonym for this particular table simply like a synonym. 144 09:51.680 --> 09:57.650 And then I can give another name to this particular table dept if it is not. 145 10:01.010 --> 10:01.550 Exist. 146 10:01.580 --> 10:02.000 Right. 147 10:02.000 --> 10:07.940 So at that time I can refer this particular table with the name of dept as well. 148 10:07.970 --> 10:08.330 Right. 149 10:08.360 --> 10:14.390 So this will be my another name for this particular object that is known as synonym. 150 10:14.390 --> 10:16.040 How we can create the synonym. 151 10:16.040 --> 10:22.070 Create synonym then synonym name for table name like. 152 10:37.160 --> 10:39.110 Like this is my EMP table, right. 153 10:39.140 --> 10:39.590 Create. 154 10:39.800 --> 10:43.300 Create synonym E for EMP table. 155 10:43.300 --> 10:49.840 So now I have created this enum synonym and after creating synonym, instead of using complete table 156 10:49.870 --> 10:53.380 name, we can use the synonym in select, insert, update or delete. 157 10:53.590 --> 10:55.660 Now if you see. 158 11:02.410 --> 11:05.140 And select star from EMP. 159 11:05.290 --> 11:07.090 So both will be same right? 160 11:07.120 --> 11:08.560 Both will be same. 161 11:09.280 --> 11:14.140 Both will give me the same same record 17in this 17in above. 162 11:15.280 --> 11:19.030 So this one E is a alternative table name. 163 11:19.060 --> 11:26.350 Now if you want to delete, suppose if you want to delete this 555 record, let me try to delete 555 164 11:26.380 --> 11:27.070 from E. 165 11:27.370 --> 11:34.060 Now I am delete 555 employee number from E, not from main table. 166 11:34.060 --> 11:35.080 So let me try. 167 11:35.110 --> 11:36.730 So I am able to delete. 168 11:36.760 --> 11:42.780 Now if I try to select that particular record from the original table. 169 11:42.900 --> 11:46.830 Then it will not available in the original table as well. 170 11:47.610 --> 11:50.370 It will not available in the original table as well. 171 11:50.400 --> 11:51.030 Right. 172 11:54.630 --> 11:58.080 Now what is the difference between synonym and alias? 173 11:58.110 --> 11:59.010 Sorry. 174 11:59.070 --> 12:00.120 Alias and synonym. 175 12:00.120 --> 12:00.600 Yes. 176 12:00.600 --> 12:03.570 So alias will not present. 177 12:04.320 --> 12:09.360 So alias is not permanent whereas synonym it is permanent. 178 12:09.390 --> 12:10.830 It is permanent. 179 12:10.980 --> 12:11.610 Why? 180 12:11.610 --> 12:12.420 It is permanent? 181 12:12.420 --> 12:14.490 Because it is stored in the database. 182 12:14.520 --> 12:16.530 It is stored in the database. 183 12:16.530 --> 12:20.790 So if you see over here let me try to identify. 184 12:22.800 --> 12:23.910 See over here. 185 12:23.940 --> 12:24.990 Synonyms are there. 186 12:24.990 --> 12:25.230 Right. 187 12:25.260 --> 12:28.440 Synonyms then dbo dot e. 188 12:28.470 --> 12:29.850 This is a synonym. 189 12:30.810 --> 12:34.290 Synonym is stored in the database. 190 12:34.290 --> 12:39.790 Whereas alias is a temporary name and it is not stored in the database. 191 12:41.860 --> 12:43.630 Scope of alias. 192 12:43.630 --> 12:46.000 Scope of alias is limited to query. 193 12:46.030 --> 12:49.150 Scope of synonym is up to the schema. 194 12:53.530 --> 13:01.510 So whenever query is over that scope, alias scope will be will be over, alias scope will be over, 195 13:01.510 --> 13:05.920 but the synonym scope will be for that particular schema only. 196 13:05.920 --> 13:12.610 Like DBO, HR or any other schema, we can define a synonym. 197 13:14.680 --> 13:17.800 Now let me know if you have till now any question. 198 13:17.800 --> 13:19.540 Let me ask. 199 13:28.090 --> 13:30.850 You can ask if you have any question. 200 13:33.040 --> 13:36.340 In inline table valued function and synonym. 201 13:41.460 --> 13:43.860 Inline table value function is a. 202 13:43.890 --> 13:50.970 We can say that parameterized view, because in a view there is no facility to provide the parameter. 203 13:50.970 --> 13:57.180 So that is why the evaluation of inline table value function came into the picture. 204 14:03.330 --> 14:06.390 If there is no question then we can move forward. 205 14:22.950 --> 14:26.880 So now let us see the this is synonym. 206 14:27.390 --> 14:27.810 Okay. 207 14:27.810 --> 14:29.400 Now let's see the sequences. 208 14:29.400 --> 14:31.290 Now let's see the sequences. 209 14:31.650 --> 14:39.170 So sequence are created to generate sequence Once numbers sequence are created to generate sequence 210 14:39.170 --> 14:39.920 numbers. 211 14:39.920 --> 14:47.420 So if you remember the identity property of the table or column we have seen in the very beginning. 212 14:47.420 --> 14:52.910 So this is the same kind of thing, but we will see what is the difference later on. 213 14:53.330 --> 14:57.320 But this is same kind of thing almost. 214 14:57.500 --> 15:00.830 So sequence are created to generate sequence numbers. 215 15:00.860 --> 15:05.720 Right now using sequence we can auto increment column values. 216 15:05.750 --> 15:07.520 Now what is the syntax. 217 15:07.700 --> 15:10.730 Create sequence sequence name. 218 15:10.730 --> 15:12.380 Start with value. 219 15:12.500 --> 15:13.910 Start with value. 220 15:13.910 --> 15:16.400 So from where the sequence will start. 221 15:17.690 --> 15:19.460 Then increment by what. 222 15:19.940 --> 15:21.320 Increment by what. 223 15:22.100 --> 15:24.410 Then maximum value mean value cycle. 224 15:24.410 --> 15:25.370 No cycle. 225 15:26.930 --> 15:29.000 Now let us see one example. 226 15:29.000 --> 15:30.560 Create sequence. 227 15:38.350 --> 15:38.740 Okay. 228 15:38.740 --> 15:40.450 Create sequence S1. 229 15:40.450 --> 15:43.660 So it will create one some kind of sequence right. 230 15:43.690 --> 15:44.800 Sequence. 231 15:44.830 --> 15:50.050 Now create a sequence to generate sequence from 1 to 5. 232 15:51.130 --> 15:54.370 Create a sequence to generate a sequence from 1 to 5. 233 15:54.370 --> 15:56.110 So let me create this one. 234 15:59.080 --> 15:59.470 Okay. 235 15:59.500 --> 16:02.020 It is already exist, so drop. 236 16:24.520 --> 16:26.950 I think I have to drop this table also. 237 16:26.980 --> 16:27.880 One minute. 238 16:43.230 --> 16:46.530 So now create sequence as to start with one. 239 16:46.530 --> 16:48.330 So it will start with one right. 240 16:48.330 --> 16:49.830 It will start with one. 241 16:50.310 --> 16:51.870 It will start with one. 242 16:51.870 --> 16:54.900 Then increment by one and max value five. 243 16:54.960 --> 16:57.180 Now let me create this table. 244 16:57.300 --> 16:59.340 So it will create this table. 245 16:59.400 --> 17:05.850 Now if you see for s id s id column at the time of insert. 246 17:05.850 --> 17:07.860 Also I can use the sequence. 247 17:07.950 --> 17:15.870 So if you want to retrieve the next value from the sequence, next value means start with one right? 248 17:15.870 --> 17:17.490 So let me try. 249 17:17.610 --> 17:19.170 This table is created. 250 17:19.170 --> 17:26.220 So in the integer column only we can use the sequence in the integer data type column only we can use 251 17:26.220 --> 17:27.210 the sequence. 252 17:27.420 --> 17:33.730 So I am not inserting the value for s id, Sid, I am inserting the value for S name, right? 253 17:33.730 --> 17:36.190 So over here I am inserting this data. 254 17:36.220 --> 17:43.210 Let me try to see what is the value it is generating. 255 17:46.030 --> 17:49.390 Select star from this one. 256 17:51.460 --> 17:51.970 Okay. 257 17:51.970 --> 17:53.530 It is generating one right? 258 17:53.650 --> 17:56.620 Start with one and increment by one. 259 17:56.650 --> 18:00.310 Now if I generate the second statement it will become two. 260 18:00.340 --> 18:03.460 If I generate third statement it will become three. 261 18:03.610 --> 18:06.580 If I generate four statement it will become four. 262 18:06.610 --> 18:10.120 If I generate fifth statement, it will become five. 263 18:10.150 --> 18:14.740 Now let me select the data 12345. 264 18:14.770 --> 18:18.340 Now max value is five. 265 18:18.370 --> 18:18.910 Is there right. 266 18:18.940 --> 18:19.900 Max value is five. 267 18:19.930 --> 18:20.560 Is there. 268 18:20.860 --> 18:26.770 Now if I try to insert then see what error it is throwing. 269 18:30.540 --> 18:35.790 The sequential object S2 has reached its maximum, minimum or maximum value. 270 18:35.820 --> 18:39.690 Restart the sequence object to allow new values to be generated. 271 18:42.570 --> 18:47.970 Now let me try with cycle. 272 18:47.970 --> 18:49.890 Let me try with cycle. 273 18:50.820 --> 18:53.340 Let me drop this sequence. 274 18:55.530 --> 18:59.790 Let me drop this table again and let me. 275 19:03.480 --> 19:05.580 Max value five. 276 19:05.700 --> 19:07.410 Max value five. 277 19:13.050 --> 19:13.560 Okay. 278 19:13.590 --> 19:14.520 Cycle. 279 19:14.880 --> 19:15.600 Okay. 280 19:15.630 --> 19:17.880 Now, now let's see. 281 19:17.910 --> 19:19.320 Let's see one more time. 282 19:19.320 --> 19:20.640 The same example. 283 19:21.420 --> 19:24.660 Now there is no data as of now. 284 19:24.660 --> 19:26.310 Currently in this particular table. 285 19:26.310 --> 19:28.550 Now I am inserting this five record. 286 19:29.120 --> 19:31.190 Now I am inserting this five record. 287 19:31.190 --> 19:32.810 Let me show you the data. 288 19:32.840 --> 19:34.850 12345. 289 19:35.180 --> 19:37.190 Now I am trying to insert. 290 19:37.190 --> 19:38.420 Let me try. 291 19:42.470 --> 19:48.980 Arithmetic overflow error in converting expression to data type. 292 19:49.610 --> 19:50.330 Integer. 293 19:58.400 --> 20:00.410 Arithmetic overflow error. 294 20:00.440 --> 20:01.610 Converting data. 295 20:13.910 --> 20:16.520 Okay, we will see this example later on. 296 20:16.550 --> 20:21.020 Actually ideally this error should not come maybe in the next. 297 20:22.640 --> 20:24.080 I have written something. 298 20:24.080 --> 20:28.870 So now let's move forward using the sequence in the update command. 299 20:28.900 --> 20:31.420 So now we can use the same use. 300 20:31.420 --> 20:34.120 The sequence is in the update command as well. 301 20:34.480 --> 20:36.250 So let me. 302 20:39.250 --> 20:40.300 Drop. 303 20:42.490 --> 20:43.960 Sequence S3. 304 20:43.990 --> 20:45.820 Let me drop this sequence. 305 20:47.710 --> 20:49.450 Let me create. 306 20:51.100 --> 20:52.510 Now say. 307 20:57.520 --> 20:58.990 Select. 308 20:59.020 --> 21:00.400 Select. 309 21:01.690 --> 21:02.470 Next. 310 21:02.470 --> 21:02.950 Value. 311 21:02.950 --> 21:03.820 Next. 312 21:12.400 --> 21:13.270 100. 313 21:13.300 --> 21:13.720 Okay. 314 21:13.750 --> 21:15.370 Over here it is 100. 315 21:16.720 --> 21:18.430 Now see? 316 21:18.430 --> 21:19.090 Over here. 317 21:19.120 --> 21:19.960 See over here. 318 21:19.990 --> 21:21.490 Select star from employee. 319 21:21.520 --> 21:23.500 Select star from employee. 320 21:25.850 --> 21:27.980 Or select star from E! 321 21:30.890 --> 21:38.750 Okay now if you see over here MP number let me take the backup of this table. 322 21:40.700 --> 21:45.020 Select star into MP two from. 323 21:51.740 --> 21:54.050 Now see create sequence as three. 324 21:54.080 --> 21:58.160 Start with 100 and increment by one and max value 999. 325 21:58.250 --> 22:02.720 Now if I, I have shown you what is the next value for s three. 326 22:02.750 --> 22:05.540 So it will become 101 right. 327 22:05.570 --> 22:07.460 It will become 101. 328 22:07.640 --> 22:15.590 So if I update this update E set MP number equal to next value for s three, then if I. 329 22:15.620 --> 22:20.780 Now if I execute this statement let me show you. 330 22:26.260 --> 22:28.270 It will start with 102. 331 22:28.300 --> 22:31.810 It is automatically incrementing 101. 332 22:31.810 --> 22:43.750 It will there so it has incremented by one and then all the other value it is doing 1011021031042117. 333 22:46.900 --> 22:52.090 So in the update statement also we can use the sequence in the insert statement. 334 22:52.090 --> 22:59.890 Also we can use the sequence and we can check the sequence value manually. 335 22:59.890 --> 23:01.300 Also what will be the next value. 336 23:01.300 --> 23:05.980 So over here 118 is there 118 is there. 337 23:06.010 --> 23:08.980 Now it will start from 119. 338 23:12.790 --> 23:17.500 Now using a sequence in the expression using a sequence in the expression. 339 23:17.500 --> 23:21.510 So we can use the sequence in the expression also. 340 23:21.720 --> 23:25.470 So one minute drop table. 341 23:25.470 --> 23:26.430 This invoice table. 342 23:26.430 --> 23:27.630 Let me drop it. 343 23:29.670 --> 23:31.080 And drop. 344 23:36.570 --> 23:37.620 Drop sequence. 345 23:37.650 --> 23:38.730 As for. 346 23:49.470 --> 23:54.750 Now I am inserting the data over here and this will become my some expression, right? 347 23:54.870 --> 24:00.840 So in the expression also I can use the next value for S4. 348 24:00.870 --> 24:03.960 So let me try to insert the data and let. 349 24:03.990 --> 24:10.350 Now let me try to select the data from this select star from invoice. 350 24:10.500 --> 24:19.220 So this will generate some random sequence number random sequence number in the while over here in the 351 24:19.220 --> 24:20.810 invoice table. 352 24:20.900 --> 24:23.360 So first it will be start with FCT. 353 24:23.720 --> 24:26.060 FCT slash sign. 354 24:26.060 --> 24:28.700 Then it will start with one increment by one. 355 24:28.700 --> 24:32.030 So one two and four. 356 24:32.060 --> 24:33.590 Format get date. 357 24:33.770 --> 24:37.040 Okay so this will become my 0924 right. 358 24:37.910 --> 24:41.000 Ninth month and 2024 is coming. 359 24:41.540 --> 24:42.500 So 24. 360 24:42.500 --> 24:45.230 And then cast next value for this one. 361 24:55.070 --> 24:58.400 So we can use now how to reset the sequence value. 362 24:58.430 --> 25:01.730 How to using alter command or using the cycle option. 363 25:02.000 --> 25:10.100 So if I using the alter command also we can uh reset the sequence value. 364 25:10.100 --> 25:13.610 And with the cycle option we can also reset the sequence value. 365 25:14.360 --> 25:17.950 Now let me create one more sequence I will show you. 366 25:18.730 --> 25:21.250 First, what will be the cycle? 367 25:27.040 --> 25:28.150 Drop sequence. 368 25:28.180 --> 25:34.690 S6 or let me create a totally new sequence like six, seven six. 369 25:37.150 --> 25:39.040 So I have created this sequence. 370 25:39.100 --> 25:41.050 Start with one increment by one. 371 25:41.050 --> 25:41.890 Max value five. 372 25:41.920 --> 25:44.500 Min value one and cycle. 373 25:44.650 --> 25:48.340 Now if I use this sequence. 374 25:48.340 --> 25:49.180 If I. 375 25:49.420 --> 25:51.760 Let me drop this table. 376 26:04.810 --> 26:11.230 Now see carefully 12345I am okay. 377 26:11.260 --> 26:11.800 One minute. 378 26:11.830 --> 26:12.160 Okay. 379 26:12.160 --> 26:13.900 We have to use this one. 380 26:24.740 --> 26:27.470 Let me insert only one record. 381 26:28.880 --> 26:29.870 Now, again. 382 26:29.870 --> 26:30.890 One record. 383 26:31.520 --> 26:32.300 Now again. 384 26:32.300 --> 26:33.350 One record. 385 26:34.880 --> 26:35.600 Now again. 386 26:35.600 --> 26:36.560 One record. 387 26:37.220 --> 26:38.060 Now again. 388 26:38.060 --> 26:38.960 One record. 389 26:40.010 --> 26:43.130 And now let me, let me now show you the data. 390 26:43.160 --> 26:44.720 Select. 391 26:47.060 --> 26:52.910 Star from this particular table 12345. 392 26:53.600 --> 26:56.690 Now let me try to insert this time. 393 26:56.720 --> 26:57.290 Okay. 394 26:57.320 --> 26:58.700 One row affected. 395 26:59.660 --> 27:01.640 Previously it is getting error right. 396 27:01.640 --> 27:06.980 Previously this statement is getting error because it has reached the max value. 397 27:07.010 --> 27:08.420 Max value is five. 398 27:08.630 --> 27:10.160 Max value is five. 399 27:10.160 --> 27:12.980 So that is why it is giving some error previously. 400 27:13.010 --> 27:16.510 But now this time I have used this min value again. 401 27:16.510 --> 27:20.230 I am doing the min value one and I am using the cycle. 402 27:20.230 --> 27:28.510 So cycle means it will repeat whenever the max value will come, it will repeat with the minimum value 403 27:28.870 --> 27:31.210 again and again and again and again. 404 27:31.210 --> 27:37.720 So if I run this select statement now so see what is the result. 405 27:37.750 --> 27:39.970 It will again start with minimum value. 406 27:40.000 --> 27:40.900 Minimum value. 407 27:40.930 --> 27:43.840 We have set as one minimum value. 408 27:43.870 --> 27:45.280 We have set as one. 409 27:45.280 --> 27:48.250 So again it is starting with the minimum value right. 410 27:50.080 --> 27:55.360 That is what I want to show you above as well. 411 27:58.030 --> 28:02.080 Now if you see over here let me okay. 412 28:02.080 --> 28:04.660 So what is this cycle and no cycle option. 413 28:04.660 --> 28:06.640 Default option is no cycle. 414 28:06.760 --> 28:09.040 Default option is no cycle. 415 28:09.370 --> 28:15.750 If sequence is created with no cycle Then it starts and generates up to max up to max value. 416 28:15.780 --> 28:16.050 Right? 417 28:16.080 --> 28:18.030 That is five in our case. 418 28:18.060 --> 28:20.820 And after reaching the max value, it stops. 419 28:20.820 --> 28:23.130 It will not generate any other value. 420 28:23.160 --> 28:30.270 It gives error, but if the sequence is generated with the cycle, then it start from the start with 421 28:30.270 --> 28:37.440 and generate up to up to max and after reaching the max, then it will be automatically reset to minimum 422 28:37.440 --> 28:41.100 value, then it will become automatically reset to minimum value. 423 28:44.370 --> 28:45.660 So minimum value. 424 28:45.660 --> 28:48.870 Also in this particular case we have defined right. 425 28:53.790 --> 28:55.410 Now let's see this one. 426 28:56.220 --> 29:02.280 Now we have I told you that identity property and sequence both are almost similar. 427 29:02.310 --> 29:04.980 But now what is the difference between them. 428 29:05.790 --> 29:11.390 So let's see that one identity property is a bind to a particular column. 429 29:11.420 --> 29:14.750 Identity property is bind to a particular column only. 430 29:14.750 --> 29:20.000 We have defined identity property at column level only right now. 431 29:20.000 --> 29:21.110 Particular table. 432 29:21.110 --> 29:22.190 In particular table. 433 29:22.220 --> 29:29.540 But sequence sequence is not bind to any column or any table. 434 29:29.630 --> 29:34.520 We can use this particular sequence in any of the table in the database. 435 29:34.760 --> 29:39.890 We can use this particular sequence in any of the table in the database. 436 29:39.890 --> 29:47.600 Right now identity cannot be controlled by application code whereas sequence can be controlled by application 437 29:47.600 --> 29:48.260 code. 438 29:48.290 --> 29:53.420 What we are doing over here, we are doing over here controlling the sequence. 439 29:53.420 --> 29:53.660 Right. 440 29:53.660 --> 29:55.220 Controlling the sequence. 441 29:59.300 --> 30:06.860 Where once identity will be generated it will be either we have to drop, but we cannot control that 442 30:06.860 --> 30:09.700 one right Now. 443 30:09.730 --> 30:16.480 Identity property of a column is available since SQL server 2000, where sequence object is available 444 30:16.480 --> 30:18.520 since SQL server 2002. 445 30:18.550 --> 30:18.910 One. 446 30:18.940 --> 30:19.540 Two. 447 30:20.170 --> 30:26.350 We cannot get the value of an identity column before inserting a record, whereas we can get the value 448 30:26.350 --> 30:30.640 of sequence number for a sequence object before inserting a record. 449 30:32.500 --> 30:41.620 That means over here, if we do not even inserted any record, then also we can get like this, right? 450 30:42.430 --> 30:46.630 Like we have to write like select next value. 451 30:46.630 --> 30:47.860 Next value. 452 30:49.990 --> 30:55.150 Next value for this particular sequence any of the sequence. 453 30:55.150 --> 30:59.320 So it will give me the record right. 454 31:06.260 --> 31:08.120 Y2Y2. 455 31:08.300 --> 31:14.600 Because over here, if you see only one again it is started with one. 456 31:14.810 --> 31:18.920 Then if we insert the next value, it will become two only, right? 457 31:18.920 --> 31:20.600 It will become two only. 458 31:20.900 --> 31:24.740 That is why it is showing over here in the below query two. 459 31:26.300 --> 31:32.420 Sorry, I think every time even though if we are not inserting it will become increase. 460 31:32.450 --> 31:37.700 See over here 12345. 461 31:37.730 --> 31:43.010 Now again 12345. 462 31:43.040 --> 31:44.660 Again one. 463 31:50.600 --> 31:55.580 Now we can received an identity property. 464 31:55.580 --> 31:57.620 But we cannot change the step size. 465 31:57.620 --> 31:58.220 But. 466 32:01.400 --> 32:02.420 One minute. 467 32:09.250 --> 32:15.130 We can we can reset the identity property, we can reset the identity property, but we cannot change 468 32:15.130 --> 32:15.940 the step size. 469 32:15.940 --> 32:22.570 But where we as we can alter the seed as well as the step size of a sequence object at any time. 470 32:28.420 --> 32:29.980 What do you mean by step size? 471 32:30.010 --> 32:32.410 Step size means starting value. 472 32:32.470 --> 32:40.480 We cannot change the starting value in the identity property, but over here we can change the step 473 32:40.480 --> 32:44.050 size by by defining the mean value and cycle option. 474 32:44.050 --> 32:48.820 Right by defining the mean value and cycle option. 475 32:48.820 --> 32:54.580 So again it will repeating the same sequence 123451234512345. 476 32:54.610 --> 32:55.750 Again and again. 477 32:58.840 --> 33:01.000 So this is all about sequence. 478 33:01.000 --> 33:03.580 Now let me know if you have any doubt.