1
00:01.640 --> 00:03.560
So we are yesterday.

2
00:03.560 --> 00:05.810
We are at the Recursive City.

3
00:06.680 --> 00:09.440
So let's take that example again.

4
00:09.440 --> 00:11.000
So let me.

5
00:14.840 --> 00:15.800
Copy this data.

6
00:15.830 --> 00:19.190
Actually I have already copied the data over here.

7
00:20.030 --> 00:25.100
So so recursive city will have a two different member.

8
00:25.130 --> 00:25.700
Nice.

9
00:25.880 --> 00:31.700
One is a anchor member and second one is a recursive member.

10
00:31.700 --> 00:34.580
So below one this one will be the.

11
00:38.810 --> 00:40.130
Recursive member.

12
00:40.160 --> 00:44.210
And above one will be the anchor member right now.

13
00:44.210 --> 00:45.020
Recursive.

14
00:45.080 --> 00:48.590
This anchor member will be executed only once.

15
00:49.160 --> 00:51.560
Anchor member will be executed only once.

16
00:51.560 --> 01:00.890
So if we execute this particular query first, this anchor member will be executed right First, this

17
01:00.920 --> 01:02.990
anchor member will be executed.

18
01:03.020 --> 01:05.750
So it will give me this result.

19
01:07.430 --> 01:09.140
And it has a particular.

20
01:09.170 --> 01:10.760
It has a no manager.

21
01:11.780 --> 01:12.950
So.

22
01:17.900 --> 01:18.260
Okay.

23
01:18.290 --> 01:20.870
It has a manager ID is null.

24
01:20.870 --> 01:24.920
So that is why manager name we have taken as a super manager right.

25
01:25.280 --> 01:26.870
Super manager.

26
01:30.080 --> 01:31.160
Now.

27
01:32.420 --> 01:36.860
Now if you see over here this table will become our H table.

28
01:37.250 --> 01:39.440
First table will become our H table.

29
01:39.470 --> 01:43.160
Because in the query we have written two different tables.

30
01:43.190 --> 01:48.860
One is the city itself and another one is a EMP table.

31
01:48.890 --> 01:49.370
Right.

32
01:49.400 --> 01:51.110
Another one is a EMP table.

33
01:51.110 --> 01:55.100
So now we have given the alias EMP as H.

34
01:55.130 --> 01:58.490
So we have to consider first table as h.

35
01:58.490 --> 02:02.090
And second table as is now over here.

36
02:08.360 --> 02:11.330
Over here in the E so e dot EMP number.

37
02:11.360 --> 02:14.030
So this one will become our super manager.

38
02:14.150 --> 02:16.880
We have already taken this record over here right.

39
02:16.910 --> 02:17.300
We have.

40
02:18.380 --> 02:25.640
Now we have to take the manager column from this particular age table.

41
02:25.670 --> 02:26.270
Right.

42
02:26.330 --> 02:32.450
So if you go to this second second row, this one will become your super manager.

43
02:32.450 --> 02:36.500
If you go to this second row then you have to check this manager column.

44
02:36.500 --> 02:37.760
Manager column.

45
02:37.760 --> 02:38.300
Right.

46
02:38.330 --> 02:40.790
This manager column from this table.

47
02:42.860 --> 02:45.920
Will be joining the employee column of this table.

48
02:45.950 --> 02:46.610
Right.

49
02:46.700 --> 02:50.000
Now this manager is 7566.

50
02:50.180 --> 02:52.130
So now you have to.

51
02:54.500 --> 02:58.280
H dot mgr equal to e dot

52
03:01.580 --> 03:02.720
MP number.

53
03:02.750 --> 03:05.450
Now you have to identify this seven five, six.

54
03:05.480 --> 03:10.790
This is where in this particular E table MP number column.

55
03:11.450 --> 03:14.840
So 7566 is available over here.

56
03:14.870 --> 03:15.440
Right.

57
03:15.470 --> 03:18.320
7566 is available over here.

58
03:18.350 --> 03:23.180
So Jones will become the manager over here.

59
03:23.210 --> 03:26.990
So wherever 756 is Jones will become the manager.

60
03:26.990 --> 03:30.710
And this will become super manager.

61
03:30.710 --> 03:31.190
Right.

62
03:31.220 --> 03:32.960
Now again it will go.

63
03:32.990 --> 03:34.370
Go to the next row.

64
03:34.400 --> 03:37.370
Because city is referencing itself.

65
03:37.670 --> 03:39.770
City is recursive.

66
03:39.770 --> 03:41.930
City will be referencing itself.

