1
00:00:00,180 --> 00:00:00,670
Okay.

2
00:00:01,020 --> 00:00:04,120
So we're moving on now and it's time for a brand new section.

3
00:00:04,140 --> 00:00:09,600
In this case, we're spending some time revisiting something we've already discussed, which is data

4
00:00:09,600 --> 00:00:10,190
types.

5
00:00:10,200 --> 00:00:16,290
So very early on, one of the first videos, once we got everything set up and installed, one of the

6
00:00:16,290 --> 00:00:22,440
first videos was on very basic data types and we talked about how data takes different forms and you

7
00:00:22,440 --> 00:00:26,520
have text and numbers and I kind of left it at that.

8
00:00:26,760 --> 00:00:32,460
We talked about var char and int are 2/1 data types, but I showed you that there are a bunch more.

9
00:00:33,180 --> 00:00:36,720
Well, now it's a time that I promised we'd revisit.

10
00:00:36,840 --> 00:00:42,900
We'd return to those data types and go into a bit more detail in depth around some of the important

11
00:00:42,900 --> 00:00:43,350
data types.

12
00:00:43,350 --> 00:00:49,170
So we're still not going to go over every single one because a lot of them are just not used and some

13
00:00:49,170 --> 00:00:55,230
of them are very, very similar with tiny differences that honestly won't make that aren't relevant

14
00:00:55,230 --> 00:00:55,800
to this course.

15
00:00:55,800 --> 00:01:00,780
So what we're going to do is focus on what will have the highest impact for you, what has the highest

16
00:01:00,780 --> 00:01:07,020
impact in my developer life and the people that I know and have to talk to about how they use my SQL.

17
00:01:07,800 --> 00:01:13,050
So basically it boils down to a couple of different data types and a couple of different broad categories.

18
00:01:13,050 --> 00:01:14,460
So the first category.

19
00:01:15,530 --> 00:01:18,620
It's text or string data types.

20
00:01:19,040 --> 00:01:21,530
And then we'll also talk about numeric data types.

21
00:01:21,530 --> 00:01:24,440
We have a lot to talk about there because we've only worked with iNts.

22
00:01:24,470 --> 00:01:30,470
We need to talk about how we store decimal points or how we store non whole numbers.

23
00:01:30,560 --> 00:01:32,300
I can't remember the term for that.

24
00:01:32,840 --> 00:01:37,460
And also we need to talk about how we store things like dates and times.

25
00:01:37,610 --> 00:01:43,940
Really important part of using a database is typically at least in web applications or applications

26
00:01:43,940 --> 00:01:48,920
in general, you're usually storing some sort of date and time information about when a transaction

27
00:01:48,920 --> 00:01:53,330
happens, when somebody signs up for your site the last time something was updated and so on.

28
00:01:53,330 --> 00:01:55,040
So we'll be discussing that as well.

29
00:01:55,040 --> 00:01:59,090
But for now in this video, we're focusing on storing text.

30
00:01:59,660 --> 00:02:05,330
So as I mentioned, we've already seen var char or var car, however you want to say it.

31
00:02:05,660 --> 00:02:08,660
I think most people actually say var car, but.

32
00:02:09,539 --> 00:02:10,380
I don't know.

33
00:02:10,620 --> 00:02:11,830
I save our char.

34
00:02:12,180 --> 00:02:13,140
Doesn't really make sense.

35
00:02:13,140 --> 00:02:15,630
I guess you say character, not character.

36
00:02:15,870 --> 00:02:18,270
All right, so we've already talked about it.

37
00:02:18,270 --> 00:02:20,190
It's what we've been using to store text.

38
00:02:20,490 --> 00:02:27,630
But there's also char or car, which is an entirely different separate data type.

39
00:02:28,020 --> 00:02:32,220
Now, they both store text, but there's one really key difference.

40
00:02:33,330 --> 00:02:34,920
Well, what is that difference?

41
00:02:36,400 --> 00:02:38,530
Char has a fixed length.

42
00:02:38,530 --> 00:02:44,890
So when we specify, let's say we're working on let's say we're working with our books table and we

43
00:02:44,890 --> 00:02:51,040
want to have a column called Title We could have right now it's a var char in our existing books table,

44
00:02:51,040 --> 00:02:58,570
but we could specify it's a char and give it a fixed length of five for title.

