1
00:02.720 --> 00:03.230
Okay.

2
00:03.230 --> 00:06.500
So today we are starting with indexes.

3
00:08.720 --> 00:14.750
So what we can say is an index one minute.

4
00:20.270 --> 00:28.910
So index index is an is a database object which is created to improve the performance of data accessing

5
00:28.910 --> 00:30.440
or data searching.

6
00:31.580 --> 00:31.760
Right.

7
00:31.790 --> 00:41.780
So whenever we are writing some SQL queries which is complex or anything, but it has a huge amount

8
00:41.780 --> 00:49.520
of data, then to improve the performance of the select statement or data retrieving, we are usually

9
00:49.520 --> 00:51.230
creating the indexes.

10
00:52.550 --> 00:57.230
You can say that index in a db is similar to index in the text books.

11
00:57.290 --> 01:05.020
So in the text books also index are coming right at the back side as well and in the front side as well.

12
01:05.380 --> 01:13.060
So in the textbook, with the help of index we can quickly search for a particular topic just as same

13
01:13.060 --> 01:13.600
over here.

14
01:13.600 --> 01:21.040
Also we can if we have created the index then we can quickly search the specific data.

15
01:21.220 --> 01:29.320
Now whenever when we write a query like select star from EMP where department number equal to 30, then

16
01:29.350 --> 01:33.850
SQL server may use the following methods to locate the record.

17
01:34.570 --> 01:42.880
So when we are executing this kind of query specifically or any kind of query, this is just one example.

18
01:47.140 --> 01:48.670
One is table scan.

19
01:48.700 --> 01:50.230
So what is a table scan.

20
01:50.260 --> 01:58.030
Table scan where each and every row of the table is scanned for a record in the table scan, entire

21
01:58.050 --> 02:04.350
table will be scanned, but all the rows from the entire table will be scanned one by one.

22
02:04.380 --> 02:07.740
So it will usually slow in performance.

23
02:07.740 --> 02:15.240
We can say it is usually slow in performance, whereas index scan it will improve the performance in

24
02:15.240 --> 02:16.470
locating the record.

25
02:16.470 --> 02:19.560
It will improve the performance in locating the record.

26
02:19.830 --> 02:22.290
The record will be located quickly.

27
02:23.760 --> 02:27.810
Now SQL server can store the data in database in two manners.

28
02:28.020 --> 02:30.060
One is unordered data.

29
02:30.090 --> 02:31.020
Unordered data.

30
02:31.020 --> 02:34.140
So whenever any data will be there in the table.

31
02:35.370 --> 02:42.330
Suppose we have this EMP table and emp id name.

32
02:43.830 --> 02:47.730
We can say salary, so it will be like anything any order.

33
02:47.940 --> 02:50.370
This is called an unordered data right.

34
02:50.370 --> 02:54.360
This is called unordered data name is anything like age.

35
02:58.040 --> 03:04.100
Be a and salary.

36
03:04.100 --> 03:05.960
We can say anything, right ten.

37
03:18.740 --> 03:21.110
So this is we can say unordered data.

38
03:21.110 --> 03:25.280
And it is also can we can say that heap structure.

39
03:25.310 --> 03:26.300
Heap structure.

40
03:26.330 --> 03:31.760
In the same heap structure data will be unordered and ordered or sorted data.

41
03:31.760 --> 03:37.790
So whenever all this data will be in the sorted format like employee ID will come first, then employee

42
03:37.790 --> 03:43.160
ID will come second, employee two will become second, then employee three will become third, employee

43
03:43.190 --> 03:44.570
four will become fourth.

44
03:44.570 --> 03:46.220
So it is a sorted data.

45
03:46.220 --> 03:48.380
So when will be the data will be sorted.

46
03:48.410 --> 03:54.980
Whenever we are creating the cluster index on any table, then normally it will be the data will be

47
03:54.980 --> 04:00.740
in the sorted order that can say order data or sorted data.

48
04:03.170 --> 04:09.260
Now we have mainly total two different kind of indexes are there.

49
04:09.710 --> 04:11.510
One is we can say.

50
04:14.780 --> 04:15.890
Clustered index.

51
04:15.890 --> 04:17.660
And second we can say non.

52
04:20.300 --> 04:21.560
Non-Clustered index.

53
04:21.590 --> 04:21.830
Right.

54
04:21.860 --> 04:24.500
Cluster index and non-clustered index.

55
04:24.590 --> 04:27.770
Now should I always create clustered index.

56
04:28.460 --> 04:31.580
So usually the answer is no.