67
03:42.110 --> 03:44.360
So again it will go to the next row.

68
03:44.390 --> 03:51.110
Then it will check what is the manager ID of this particular particular row.

69
03:51.140 --> 03:55.580
So it will search this 7654 into this particular column

70
03:57.710 --> 03:59.000
7654.

71
03:59.030 --> 04:00.050
It is over here.

72
04:00.050 --> 04:03.320
So Martin will be the manager of this particular employee.

73
04:03.350 --> 04:03.800
Jerry.

74
04:06.140 --> 04:07.880
Martin will be the.

75
04:12.080 --> 04:15.590
Manager of this particular employee right now.

76
04:15.620 --> 04:16.670
Again it will.

77
04:18.020 --> 04:22.940
So this this data is now present in our result set.

78
04:22.970 --> 04:23.990
This data.

79
04:35.060 --> 04:35.480
Okay.

80
04:35.510 --> 04:37.790
This data is present in our result set.

81
04:37.880 --> 04:39.500
Now what what it will do.

82
04:39.530 --> 04:41.510
It will go again to the next row.

83
04:41.630 --> 04:47.900
So 79007698 is the manager.

84
04:47.900 --> 04:51.470
So it will check 7698 in this particular column.

85
04:52.340 --> 04:54.110
So where is 7698.

86
04:54.140 --> 04:54.620
Black.

87
04:54.620 --> 04:55.160
Is there right.

88
04:55.190 --> 04:59.240
Black So it will copy.

89
04:59.240 --> 05:00.320
Black over here.

90
05:00.350 --> 05:01.340
Black over here.

91
05:01.370 --> 05:02.240
Black over here.

92
05:02.240 --> 05:03.350
Black over here.

93
05:03.350 --> 05:04.970
And black over here.

94
05:12.830 --> 05:22.640
So let me delete one minute this data and let me copy this data.

95
05:23.030 --> 05:25.820
So now black is copied everywhere right.

96
05:25.850 --> 05:28.880
Wherever this particular.

97
05:36.290 --> 05:40.400
Wherever this particular seven, six, nine, eight is there right now.

98
05:40.400 --> 05:42.740
It will check again for the next row.

99
05:42.740 --> 05:45.740
And next row it will check 7782.

100
05:45.770 --> 05:51.560
So where 7782 will come in this particular table E dot EMP number column.

101
05:51.680 --> 05:53.390
So it is Clark.

102
05:53.390 --> 05:53.630
Right.

103
05:53.660 --> 05:54.350
Clark.

104
05:54.770 --> 05:56.030
So it will give copy.

105
05:56.030 --> 05:57.900
Clark Over here now!

106
05:57.900 --> 06:03.030
787678767788.

107
06:03.060 --> 06:03.300
Right.

108
06:03.300 --> 06:07.830
So we have to copy this one and check it into this particular table.

109
06:09.330 --> 06:14.910
So Scott will be the manager of this Adams right now 7698.

110
06:14.910 --> 06:17.670
So it will be 7839 coming three times.

111
06:17.670 --> 06:20.190
So we have to check again over here.

112
06:21.000 --> 06:22.200
And it will be the king.

113
06:22.200 --> 06:22.740
Right.

114
06:22.740 --> 06:23.940
So we have to copy.

115
06:23.940 --> 06:24.660
King over here.

116
06:24.690 --> 06:25.380
King over here.

117
06:25.410 --> 06:26.610
King over here.

118
06:26.910 --> 06:29.880
And 73697902.

119
06:29.910 --> 06:33.120
We have to copy this one and check over here in the second table.

120
06:36.300 --> 06:39.300
So Ford will be the manager of this myth, right?

121
06:41.400 --> 06:43.560
Ford will be the manager of this myth.

122
06:43.650 --> 06:47.730
Now, there is no particular rows, are there?

123
06:47.730 --> 06:50.370
Right now there is no particular rows, are there?

124
06:50.370 --> 06:52.530
So city will be stopped.

125
06:52.650 --> 06:59.700
City execution, recursive city execution will be stopped and it will copy all the data over here.

126
07:02.520 --> 07:03.810
All the data over here.

127
07:03.810 --> 07:06.810
So this is how Recursive City will be executed.

128
07:06.960 --> 07:12.930
Now, if you see the same thing we have seen at the time of doing the Self-join.

129
07:13.440 --> 07:17.040
The same thing we have seen at the time of doing the self-join.

130
07:17.040 --> 07:24.000
So if I execute both of these query, it will give me the same result.

131
07:24.030 --> 07:24.810
See?