45
00:02:58,810 --> 00:03:04,690
And what that means is that every title that we add in is going to be five characters.

46
00:03:05,170 --> 00:03:09,070
So if it's greater than five characters, if it's ten characters, it's going to be truncated, it's

47
00:03:09,070 --> 00:03:11,590
going to get chopped at that five character mark.

48
00:03:11,590 --> 00:03:14,650
And if it's fewer, you'll see in a moment what it does.

49
00:03:14,650 --> 00:03:18,100
It actually add spaces to make it five characters.

50
00:03:18,340 --> 00:03:25,180
So Char will always allocate the same amount of space for every row or every instance of that char versus

51
00:03:25,180 --> 00:03:25,450
var.

52
00:03:25,450 --> 00:03:26,710
Char can vary.

53
00:03:26,710 --> 00:03:32,710
And as you can see here, if we did something like char three, only three characters are allowed in

54
00:03:32,710 --> 00:03:33,670
this example.

55
00:03:34,180 --> 00:03:38,890
So this is taken straight from the documentation, some really boring explanatory text, but it has

56
00:03:38,890 --> 00:03:40,300
some important things to know.

57
00:03:41,200 --> 00:03:44,950
The length of a char column is fixed to the length that you declare when you create the table.

58
00:03:44,950 --> 00:03:51,400
We've already discussed that the length can be any value from 0 to 2 five five, so the longest char

59
00:03:51,830 --> 00:03:54,760
or whatever that you can have is 255 characters long.

60
00:03:55,990 --> 00:04:01,270
When char values are stored, they are right padded with spaces to the specified length.

61
00:04:01,570 --> 00:04:03,880
So that's kind of what I was explaining.

62
00:04:03,880 --> 00:04:12,070
If we have a ten character limit, let's say Char ten and I, let's say I insert something that is only

63
00:04:12,070 --> 00:04:18,490
five characters, it's going to add five spaces after it on the right side of it, pat it with these

64
00:04:18,490 --> 00:04:22,480
base spaces to make it exactly ten characters.

65
00:04:23,480 --> 00:04:28,910
However, it's kind of confusing because when you go to look at that string, when you select it back

66
00:04:29,000 --> 00:04:31,700
and it's retrieved, those spaces are actually removed.

67
00:04:31,700 --> 00:04:36,980
As you can see here, when char values are retrieved, trailing spaces are removed and less blah, blah,

68
00:04:36,980 --> 00:04:37,190
blah.

69
00:04:37,220 --> 00:04:42,170
There is a way to get them to stay there, but I'm not sure why you would want that to happen.

70
00:04:42,860 --> 00:04:48,590
The only reason that I want to point that out is that I think it helps understand, helps you understand,

71
00:04:48,960 --> 00:04:53,210
has helped other students that I've worked with, understand how the fixed length works.

72
00:04:53,810 --> 00:04:59,360
If you think in memory that it's actually storing something the same size every time, it's always five

73
00:04:59,360 --> 00:05:02,780
or always ten characters and you can't get around that.

74
00:05:02,780 --> 00:05:08,330
So if you give it something shorter, it will elongate it with spaces to make it ten characters.

75
00:05:08,330 --> 00:05:12,560
If you give it something longer than ten characters, it's going to truncate it to make it ten characters.

76
00:05:13,100 --> 00:05:14,480
So why does it matter?

77
00:05:15,290 --> 00:05:23,450
Well, Char is faster for fixed length text, so there's not a ton of uses, in my experience, day

78
00:05:23,450 --> 00:05:29,390
to day for using char unless you know for sure that something is fixed length.

79
00:05:29,570 --> 00:05:35,780
So I tried to come up with some examples like state abbreviations is one at least in the US where we

80
00:05:35,780 --> 00:05:37,970
have two letter abbreviations for every state.

81
00:05:38,790 --> 00:05:41,480
There's no reason to make that a variable length of cha cha.

82
00:05:41,580 --> 00:05:50,490
You can save space and time essentially by using cha cha or a yes or no flag while you're n or any sort

83
00:05:50,490 --> 00:05:51,660
of like binary.

84
00:05:51,750 --> 00:05:55,560
It doesn't even have to be binary, but something that has a fixed length.