57
04:31.580 --> 04:34.490
This is one interview question we can say.

58
04:34.520 --> 04:36.650
Should I always create the cluster index.

59
04:36.650 --> 04:41.270
So whenever when the creating the cluster index is useful.

60
04:41.570 --> 04:49.460
Whenever there is a less amount of insert, update and delete statement on, some particular tables

61
04:49.490 --> 04:50.690
are happening.

62
04:51.080 --> 04:56.700
So when the table has lots of insert update, delete, Our table is highly transactional.

63
04:57.030 --> 05:00.180
At that time, our table is highly transactional.

64
05:00.420 --> 05:04.350
Then creating cluster index actually degrades the performance.

65
05:05.130 --> 05:07.830
It will actually degrade the performance.

66
05:07.950 --> 05:11.280
Cluster index is good when the cluster index is good.

67
05:11.370 --> 05:16.620
When you know that my table has many data access operations.

68
05:16.920 --> 05:25.380
Data access operations means many Select statements or we are using that particular table in many reporting

69
05:25.380 --> 05:25.740
purpose.

70
05:25.770 --> 05:26.010
Right.

71
05:26.040 --> 05:26.970
Reporting purpose.

72
05:26.970 --> 05:31.440
We are selecting data from that particular table at that time.

73
05:31.440 --> 05:33.930
Creating the cluster index is useful.

74
05:34.920 --> 05:41.400
But when we are having insert, update and delete statement on that particular table then it will degrade

75
05:41.400 --> 05:42.210
the performance.

76
05:42.240 --> 05:45.000
It is better not to use cluster index.

77
05:46.980 --> 05:50.490
Now how many cluster index we can create per table?

78
05:50.520 --> 05:51.420
How many?

79
05:51.480 --> 05:55.860
So usually we can create only one cluster index per table.

80
05:56.520 --> 06:04.890
So if we need if we need search on the basis of any other column, then we have to use non-clustered

81
06:04.890 --> 06:05.670
index.

82
06:07.140 --> 06:09.510
So there is another index is there.

83
06:09.510 --> 06:09.810
Right.

84
06:09.810 --> 06:11.190
That is non-clustered index.

85
06:11.190 --> 06:12.960
So one.

86
06:16.140 --> 06:19.140
Cluster index per table.

87
06:19.560 --> 06:21.930
And what is the limit for non-clustered index.

88
06:21.930 --> 06:25.260
We can say 9999.

89
06:31.350 --> 06:32.310
Per table.

90
06:32.310 --> 06:35.760
And how how many.

91
06:39.420 --> 06:45.060
Total indexes we can create on any.

92
06:47.970 --> 06:49.500
Specific table.

93
06:51.030 --> 06:54.980
So answer will be one plus nine, nine, nine.

94
06:55.010 --> 06:56.840
That will be 1000, right?

95
06:56.870 --> 06:57.950
1000.

96
07:00.170 --> 07:01.610
Now let's.

97
07:04.760 --> 07:07.910
So there are three kind of scans available.

98
07:07.910 --> 07:10.640
Now what are the scans.

99
07:10.640 --> 07:14.180
So scanning will be scanning of of any particular table.

100
07:14.210 --> 07:14.810
Right.

101
07:15.260 --> 07:18.440
Scanning of any particular table.

102
07:18.500 --> 07:22.520
So how many total scans are available.

103
07:24.890 --> 07:27.560
We can say there are three table scan.

104
07:27.590 --> 07:30.950
So we will scan every row of our table.

105
07:31.370 --> 07:38.180
When we have a heap table we will scan every row of a table when we have a heap table.

106
07:38.180 --> 07:44.870
So in this particular heap table it will happen.

107
07:46.910 --> 07:48.170
Table scan.

108
07:52.390 --> 07:54.100
It means we are.

109
07:56.380 --> 07:58.330
Each row of the table.

110
07:58.360 --> 07:58.690
Right?

111
07:58.720 --> 08:00.040
Each row of the table.

112
08:00.070 --> 08:02.410
So it is usually slow, right?

113
08:02.440 --> 08:04.240
Usually slow in performance.

114
08:04.450 --> 08:07.900
Now index can.

115
08:07.900 --> 08:17.290
So when we create cluster index index can will be happen when we don't have any filtering predicate

116
08:17.290 --> 08:19.720
in where clause select star from EMP.

117
08:19.960 --> 08:24.010
So when we have this kind of table select star from EMP.

118
08:24.520 --> 08:28.000
And we do not have any where clause over here.

119
08:28.480 --> 08:36.100
So at that time we can also create a cluster index on particular column which is usually ID column right.