132
07:29.700 --> 07:35.370
This is the example of Self-join and below one is the result set.

133
07:40.320 --> 07:42.150
Result set of the.

134
07:44.580 --> 07:45.690
Recursive city.

135
07:45.720 --> 07:51.420
So see over here 737369.

136
07:51.510 --> 07:52.290
Smith.

137
07:52.290 --> 07:53.280
Manager is Ford.

138
07:53.310 --> 07:55.830
So if you find 7369 Smith.

139
07:55.830 --> 07:56.190
Manager.

140
07:56.190 --> 07:57.420
Is Ford over here?

141
07:59.130 --> 08:00.030
It is correct.

142
08:00.030 --> 08:00.510
Right.

143
08:00.540 --> 08:03.900
We have seen this in the same scenario at the time of.

144
08:07.140 --> 08:08.040
Self-join.

145
08:08.040 --> 08:13.860
Right now if you see align n word, what are the alien word?

146
08:15.030 --> 08:15.750
Manager.

147
08:15.750 --> 08:16.830
It is a black right?

148
08:16.830 --> 08:17.820
It is a black.

149
08:17.820 --> 08:24.720
So if you see ln n word where where ln n word is coming over here it is black over here in the recursive

150
08:24.720 --> 08:25.860
setting as well.

151
08:26.220 --> 08:28.290
So this is the result set of.

152
08:32.130 --> 08:33.810
Self-join.

153
08:33.900 --> 08:36.330
And this is the result set of.

154
08:42.060 --> 08:44.160
City right.

155
08:48.990 --> 08:51.510
Now let's see one more example.

156
08:51.900 --> 08:54.450
Now let's see one more example

157
09:03.180 --> 09:05.430
Now I want to generate the hierarchical.

158
09:05.430 --> 09:06.120
Hierarchical.

159
09:07.380 --> 09:11.100
I want to generate the date based upon the hierarchical date.

160
09:11.100 --> 09:11.670
Right.

161
09:11.910 --> 09:15.840
Hierarchical date means I want the data like this.

162
09:20.310 --> 09:22.200
Then I'm.

163
09:30.360 --> 09:31.860
Up to 31st August.

164
09:31.860 --> 09:40.440
I want data like this because in power BI also I have I have to create the date column.

165
09:40.440 --> 09:40.680
Right.

166
09:40.680 --> 09:42.720
I have to create the date column right.

167
09:43.050 --> 09:47.790
In power BI or date table with date column date table with date column.

168
09:47.790 --> 09:50.220
So this is my table name.

169
09:50.220 --> 09:56.130
You can say that date table and column name is Date, right?

170
09:58.170 --> 10:00.120
Now this thing also we can.

171
10:02.550 --> 10:09.570
Achieve through the this thing also we can achieve through the recursive city.

172
10:09.570 --> 10:13.350
So what I have done over here, recursive city has two different parts right.

173
10:13.560 --> 10:16.020
So first part will be the start date.

174
10:16.020 --> 10:17.850
Start date I have to mention.

175
10:17.850 --> 10:18.120
Right.

176
10:18.150 --> 10:21.840
So for that purpose I have used this date formats.

177
10:21.840 --> 10:22.320
Parts.

178
10:22.500 --> 10:23.250
Date.

179
10:24.750 --> 10:25.260
Date.

180
10:25.260 --> 10:26.310
Form part function.

181
10:26.310 --> 10:31.470
So date form part function will have multiple parameters I have to mention here.

182
10:36.150 --> 10:39.810
I have to mention the month and I have to mention one right.

183
10:39.840 --> 10:44.190
Because start date will be 1st August current month right.

184
10:44.220 --> 10:46.110
Current month will current month.

185
10:46.140 --> 10:48.630
First date will be my start date right.

186
10:49.740 --> 10:50.670
Current month.

187
10:50.700 --> 10:53.940
First date will be my start date right?

188
10:56.400 --> 10:58.440
So this will become my start date.

189
10:58.440 --> 11:03.360
So if I execute this query, it will give me 1st August 2024.

190
11:05.370 --> 11:08.010
202408 and 1st August.

191
11:08.040 --> 11:10.110
Right now.

192
11:14.010 --> 11:21.570
I have to execute this particular loop until 31st August 2002 for only.

193
11:22.230 --> 11:23.940
So I want Eomonth right?

194
11:23.970 --> 11:25.770
How can I get the eomonth?

195
11:26.730 --> 11:29.940
Select Eomonth of.

196
11:32.100 --> 11:35.940
Get date so it will give me 31st August.