85
00:05:55,560 --> 00:06:04,010
So you could do sex in this case if you were doing where a pound or a what he called an animal shelter

86
00:06:04,020 --> 00:06:08,160
or keeping track of animals and their sex could use M and F.

87
00:06:08,760 --> 00:06:15,270
However, if we wanted to do things like male and female or unknown or whatever other options, those

88
00:06:15,270 --> 00:06:18,530
aren't all the same length, so it makes more sense to use fa cha.

89
00:06:19,260 --> 00:06:24,330
So you could probably come up with some other situations where this would make sense, but typically

90
00:06:24,330 --> 00:06:27,030
we use cha cha because it gives us a lot of flexibility.

91
00:06:28,520 --> 00:06:29,970
Otherwise use water.

92
00:06:31,220 --> 00:06:34,840
So there's a little there's a table here that I actually took this from the docks.

93
00:06:34,850 --> 00:06:38,690
I just kind of blew it up and added it into my slides.

94
00:06:38,690 --> 00:06:46,100
But it's from the documentation for char and char and what it's showing you here for a given value,

95
00:06:46,940 --> 00:06:49,160
like let's take a B.

96
00:06:50,130 --> 00:06:53,670
If we have a column with data type chart for.

97
00:06:55,070 --> 00:06:57,380
You can see that it adds the two spaces.

98
00:06:57,380 --> 00:07:00,230
So it's actually stored as a B space space.

99
00:07:01,270 --> 00:07:08,980
And if we store the same thing, it string a B in a column with var char for so var char instead of

100
00:07:08,980 --> 00:07:11,590
char it just stores a b.

101
00:07:12,370 --> 00:07:18,700
But what I want to call your attention to is not just how it stored, but also the storage size, how

102
00:07:18,700 --> 00:07:19,900
many bytes it takes up.

103
00:07:20,560 --> 00:07:29,920
So if we look at char, when we declare char for every single string that we insert into that column

104
00:07:29,920 --> 00:07:31,330
will always be four bytes.

105
00:07:32,300 --> 00:07:36,830
And you can see over here, whether it's an empty string, it will convert it to be four spaces.

106
00:07:37,250 --> 00:07:39,610
If it's two characters, it adds two spaces.

107
00:07:39,620 --> 00:07:41,690
If it's four characters, it leaves it alone.

108
00:07:41,690 --> 00:07:43,880
If it's more than four, it truncates it.

109
00:07:44,120 --> 00:07:46,160
So we have a fixed size here.

110
00:07:46,370 --> 00:07:55,010
However, with the bar chart for the same values, all have different storage sizes, so they're unchanged

111
00:07:55,010 --> 00:07:55,880
when they're stored.

112
00:07:55,900 --> 00:08:00,560
We don't get spaces added, we don't get anything truncated, which is good.

113
00:08:00,560 --> 00:08:04,780
But you can see that our storage size does start to blossom.

114
00:08:04,790 --> 00:08:09,620
Blossom starts to grow as the length of these strings grow.

115
00:08:09,650 --> 00:08:14,810
Now it's not a 1 to 1 thing, so it's not like if we have a ten character string here, it's going to

116
00:08:14,810 --> 00:08:16,040
take up ten bytes.

117
00:08:16,040 --> 00:08:17,780
But there is a difference.

118
00:08:17,780 --> 00:08:18,920
That's all that matters.

119
00:08:19,310 --> 00:08:24,410
With that said, to be honest, that difference won't make a big difference.

120
00:08:25,400 --> 00:08:26,390
Let me put that another way.

121
00:08:26,420 --> 00:08:32,780
That difference is insignificant for most applications that I have ever worked on.

122
00:08:32,900 --> 00:08:39,350
If you are working on some giant app, you work at Bank of America and you've got, you know, tens

123
00:08:39,350 --> 00:08:45,830
of millions of customers and hundreds of millions of records, that does make a significant difference.

124
00:08:45,830 --> 00:08:50,750
But if you're working on a personal project or your own hobby application, you don't really need to

125
00:08:50,750 --> 00:08:52,750
fret over this sort of thing.

126
00:08:52,760 --> 00:08:57,560
In fact, it's something sort of a common strategy that a lot of people would say is just get something

127
00:08:57,560 --> 00:08:58,070
working.

128
00:08:58,070 --> 00:09:01,700
And then when you get it, when it comes time, you can optimize things.