120
08:36.100 --> 08:38.110
Which is usually id column.

121
08:38.200 --> 08:45.490
And if we create the cluster index on the id column then it will happen index scan.

122
08:45.520 --> 08:49.890
It will happen index scan because of cluster index are there Right.

123
08:52.470 --> 08:54.180
And what is index seek.

124
08:57.330 --> 09:02.700
So it is a fasted mode of scanning when we want to select few rows from the table.

125
09:02.730 --> 09:12.900
And how we can how we can select the few rows from the table by by specifying where clause right by

126
09:12.900 --> 09:14.430
specifying where clause.

127
09:14.460 --> 09:21.420
So whenever where clause will be there, we can own a column of where clause it will happen index seek.

128
09:21.450 --> 09:25.140
If we create index on the column of a Where clause it will happen.

129
09:25.140 --> 09:25.620
Index.

130
09:25.650 --> 09:26.250
Seek.

131
09:27.600 --> 09:28.590
Now.

132
09:30.900 --> 09:33.090
Cluster index we will create a primary key.

133
09:33.090 --> 09:40.320
So when we cluster index will be created when we create a primary key on any specific table.

134
09:40.860 --> 09:47.790
When we create a primary key on any specific table at that time, cluster index is automatically created

135
09:47.810 --> 09:50.630
on the peak, a unique cluster index is created.

136
09:50.810 --> 09:54.800
Now let me drop this table.

137
10:03.650 --> 10:07.610
Now I am creating this table with only two columns.

138
10:10.700 --> 10:15.980
Now if I see this one, if I see, how can I see the table structure?

139
10:16.010 --> 10:18.560
So SP underscore help.

140
10:21.800 --> 10:23.030
Then this table name.

141
10:23.030 --> 10:24.560
Let me check.

142
10:28.310 --> 10:33.320
See over here what we have created.

143
10:33.320 --> 10:36.290
We have created a primary key constraint.

144
10:36.320 --> 10:38.090
So this is my constraint.

145
10:38.720 --> 10:46.550
But now if you see over here this is an index I told right index will be automatically created.

146
10:46.550 --> 10:48.790
index will be automatically created.

147
10:49.090 --> 10:54.850
So see it is created this index, it will give the index name automatically.

148
10:55.420 --> 10:57.310
It will give the index name automatically.

149
10:57.310 --> 10:57.880
Cluster.

150
10:57.910 --> 11:04.030
Unique primary key located on primary ID column ID column right.

151
11:04.060 --> 11:06.340
Because primary key is on id column.

152
11:06.340 --> 11:09.640
So index will be created also on id id column.

153
11:09.760 --> 11:12.610
Now another way how we can see over here.

154
11:12.610 --> 11:14.110
Let me show you.

155
11:17.020 --> 11:19.540
Tables refresh.

156
11:21.220 --> 11:22.690
Let me.

157
11:28.510 --> 11:31.960
It is AT1 hundred right 300.

158
11:34.810 --> 11:37.210
You have to over here also index.

159
11:37.210 --> 11:40.060
You are seeing right over here also index.

160
11:40.060 --> 11:41.230
You are able to see.

161
11:41.260 --> 11:47.220
Then if you see over here This is.

162
11:51.540 --> 11:53.700
This is same as what we have created.

163
11:53.700 --> 12:01.020
If you now do the right click let me try new index or script is create to internally.

164
12:01.020 --> 12:02.490
This will automatically happen.

165
12:02.520 --> 12:04.440
See now it will come.

166
12:07.170 --> 12:08.250
Alter table.

167
12:08.250 --> 12:14.820
This table we have created right over here 200 table we have created now 200 table.

168
12:15.540 --> 12:16.560
Add primary key.

169
12:16.560 --> 12:22.110
Clustered index is created on this particular column.

170
12:22.110 --> 12:24.960
And this is something something.

171
12:28.380 --> 12:29.460
Is there right.

172
12:29.760 --> 12:31.950
So this will automatically happen.

173
12:32.370 --> 12:35.040
Now let's move ahead.

174
12:42.900 --> 12:49.490
Now if we want to create a cluster index explicitly, it means manually.

175
12:50.090 --> 12:52.010
Then also it is possible, right?

176
12:52.010 --> 12:53.870
Then also it is possible.

177
12:53.870 --> 12:55.760
So drop table.

178
12:58.400 --> 12:59.330
Table name.

179
12:59.330 --> 13:00.050
Drop table.

180
13:00.080 --> 13:01.040
Table name.

181
13:02.120 --> 13:04.520
Now see there is no primary key over here right.