197
11:35.940 --> 11:38.250
It will give me 31st August right?

198
11:38.550 --> 11:42.510
Because anyhow I have to mention some exit exist condition.

199
11:42.510 --> 11:43.110
Right.

200
11:45.810 --> 11:48.060
I have to mention some exist condition.

201
11:48.060 --> 11:48.720
Right.

202
11:51.180 --> 11:53.550
So this this loop will be exit.

203
11:53.550 --> 11:57.090
And over here what I have done, I have done the.

204
12:00.570 --> 12:01.770
I have done the.

205
12:03.480 --> 12:06.330
I have done the one date addition.

206
12:06.390 --> 12:08.700
One date addition into.

207
12:12.450 --> 12:15.060
Into this particular column.

208
12:15.060 --> 12:16.860
Into this particular column.

209
12:16.920 --> 12:21.270
So this date will be increased every time by one day.

210
12:21.300 --> 12:22.920
Every time by one day.

211
12:25.020 --> 12:28.230
And until until when this date will be increased.

212
12:28.560 --> 12:30.330
Until 31st of August.

213
12:30.360 --> 12:34.710
Only until 31st of August only it will increase.

214
12:34.740 --> 12:35.340
Right?

215
12:35.520 --> 12:41.220
So now if I execute this query, it will give me the result set.

216
12:48.150 --> 12:48.690
Right.

217
12:48.690 --> 12:52.020
It will give me 1st August till 31st of August data.

218
12:55.260 --> 12:57.330
Okay, now let's see one more example.

219
12:57.360 --> 13:01.560
Now my my requirement is I want the data.

220
13:01.590 --> 13:03.060
I want the data.

221
13:09.720 --> 13:12.780
202401-01.

222
13:16.470 --> 13:17.250
Till.

223
13:23.730 --> 13:24.750
Enter one.

224
13:25.050 --> 13:26.400
Enter your data I want.

225
13:26.430 --> 13:27.360
Enter your date.

226
13:27.360 --> 13:27.930
Right.

227
13:28.290 --> 13:31.050
So that one also we can achieve right.

228
13:31.080 --> 13:32.970
That thing also we can achieve.

229
13:33.030 --> 13:38.700
So over here my start date will be my 1st January.

230
13:38.730 --> 13:38.940
Right.

231
13:38.970 --> 13:40.410
1st January.

232
13:42.660 --> 13:43.890
2024.

233
13:43.890 --> 13:45.510
So it is my start date right?

234
13:45.540 --> 13:47.520
1st January, 2024.

235
13:47.790 --> 13:49.530
I have taken it over here.

236
13:49.950 --> 13:53.310
This query will return execute only once.

237
14:04.470 --> 14:05.940
And it is.

238
14:08.880 --> 14:09.660
Anchor member.

239
14:09.660 --> 14:10.890
Right now.

240
14:10.890 --> 14:15.990
Until when this query we have to execute that thing will be changed over here.

241
14:16.260 --> 14:18.420
Everything else will be same.

242
14:20.700 --> 14:22.230
This thing will be same, right?

243
14:22.260 --> 14:23.730
This thing will be same.

244
14:26.640 --> 14:28.860
But we need to change this part, right?

245
14:28.890 --> 14:29.370
This part?

246
14:29.400 --> 14:31.080
How we can change this part.

247
14:31.110 --> 14:34.320
We have to mention like this.

248
14:35.760 --> 14:36.990
Date from past.

249
14:36.990 --> 14:38.610
Date from past year.

250
14:38.610 --> 14:39.690
The year of get date.

251
14:39.690 --> 14:40.350
And then.

252
14:44.310 --> 14:45.930
Let's run this query.

253
14:45.930 --> 14:47.640
Let's try to run this query

254
14:51.090 --> 14:54.120
So see over here it is giving some error.

255
14:54.120 --> 14:55.680
It is giving some error.

256
14:55.680 --> 14:56.340
Right?

257
14:59.190 --> 15:03.180
The statement terminated the maximum recursive recursion.

258
15:04.590 --> 15:06.480
The maximum recursion.

259
15:07.380 --> 15:09.840
100 has been exhausted.

260
15:11.550 --> 15:19.590
So recursive query will give me up to 100 100, 100 records if we do not find this particular clause.

261
15:20.160 --> 15:26.700
If we do not find this particular clause for that purpose, we have to mention like this.

262
15:28.590 --> 15:30.540
This is the maximum limit.

263
15:30.690 --> 15:35.610
32767 is the.

264
15:41.280 --> 15:42.840
Maximum limit.