129
00:09:01,700 --> 00:09:06,350
So if you start to realize, well, this one thing is becoming kind of slow, this query I'm writing

130
00:09:07,070 --> 00:09:13,460
or whatever it is, maybe it makes sense for me to make this a fixed length and convert it to a char.

131
00:09:14,620 --> 00:09:15,160
Okay.

132
00:09:15,160 --> 00:09:17,230
So that's the difference on paper.

133
00:09:17,740 --> 00:09:19,690
You can go ahead and skip to the next video.

134
00:09:19,690 --> 00:09:22,960
I'm just going to show you in practice what it looks like.

135
00:09:22,960 --> 00:09:24,790
So I'm going to hop over to cloud nine.

136
00:09:25,270 --> 00:09:28,840
And first thing I'll say, I'm working in just a new database.

137
00:09:28,840 --> 00:09:32,470
I made basically just a testing database.

138
00:09:32,470 --> 00:09:36,910
I didn't want to muddy the waters with our inside of our book shop database.

139
00:09:36,910 --> 00:09:38,230
I want to leave that alone.

140
00:09:38,470 --> 00:09:41,140
So I just made one called New Testing.

141
00:09:41,140 --> 00:09:44,530
DB If you want to follow along, just make a new database.

142
00:09:44,740 --> 00:09:45,100
Hopefully.

143
00:09:45,100 --> 00:09:46,150
Remember how to do that.

144
00:09:46,810 --> 00:09:47,740
Call it whatever you want.

145
00:09:47,740 --> 00:09:50,170
I'm just going to delete it at the end of this anyways.

146
00:09:51,040 --> 00:09:58,000
So I'm going to create a table here and all I want to do is have two different columns.

147
00:09:58,000 --> 00:10:00,310
One, that's a char one that's a bar char.

148
00:10:00,580 --> 00:10:10,240
We'll just do dogs, let's say create table dogs and we'll have name, which will be char a fixed length

149
00:10:10,240 --> 00:10:19,750
five characters and then we'll have breed, which will be bar char and let's just say ten there.

150
00:10:21,930 --> 00:10:23,070
Just like that.

151
00:10:23,340 --> 00:10:25,410
And we'll go ahead and hit enter.

152
00:10:26,280 --> 00:10:27,690
And I'm missing a comma.

153
00:10:30,890 --> 00:10:31,550
Here we go.

154
00:10:33,260 --> 00:10:34,910
So we have that dogs table.

155
00:10:35,030 --> 00:10:37,430
Now let's insert some simple data.

156
00:10:37,550 --> 00:10:43,530
So the first thing that I'll do is insert into dogs and remember our syntax here.

157
00:10:43,550 --> 00:10:49,130
It's been a little while since we've inserted we've been doing a lot of select, but we need to have

158
00:10:49,130 --> 00:10:51,780
our values and the values that we'll pass in.

159
00:10:51,800 --> 00:10:56,630
We're going to start with something that is less than five characters.

160
00:10:56,840 --> 00:10:59,330
So for a name, let's say Bob.

161
00:11:00,750 --> 00:11:05,190
And breed will also be something short, let's say.

162
00:11:06,560 --> 00:11:07,110
Well, what is it?

163
00:11:07,110 --> 00:11:08,190
Short dog breed.

164
00:11:09,300 --> 00:11:09,930
Beagle?

165
00:11:09,960 --> 00:11:10,470
I guess.

166
00:11:10,470 --> 00:11:11,100
I don't know.

167
00:11:11,790 --> 00:11:12,750
So we'll do that.

168
00:11:13,880 --> 00:11:15,530
And we'll do one more here.

169
00:11:16,370 --> 00:11:18,440
This time will have a name.

170
00:11:18,440 --> 00:11:22,580
That's exactly five characters, let's call it Robbie.

171
00:11:22,610 --> 00:11:23,720
Spelled that way.

172
00:11:24,350 --> 00:11:26,870
I apologize for unoriginal names here.

173
00:11:26,880 --> 00:11:35,360
Just trying to work with the constraints and breed here will be corgi.

174
00:11:39,020 --> 00:11:40,070
And we'll do one more.

175
00:11:40,070 --> 00:11:41,990
And this time we'll have a long name.

176
00:11:42,680 --> 00:11:45,110
How about Princess?