182
13:04.550 --> 13:06.470
There is no primary key over here.

183
13:06.800 --> 13:08.930
Now create cluster index.

184
13:08.930 --> 13:15.950
Create cluster index i5 on t 200 id t 200 id.

185
13:16.550 --> 13:20.480
So let me create this will create cluster index.

186
13:20.480 --> 13:24.830
Now let me show you ESP help index.

187
13:24.920 --> 13:32.570
So this will help me directly located to this index right.

188
13:32.600 --> 13:35.450
What is the name okay.

189
13:35.480 --> 13:37.220
Index name we have created over here.

190
13:37.220 --> 13:38.540
That is i5.

191
13:39.890 --> 13:42.850
Now if I do over here ESP SP help.

192
13:47.680 --> 13:51.250
You can check over here as well if I will be there.

193
13:51.280 --> 13:54.820
C i5 is there index.

194
13:54.820 --> 13:55.450
Description.

195
13:55.600 --> 13:55.960
Index.

196
13:55.960 --> 13:56.500
Name.

197
13:56.650 --> 13:57.250
Index.

198
13:57.250 --> 13:57.940
Description.

199
13:57.970 --> 13:58.420
Index.

200
13:58.420 --> 13:59.380
On which column?

201
13:59.410 --> 14:00.460
ID column.

202
14:08.110 --> 14:10.960
Now we have ID column and name.

203
14:10.960 --> 14:13.750
Column are there in this two table right.

204
14:14.230 --> 14:15.520
T 200.

205
14:15.550 --> 14:18.310
So we can create non-clustered index as well.

206
14:18.310 --> 14:19.090
Like this.

207
14:19.090 --> 14:23.260
Create non-clustered index i6 on t.

208
14:28.300 --> 14:34.540
Now if I if I mention this one uh let me use this one.

209
14:34.930 --> 14:39.310
See over here it will show me non-clustered index as well.

210
14:39.790 --> 14:42.330
And which is I have created on in-game column, right?

211
14:42.360 --> 14:43.380
In-game column.

212
14:44.280 --> 14:50.310
Now, if I do help, spell on this table over here.

213
14:50.310 --> 14:53.430
Also a second index will be there, right?

214
14:54.210 --> 14:56.190
Which is created on in-game column.

215
14:56.190 --> 14:56.760
Right.

216
14:59.910 --> 15:05.640
So this is how we can say cluster and non-clustered index.

217
15:05.640 --> 15:06.510
We can create.

218
15:06.540 --> 15:07.500
Now.

219
15:11.790 --> 15:13.860
Do I have any data in this table.

220
15:13.860 --> 15:16.320
Let me check.

221
15:20.880 --> 15:23.130
Select star from student.

222
15:25.980 --> 15:26.880
Okay.

223
15:29.160 --> 15:30.840
Now covering index.

224
15:30.870 --> 15:35.520
Now covering index two to improve the search performance.

225
15:35.580 --> 15:42.860
Now one thing more we can say that is covering index are available in SQL server to improve the search

226
15:42.860 --> 15:43.760
performance.

227
15:43.760 --> 15:51.950
So now if I mention query like this one, or if I mention query like this one, select name from student.

228
15:51.950 --> 15:52.910
Where mobile.

229
15:58.280 --> 15:58.580
Okay.

230
15:58.580 --> 16:00.050
Over here Department.

231
16:03.590 --> 16:04.070
Okay.

232
16:04.070 --> 16:06.170
Let's see this example directly.

233
16:06.230 --> 16:12.530
If I mention select ename from emp where ename equal to John.

234
16:12.530 --> 16:13.100
So.

235
16:16.070 --> 16:17.090
One minute.

236
16:33.530 --> 16:34.610
It is John's right.

237
16:34.640 --> 16:36.290
It is John's.

238
16:53.830 --> 16:54.490
Okay.

239
16:54.520 --> 16:57.460
Now, in this case the name is a key column.

240
16:58.240 --> 17:05.350
In this particular particular case your name is a key column and job is a non key column.

241
17:08.080 --> 17:13.210
And to improve the performance or avoid key lookup we can include job.

242
17:13.210 --> 17:15.580
So what is a covering index.

243
17:15.820 --> 17:23.500
Usually while creating the index in while creating the index on the name name column.

244
17:23.800 --> 17:31.990
While creating the index on the name column, we can include another column which is available in the

245
17:31.990 --> 17:33.160
select statement.

246
17:34.390 --> 17:39.250
We can include another column while creating the index.