265
15:42.930 --> 15:46.170
Now I can mention like this also right.

266
15:48.720 --> 15:52.050
Because in a year there will be a 365 days, right?

267
15:52.290 --> 15:54.870
In a year there will be a 365 days.

268
15:55.050 --> 15:59.010
So I think this one will be the leap year.

269
16:08.940 --> 16:10.890
February has one extra.

270
16:10.920 --> 16:11.550
Extra.

271
16:11.580 --> 16:13.500
I think February.

272
16:13.920 --> 16:14.310
Yes.

273
16:14.310 --> 16:16.530
February is one extra date, right?

274
16:23.070 --> 16:25.770
Now let me know if you have any questions.

275
16:30.180 --> 16:33.930
Now, if we do not specify the Where clause then let me execute this query.

276
16:33.960 --> 16:40.380
See how it can be identified at which place I have to exit?

277
16:40.500 --> 16:43.260
At which place I have to exit the loop.

278
16:44.550 --> 16:47.310
At which place I have to exit the loop.

279
16:47.640 --> 16:55.750
It cannot identify right If we do not specify the Where clause, then it cannot identify that at the

280
16:55.750 --> 17:02.410
end of the month we have to exit the loop, or at the end of the year we have to exit the loop at which

281
17:02.410 --> 17:03.730
place we have to exit.

282
17:03.730 --> 17:06.820
So this is the exist exit condition.

283
17:12.460 --> 17:15.130
That is why it is required, right.

284
17:21.880 --> 17:24.700
Now internally it is doing a loop.

285
17:25.090 --> 17:25.480
Yeah.

286
17:25.480 --> 17:26.200
Internally.

287
17:26.440 --> 17:29.170
Firstly it will take the this date right.

288
17:29.200 --> 17:31.750
Firstly it will take the this this date.

289
17:31.780 --> 17:36.160
Now next time it will add one day to this particular date.

290
17:36.160 --> 17:40.300
So next time it will be it will become this date.

291
17:40.990 --> 17:43.000
Now next time again it will add.

292
17:43.030 --> 17:44.530
It will add one more day.

293
17:44.530 --> 17:45.700
But until when.

294
17:45.730 --> 17:49.780
Until when it will add one plus one plus one plus one date.

295
17:49.810 --> 17:50.740
Until.

296
17:52.960 --> 17:53.980
Each month.

297
17:54.850 --> 17:59.080
Until this date only, it will add one one day to this particular date.

298
18:02.110 --> 18:08.290
When the next execution is there, your your increment date will be this one.

299
18:08.290 --> 18:11.920
So it will add one date into this date.

300
18:11.950 --> 18:15.280
Now again when next execution next row will be there.

301
18:15.280 --> 18:19.510
It will place this row as the current row.

302
18:19.690 --> 18:25.270
And it will add one day to this particular date, not one day to 1st August.

303
18:25.690 --> 18:30.910
Your SQL is storing this in temporary in its memory like you know to.

304
18:30.940 --> 18:39.190
Yeah, it may be storing in somewhere, but we cannot see that uh, anywhere physically.

305
18:39.190 --> 18:40.720
We cannot see that.

306
18:43.300 --> 18:46.150
Um, Arshad, uh, one question here.

307
18:46.150 --> 18:53.860
Maybe I may relate to the question here, even I'm not sure that no variable is there to store the data

308
18:53.860 --> 18:54.910
and showing it.

309
18:55.030 --> 18:58.930
If it is going in a virtual way, then virtual memory, then it is different.

310
18:59.170 --> 19:05.650
Um, can you try, uh, showing this loop kind of one, uh, you know, uh, by, say, like, you know,

311
19:05.680 --> 19:11.200
printing a specific word for a ten times or 15 times like that, if you show it so that we will understand

312
19:11.230 --> 19:12.910
how the iteration is happening.

313
19:13.030 --> 19:16.510
That's where actually, uh, iteration here.

314
19:18.400 --> 19:18.610
Okay.

315
19:18.610 --> 19:24.310
One minute I will try to give you one case study either today or tomorrow.

316
19:26.110 --> 19:29.770
So now our querying related things will be completed.

317
19:31.180 --> 19:39.640
Now t SQL kind of things we have pending like this temporary tables views all the query querying related

318
19:39.640 --> 19:41.530
part is now completed.

319
19:42.400 --> 19:46.330
Inline table valued function stored procedure that kind of things are pending.

320
19:46.330 --> 19:47.770
So we will see.

321
19:49.580 --> 19:51.080
It from today only.