177
00:11:46,970 --> 00:11:47,720
Jane.

178
00:11:49,620 --> 00:11:52,680
And Princess Jane is a man.

179
00:11:52,680 --> 00:11:54,750
I'm told that a lot here with another breed.

180
00:11:55,560 --> 00:11:58,260
Let's go with Retriever.

181
00:11:59,680 --> 00:12:00,670
First of all, that right.

182
00:12:03,960 --> 00:12:04,680
You never remember.

183
00:12:04,710 --> 00:12:05,070
All right.

184
00:12:05,070 --> 00:12:06,840
Forgive me if that spelled wrong.

185
00:12:09,090 --> 00:12:09,420
Okay.

186
00:12:09,600 --> 00:12:11,640
So first thing first, right off the bat.

187
00:12:11,670 --> 00:12:13,710
Notice we got a warning on this last one.

188
00:12:14,070 --> 00:12:15,300
Why do you think that happened?

189
00:12:16,170 --> 00:12:24,300
Well, it's because Princess Jane exceeds the limit that we've set for our char, which is five characters.

190
00:12:24,870 --> 00:12:29,250
So now if we do a simple select star from dogs.

191
00:12:32,020 --> 00:12:32,860
You'll see.

192
00:12:32,890 --> 00:12:35,350
We have Bob Robbie.

193
00:12:35,890 --> 00:12:36,690
They look good.

194
00:12:36,700 --> 00:12:39,010
They look the same unchanged.

195
00:12:39,370 --> 00:12:42,400
And Princess Jane, unfortunately, has been truncated.

196
00:12:42,400 --> 00:12:47,590
So unfortunately, we can't actually tell that there are those trailing spaces because they are not

197
00:12:47,590 --> 00:12:52,060
actually here when the text is retrieved.

198
00:12:52,090 --> 00:12:58,630
Remember, with that giant blurb Tech said, it said that they're stored with these two extra spaces.

199
00:12:58,630 --> 00:13:03,130
In our case, because this is three characters and we set up a five character fixed char.

200
00:13:03,550 --> 00:13:06,370
So there in memory are going to be two spaces here.

201
00:13:06,370 --> 00:13:09,730
But when it's pulled back out and displayed to us, they're chopped off.

202
00:13:11,110 --> 00:13:14,770
Robbie is unchanged because Robbie is five characters long.

203
00:13:15,010 --> 00:13:16,960
Princess Jane, unfortunately, is cut off.

204
00:13:17,630 --> 00:13:24,440
However, when we look over here, beagle, corgi and retriever works how you expect it's a variable

205
00:13:24,440 --> 00:13:25,400
character length.

206
00:13:25,430 --> 00:13:29,580
Let's just do one more example here with Princess Jane.

207
00:13:30,080 --> 00:13:34,190
We'll just have another Princess Jane and a long retriever.

208
00:13:34,310 --> 00:13:35,810
Blah, blah, blah, blah, blah.

209
00:13:38,070 --> 00:13:42,180
Now when I do a select star, notice again what happened.

210
00:13:42,330 --> 00:13:44,150
This is what we expect to happen.

211
00:13:44,160 --> 00:13:46,650
We've already seen this how VAR works.

212
00:13:47,040 --> 00:13:49,920
It still has a maximum length that we can provide.

213
00:13:49,920 --> 00:13:51,570
In this case, we did ten.

214
00:13:52,400 --> 00:13:53,270
I believe.

215
00:13:54,250 --> 00:13:54,970
Yes.

216
00:13:55,420 --> 00:14:01,370
So it can work similarly to CHA in that in that sense where it will truncate things.

217
00:14:01,390 --> 00:14:07,090
So just to wrap up the end here, Char again is faster for fixed length text.

218
00:14:07,090 --> 00:14:13,330
So if you know something is always going to be the same size use chart, however, if you have any doubt

219
00:14:13,430 --> 00:14:14,530
use var char.

220
00:14:14,800 --> 00:14:19,210
And again, the difference is relatively insignificant for most of the things.

221
00:14:20,020 --> 00:14:24,370
I don't want to be presumptuous, but most of the time it's not going to be a dealbreaker.

222
00:14:24,760 --> 00:14:29,020
So moving on, we're now going to discuss numbers in the next video, not this video.