247
17:39.790 --> 17:45.940
Apart from the column which is mentioned in the Where clause, so to include another column which is

248
17:45.940 --> 17:49.480
mentioned in the Select clause, we have to specify include clause.

249
17:49.870 --> 17:52.360
We have to specify include clause.

250
17:52.360 --> 17:57.010
So how this is how we can create this index.

251
17:57.820 --> 17:59.560
Let me drop drop.

252
18:07.780 --> 18:09.040
I seven okay.

253
18:09.070 --> 18:10.150
It is created.

254
18:10.480 --> 18:11.560
Now.

255
18:15.010 --> 18:19.510
This I seven index is covering index for above query.

256
18:19.540 --> 18:22.360
So now this I seven index we can say this.

257
18:22.390 --> 18:25.810
This is a covering index for this query.

258
18:25.900 --> 18:29.560
Why it is covering because it includes.

259
18:29.590 --> 18:37.740
It is including the column which is mentioned apart from the Where clause It is including the column

260
18:37.740 --> 18:40.140
which is mentioned apart from the Where clause.

261
18:40.230 --> 18:41.580
That is why it is.

262
18:41.610 --> 18:45.630
We can say that covering covering it is covering, right?

263
18:45.660 --> 18:46.710
That is why it is.

264
18:46.740 --> 18:48.540
We can say that covering index.

265
18:52.380 --> 18:59.370
See over here to improve the performance or avoid key lookup, we can include job when we create a non-clustered

266
18:59.370 --> 19:02.400
index to fetch result of particular query.

267
19:03.180 --> 19:06.990
SQL server uses key lookup to retrieve non-key data.

268
19:08.160 --> 19:12.960
Key lookup so it will lookup on this particular table only.

269
19:12.990 --> 19:17.520
So sorry particular column only because it is a key column.

270
19:18.780 --> 19:25.140
And to retrieve non key data to covering index is an index that contains all information to resolve

271
19:25.140 --> 19:33.420
the query by including the non-key columns in a non-clustered index, we can dramatically improve the

272
19:33.420 --> 19:34.050
performance.

273
19:34.050 --> 19:41.240
So by this particular way, by this particular creation of index, we can dramatically improve the performance.

274
19:48.680 --> 19:51.050
Now how we can alter the index.

275
19:51.050 --> 19:59.030
One thing we can mention like alter index index name on table name, we can disable the existing index

276
19:59.420 --> 20:01.250
as well as disabled index.

277
20:01.370 --> 20:03.050
We can enable also.

278
20:03.050 --> 20:07.640
So once the index is disabled we can also do the enable also.

279
20:08.390 --> 20:11.150
This is how we can drop the index as well.

280
20:12.860 --> 20:15.500
Let me try to disable this index.

281
20:15.500 --> 20:16.730
Let me try.

282
20:22.610 --> 20:24.170
Alter index.

283
20:27.110 --> 20:31.370
On EMP disable.

284
20:31.370 --> 20:32.570
Let me try.

285
20:42.760 --> 20:44.440
See now the index.

286
20:44.470 --> 20:44.980
Index.

287
20:44.980 --> 20:49.360
Whatever we have created, this i7 is now disabled.

288
20:51.610 --> 20:58.450
And we can enable the same index as well by writing the enable command.

289
21:10.060 --> 21:12.760
I don't know why it is not working.

290
21:12.790 --> 21:13.960
Enable.

291
21:15.670 --> 21:16.630
One minute.

292
21:19.060 --> 21:20.170
See over here.

293
21:20.170 --> 21:22.270
It is mentioned over here as well.

294
21:22.810 --> 21:28.450
If we want to to enable all the indexes on a table using alter index.

295
21:28.540 --> 21:32.410
So alter index all on particular table rebuilt.

296
21:32.430 --> 21:42.990
So if we do rebuild then it will do the enable the disabled index table using Dbcc.

297
21:45.480 --> 21:46.440
Okay.

298
21:50.370 --> 21:53.400
And now one more that that is a covering index.

299
21:53.400 --> 21:57.240
And now one more thing is we can say as a filter index.

300
21:57.420 --> 22:01.530
So it is a non-clustered index with a with where clause.

301
22:02.010 --> 22:07.380
So to improve the performance or avoid a key lookup we can include job.

302
22:07.590 --> 22:14.400
So create index i8 on MP salary where salary greater than equal to five.

303
22:16.830 --> 22:23.460
So this is we can say the filter index by filter because we have specified the Where clause over here

304
22:23.460 --> 22:25.140
while creating the index.

305
22:30.060 --> 22:31.980
Now let me unmute you.