322
19:51.080 --> 19:56.420
And I will try to give you one case study either today or tomorrow.

323
19:56.420 --> 20:01.910
So from that also you can check further your assessment.

324
20:01.940 --> 20:05.090
So now let's move towards the temporary tables.

325
20:08.870 --> 20:13.700
So the temporary tables are created in the temp Tempdb database.

326
20:13.700 --> 20:17.150
The temporary tables are created in the Tempdb database.

327
20:17.270 --> 20:19.760
So where is my Tempdb database.

328
20:19.760 --> 20:30.620
So if you see over here in the system system data databases master there are few databases are there.

329
20:30.620 --> 20:30.830
Right.

330
20:30.860 --> 20:33.050
Master model Msdb and temp DB.

331
20:33.560 --> 20:39.530
So now if you see over here in the temp db there will be temp temporary tables are there.

332
20:39.530 --> 20:44.150
So over here your temporary tables are created right.

333
20:44.180 --> 20:51.760
These are created to store the data temporarily, temporarily and they can perform, create, read,

334
20:51.790 --> 20:58.450
update, delete, join operations like we do with normal tables like we do with normal tables.

335
20:58.450 --> 21:05.530
They have drop when the session that created that is closed, and they can also be dropped explicitly

336
21:05.530 --> 21:07.690
by the user using the drop statement.

337
21:07.990 --> 21:10.570
Now there are total two types of temporary tables.

338
21:10.570 --> 21:13.810
Are there local temporary tables and global temporary tables.

339
21:14.800 --> 21:21.250
So the scope of the local temporary table is limited by the connection in which this is created.

340
21:21.730 --> 21:27.250
The local temp tables are visible for the duration of the connection, and when the session is closed,

341
21:27.880 --> 21:31.120
the local temp table is dropped automatically.

342
21:31.990 --> 21:40.450
Now the local temp table can be created using the create statement, but we have to use a symbol in

343
21:40.450 --> 21:41.650
front of the temp table.

344
21:41.650 --> 21:46.540
So let me let me first drop this table.

345
21:52.300 --> 21:55.360
I think I have already created it before some time.

346
21:55.360 --> 22:01.210
So if you want to create the local temporary table, then you have to mention like this hash sign.

347
22:01.570 --> 22:03.790
And then you have to mention the column names.

348
22:03.790 --> 22:04.090
Right.

349
22:04.090 --> 22:05.710
You have to mention column names.

350
22:05.800 --> 22:09.250
So this one will become my local temp temporary table.

351
22:09.250 --> 22:11.830
And you can insert data also into the same.

352
22:12.850 --> 22:13.120
Right.

353
22:13.120 --> 22:18.970
So now if I show this one then it will give me result also.

354
22:18.970 --> 22:19.270
Right.

355
22:19.300 --> 22:21.130
It will give me result also.

356
22:22.240 --> 22:24.970
And now where we have to see the temporary table.

357
22:24.970 --> 22:27.130
So let me refresh this over here.

358
22:30.340 --> 22:36.220
In the database system database Tempdb temporary tables.

359
22:36.460 --> 22:38.320
So if you see over here.

360
22:41.410 --> 22:50.140
They are giving some random names random names to the Temporary tables by automatically by the SQL server.

361
22:50.170 --> 22:51.640
This is our actual table.

362
22:51.670 --> 22:52.090
DB.

363
22:54.880 --> 22:56.410
DB dot start.

364
22:56.440 --> 22:58.510
DB db is the default schema, right?

365
22:58.540 --> 23:00.220
DBO is the default schema.

366
23:00.490 --> 23:05.980
So whenever we do not specify any schema then it will become automatically created in the dbo schema.

367
23:06.010 --> 23:06.640
Right.

368
23:07.600 --> 23:11.410
So this is how local temporary tables we can create.

369
23:11.410 --> 23:15.880
And we can create the local temporary tables by by this query also.

370
23:15.880 --> 23:17.260
So what it will do.

371
23:19.870 --> 23:25.510
It will create some kind of table whatever the name we are giving.

372
23:28.330 --> 23:30.340
Whatever the name we are giving.

373
23:30.670 --> 23:33.760
And it will take data from this particular table.

374
23:34.420 --> 23:36.730
So this will this one.

375
23:45.010 --> 23:46.870
This one will become my inner temple.

376
23:46.900 --> 23:48.310
Right inner temple.

377
23:48.340 --> 23:52.510
So from the subquery we have created this table select star into.

378
23:52.720 --> 23:57.190
So I have told you in the very first lecture that we can create the table like this.

379
23:57.190 --> 23:59.020
Also select star into.

380
23:59.440 --> 24:05.740
If we do not specify this hash sign then it will create this table as a physical table.

381
24:05.950 --> 24:14.920
But if we do specify this hash sign then this will create one one table with local temporary table.

382
24:14.920 --> 24:18.790
So if you now let me again refresh this one.

383
24:21.490 --> 24:23.560
And let me show you over here.

384
24:25.720 --> 24:29.440
See again second table will be created in temporary tables only.

385
24:29.440 --> 24:30.040
Right.

386
24:30.310 --> 24:33.430
But now it will it will have data automatically.

387
24:33.460 --> 24:34.030
Why?

388
24:34.420 --> 24:38.320
Because this inner query will be giving us the data.

389
24:38.350 --> 24:38.590
Right.

390
24:38.620 --> 24:44.020
So it is giving data over here like the same Right.

391
24:44.770 --> 24:49.840
If we do not specify the where condition, then it will given given me and or.

392
24:54.850 --> 24:56.410
It will give me entire data.

393
24:56.440 --> 24:56.650
Right?

394
24:56.650 --> 25:01.270
It will give me entire data over here right now.

395
25:01.810 --> 25:03.580
Local temporary tables.

396
25:03.580 --> 25:06.700
We cannot execute.

397
25:06.700 --> 25:11.260
This is you can say in the SQL server, this one will become my one session.

398
25:11.590 --> 25:13.990
This one will become my second session.

399
25:13.990 --> 25:22.930
So if you run over here, you cannot identify this particular object because local temporary table will

400
25:22.960 --> 25:26.020
be executed into some particular session only.

401
25:26.740 --> 25:27.010
Right.

402
25:27.040 --> 25:33.220
Local temporary table will be when the session is closed or when the new session will be started.

403
25:33.220 --> 25:37.540
It will not become available in the next session, right?

404
25:38.020 --> 25:41.410
It will not become available now.

405
25:41.800 --> 25:43.660
Global temporary tables.

406
25:43.960 --> 25:48.430
so we can create the global temp tables like local temp tables.

407
25:48.430 --> 25:49.000
But the.

408
25:49.030 --> 25:52.000
Table name will be prefixed by double hash.

409
25:52.540 --> 25:55.570
And these tables can be accessed by all other sessions.

410
25:55.570 --> 26:01.660
Unlike local ones, these tables can be accessed by all other sessions.

411
26:01.660 --> 26:04.510
Unlike local ones, we cannot.

412
26:04.540 --> 26:06.970
We can drop the global temp tables.

413
26:06.970 --> 26:13.480
We can drop the with the drop statement in any of the session, and also it is automatically dropped

414
26:13.480 --> 26:16.210
when the session is closed.

415
26:16.660 --> 26:21.790
That created this global temp table if it is in use.

416
26:21.820 --> 26:22.480
Other session.

417
26:22.480 --> 26:23.950
We are closing the session.

418
26:25.210 --> 26:29.830
SQL server waits until the completion of the last SQL statement activity.

419
26:31.360 --> 26:35.740
So now this is how we can create the global temp table.

420
26:35.740 --> 26:39.640
So let me show you this is my global temp table.

421
26:39.640 --> 26:46.210
We can insert the data also into the same And the global temp table will be available over here as well.

422
26:46.540 --> 26:47.800
In the next session.

423
26:47.800 --> 26:54.280
So if me that is the difference between local temp table and global temp table.

424
26:55.060 --> 26:56.770
So it is available over here.

425
26:56.770 --> 27:02.110
But now let me try to close this session.

426
27:04.780 --> 27:07.150
Let me copy this.

427
27:10.600 --> 27:16.300
And let me try to I think it should be available over here as well.

428
27:16.300 --> 27:17.110
Local.

429
27:17.740 --> 27:18.250
Okay.

430
27:18.250 --> 27:24.100
Because of both the sessions has been closed in which the global temporary table is created.

431
27:24.100 --> 27:26.140
Both the sessions is closed.

432
27:26.320 --> 27:32.590
So now that table you cannot identify over here because of that reason, you cannot identify over here.

433
27:32.830 --> 27:41.890
Or again if you open the new SSMs, if you open the new SSMs, then again you cannot identify this table

434
27:41.890 --> 27:47.720
as well as the one which we have created locally, which we have created locally.

435
27:55.370 --> 28:00.020
Right now, let me know if you have any questions.

436
28:05.840 --> 28:07.640
How is the table is also.

437
28:08.540 --> 28:14.240
So the local local temporary tables are available to this particular sessions only.

438
28:14.270 --> 28:17.750
Its scope is available to this particular sessions only.

439
28:18.020 --> 28:23.090
No other user, no other user or no in no other session.

440
28:23.480 --> 28:25.850
We cannot use this particular table.

441
28:25.850 --> 28:28.010
We cannot use this particular table.

442
28:28.160 --> 28:36.560
Whereas global temporary table will be available to the next how many sessions you have used, right?

443
28:36.590 --> 28:41.630
So let me try to create this global temporary table again.

444
28:43.850 --> 28:46.100
This is my global temporary table.

445
28:47.210 --> 28:51.620
And let me try to see over here.

446
28:51.650 --> 28:52.730
Create this.

447
28:53.180 --> 28:59.300
Now this particular table will be available to this session also.

448
29:02.900 --> 29:04.400
To this session also.

449
29:04.430 --> 29:08.030
Also as well as to the next sessions also.

450
29:10.100 --> 29:15.110
But now if I open the new SQL server this is my new SQL server.

451
29:16.130 --> 29:26.420
And now if I try to execute this table then I cannot find that table over here.

452
29:31.940 --> 29:35.480
Then I cannot find okay it is available.

453
29:38.600 --> 29:39.440
Okay.

454
29:39.470 --> 29:41.510
This is new for me also.

455
29:41.690 --> 29:44.990
But if I close now, if you see over here.

456
29:44.990 --> 29:45.920
If I close now.

457
29:46.130 --> 29:51.590
But now, if you see one minute now, let's do it for local temporary table.

458
29:52.280 --> 29:54.770
Let's do it for local temporary table.

459
29:59.600 --> 30:01.310
This is my local temporary table.

460
30:01.340 --> 30:02.540
Now let's do.

461
30:04.730 --> 30:06.170
See over here.

462
30:06.830 --> 30:11.660
Select star from this one.

463
30:13.070 --> 30:14.840
It will not be available here.

464
30:14.840 --> 30:17.120
Also it will not be available here also.

465
30:17.120 --> 30:17.690
Right.

466
30:18.650 --> 30:22.130
It will not be available here also in the new SQL server.

467
30:22.250 --> 30:31.130
But now the thing is, if I close this particular session in which the global and local temporary table

468
30:31.160 --> 30:33.590
both are created, let me close it again.

469
30:35.780 --> 30:42.350
And now if I try to run this query, if I try to run this query.

470
30:47.090 --> 30:48.680
See over here.

471
30:50.030 --> 30:51.350
It is not over here.

472
30:51.350 --> 30:51.620
Right?

473
30:51.620 --> 30:53.000
It is not over here.

474
31:01.670 --> 31:04.040
So local time the scope is different.

475
31:04.040 --> 31:05.900
Only the scope is different.

476
31:06.080 --> 31:12.470
Local temporary table you can access in the particular session only in which it is created, whereas

477
31:12.470 --> 31:19.970
global temporary table can be accessed from all other sessions also in which it is not created.

478
31:19.970 --> 31:27.290
But when you close the particular session in which local and global temporary table both are created

479
31:27.470 --> 31:34.610
at that time, the local and global, both the temporary tables will be gone, will be gone.

480
31:34.640 --> 31:40.970
And now if you write the sometimes if you see in your company, if you are working in the SQL server,

481
31:41.390 --> 31:48.950
they are using the temporary tables, local temporary tables many a times while doing the store procedure

482
31:48.980 --> 31:50.810
while creating the store procedure.

483
31:51.380 --> 31:55.490
Because they will create the local temporary table, they will do some performance.

484
31:55.520 --> 32:03.050
Do do some DML operations into that particular store procedure, and after the DML operation they will

485
32:03.050 --> 32:06.290
automatically drop the local temporary table.

486
32:06.320 --> 32:06.680
Why?

487
32:06.710 --> 32:14.840
Because if I do not drop that temporary tables, then it will throw error to some other user that this

488
32:14.840 --> 32:17.570
table is not exist.

489
32:17.600 --> 32:18.020
Why?

490
32:18.050 --> 32:22.400
Because it will be automatically dropped right when the session is closed.

491
32:22.430 --> 32:25.460
So their stored procedure will be failed.

492
32:26.240 --> 32:30.320
The stored procedure will be failed if you do not drop by yourself, right?

493
32:30.680 --> 32:32.510
In the same store procedure?

494
32:35.000 --> 32:37.190
Yes, I understood the concept.

495
32:37.220 --> 32:40.130
Now, uh as.