1
00:00:00,000 --> 00:00:06,000
[Music]

2
00:00:17,000 --> 00:00:21,000
>> Well, hello and welcome, live from
Microsoft headquarters here

3
00:00:21,000 --> 00:00:26,000
in Bellevue/Redmond/Seattle-ish area.
We're going to be presenting

4
00:00:26,000 --> 00:00:30,000
a Querying Microsoft SQL Server
2012 class with you today.

5
00:00:30,000 --> 00:00:33,000
Thank you very much for taking
time to join us. We want some

6
00:00:34,000 --> 00:00:37,000
interaction for you to be able to
kind of give us some feedback,

7
00:00:37,000 --> 00:00:41,000
so as you're viewing the presentation
throughout the next several

8
00:00:41,000 --> 00:00:45,000
hours, to the right of your screen
there's a chat interface in

9
00:00:45,000 --> 00:00:48,000
there, feel comfortable replying
or sending in some comments

10
00:00:48,000 --> 00:00:52,000
to there or some chats to there.
We've got tons of SMEs, subject

11
00:00:52,000 --> 00:00:55,000
matter exerts, working behind the
scenes with us. And myself,

12
00:00:55,000 --> 00:00:59,000
which is Brian Alderman, myself as
I will be presenting, we will

13
00:00:59,000 --> 00:01:04,000
also be monitoring the chats as
well here from the studio.

14
00:01:04,000 --> 00:01:06,000
And the cool thing is if you type
in a really good question,

15
00:01:06,000 --> 00:01:10,000
it will be sent up to us and we'll
take that question live and

16
00:01:10,000 --> 00:01:12,000
get that answer to you. The good
news is you're not going to

17
00:01:12,000 --> 00:01:15,000
have to listen to me all day alone.
I also have joining me in

18
00:01:15,000 --> 00:01:18,000
the studio Tobias. How you doing?

19
00:01:18,000 --> 00:01:21,000
>> Not too bad, Brian. Feels
good to be here.

20
00:01:22,000 --> 00:01:24,000
>> It's good to see you. Thanks
for joining us today. And what

21
00:01:24,000 --> 00:01:26,000
we're going to talk about, I want
to make sure everyone understands

22
00:01:26,000 --> 00:01:30,000
what you've gotten yourself into. This
is more of a Level 300 class.

23
00:01:30,000 --> 00:01:33,000
And we're trying to meet the audience
that's trying to learn

24
00:01:33,000 --> 00:01:37,000
the technical skills required for
some of the advanced Transact-SQL

25
00:01:37,000 --> 00:01:41,000
queries within SQL Server. So you
should have a basic understanding

26
00:01:41,000 --> 00:01:44,000
at least of relational databases,
understanding the use of the

27
00:01:44,000 --> 00:01:47,000
SSMS, or the SQL Server Management
Studio, because we're going

28
00:01:47,000 --> 00:01:50,000
to be spending a lot of time in there,
and then a basic knowledge

29
00:01:50,000 --> 00:01:53,000
of some of the Microsoft Windows
operating system and some of

30
00:01:53,000 --> 00:01:56,000
its core functionality. Let's take
a look at what we're going

31
00:01:56,000 --> 00:01:59,000
to discuss throughout the day here.
Or evening, depending where

32
00:01:59,000 --> 00:02:02,000
you're dialed in from. We're going
to begin with just an introduction

33
00:02:02,000 --> 00:02:05,000
to SQL Server 2012. We've got a
couple modules, just kind of

34
00:02:05,000 --> 00:02:08,000
levelling the playing field to
make sure everyone understands

35
00:02:08,000 --> 00:02:10,000
what it is that we're going to address
throughout the day, and

36
00:02:10,000 --> 00:02:13,000
some of the terms and concepts that
we'll be addressing or using

37
00:02:13,000 --> 00:02:16,000
throughout the day. We'll then move
into the second module and

38
00:02:16,000 --> 00:02:20,000
start looking at some of the advanced
select statements, things

39
00:02:20,000 --> 00:02:23,000
like the distinct clause, the cases,
the joins, the merges, filtering

40
00:02:23,000 --> 00:02:28,000
and sorting data, and how do we deal
with those pesky null values.

41
00:02:28,000 --> 00:02:31,000
We'll move into data types. Because,
as you know, when we build

42
00:02:31,000 --> 00:02:34,000
out new objects in SQL Server,
it's very important for us to

43
00:02:34,000 --> 00:02:38,000
understand the appropriate data
type and apply that data type.

44
00:02:38,000 --> 00:02:42,000
So we'll talk about that, the conversion
of data types, and actually

45
00:02:42,000 --> 00:02:44,000
introduce some SQL Server functions.

46
00:02:45,000 --> 00:02:48,000
We'll then move into grouping and
aggregating data. And in that

47
00:02:48,000 --> 00:02:51,000
area we'll talk about group by
and having clauses. We'll talk

48
00:02:51,000 --> 00:02:56,000
about subqueries. We'll talk about
different views, inline tables,

49
00:02:56,000 --> 00:03:00,000
valid functions, and derived tables.
So some of the more advanced

50
00:03:00,000 --> 00:03:04,000
titles we're talking about grouping and
aggregating... aggregating data.

51
00:03:04,000 --> 00:03:07,000
We'll then break for lunch. We're posting
up there roughly 60 minutes.

52
00:03:07,000 --> 00:03:11,000
It may be 45. It may be 60. It
may be two hours. No, it won't

53
00:03:11,000 --> 00:03:14,000
be two hours, I promise. But will
be a roughly 60-minute lunch

54
00:03:14,000 --> 00:03:18,000
hour, depending on what time we are,
where we are in the curriculum

55
00:03:18,000 --> 00:03:22,000
as we go through today's event.
We'll then move in after lunch,

56
00:03:22,000 --> 00:03:24,000
or roughly somewhere throughout
the day, we're going to talk

57
00:03:24,000 --> 00:03:25,000
about set operators.

58
00:03:26,000 --> 00:03:30,000
Set operators are we're going to include
Windows functions, grouping

59
00:03:30,000 --> 00:03:34,000
sets using pivot and cubes and
rollups. We'll introduce those

60
00:03:34,000 --> 00:03:37,000
topics and talk about those for
you. We'll talk about modifying

61
00:03:37,000 --> 00:03:39,000
data, because it's not all about
retrieving data, we need to

62
00:03:39,000 --> 00:03:43,000
know how to properly insert,
update, and delete content.

63
00:03:43,000 --> 00:03:46,000
And we'll look at the use of constraints,
default constraints,

64
00:03:46,000 --> 00:03:50,000
triggers, to make sure that when
we're adding data, we're kind

65
00:03:50,000 --> 00:03:53,000
of enforcing data integrity by
setting up different defaults,

66
00:03:53,000 --> 00:03:58,000
constraints, and triggers. We'll look at
programming with T-SQL, Transact-SQL.

67
00:03:58,000 --> 00:04:02,000
We'll look some of the programming
elements, some error handling,

68
00:04:02,000 --> 00:04:05,000
and understanding and implementing transactions
within your programming environment.

69
00:04:06,000 --> 00:04:09,000
And then we'll wrap up today's class
with retrieving SQL Server

70
00:04:09,000 --> 00:04:13,000
metadata and improving SQL Server
query performance. So we'll

71
00:04:13,000 --> 00:04:17,000
look at things like system catalogs,
dynamic management views,

72
00:04:17,000 --> 00:04:21,000
or DMVs. We'll talk about creating
and executing store procedures,

73
00:04:21,000 --> 00:04:24,000
and then an overall understanding
of ways that we can improve

74
00:04:24,000 --> 00:04:28,000
the SQL Server performance specifically
when we're querying.

75
00:04:28,000 --> 00:04:29,000
So that's what we're going to take
a look at. We're going to

76
00:04:30,000 --> 00:04:33,000
begin, and like we said, with an introduction
to Microsoft SQL Server.

77
00:04:33,000 --> 00:04:36,000
Again, feel comfortable using that
chat to the right. If you

78
00:04:36,000 --> 00:04:38,000
have any great questions for us,
feel free to chat in to us so

79
00:04:38,000 --> 00:04:42,000
we can get any answers... any questions
answered for you as we

80
00:04:42,000 --> 00:04:45,000
have a plethora... I love using
that word... a plethora of SMEs

81
00:04:45,000 --> 00:04:48,000
working behind the scenes working
with us. And Tobias and I

82
00:04:48,000 --> 00:04:52,000
will also take some live questions
as they come into us. So let's

83
00:04:52,000 --> 00:04:55,000
go ahead and begin with an introduction
to Microsoft SQL Server.

84
00:04:56,000 --> 00:04:59,000
And this is going to be Module 1.
And, again, kind of like just

85
00:04:59,000 --> 00:05:03,000
kind of laying a foundation and understanding
or kind of levelling

86
00:05:03,000 --> 00:05:06,000
the playing field for people. We're
just going to talk a little

87
00:05:06,000 --> 00:05:09,000
bit about some of the different
types of commands and statement

88
00:05:09,000 --> 00:05:12,000
elements that we have out there,
and then look at a couple basic

89
00:05:12,000 --> 00:05:15,000
select statements where we can actually
go in and talk to these...

90
00:05:15,000 --> 00:05:18,000
the basic select statements
that you might perform.

91
00:05:19,000 --> 00:05:23,000
So three types of commands that
you can find out there. We have

92
00:05:23,000 --> 00:05:26,000
DML, or data manipulation language.
And with this class being

93
00:05:26,000 --> 00:05:30,000
querying SQL Server, the primary
focus is going to be on DML

94
00:05:30,000 --> 00:05:33,000
specifically the select statement,
but we are... as we talked

95
00:05:33,000 --> 00:05:35,000
about, we are going to talk about
the insert, the update, and

96
00:05:35,000 --> 00:05:39,000
delete commands, which falls in
that data manipulation language

97
00:05:39,000 --> 00:05:42,000
category of statements. We're also
going to talk about DDLs,

98
00:05:42,000 --> 00:05:45,000
how we create objects, whether
it be a database or a table or

99
00:05:45,000 --> 00:05:49,000
a store procedure. And after we create
we may need to modify that.

100
00:05:49,000 --> 00:05:52,000
We'll talk about ways to alter that.
And eventually we may have

101
00:05:52,000 --> 00:05:56,000
a reason to drop that. Now, my goal
is to change that drop command

102
00:05:56,000 --> 00:05:59,000
to the toast command. So I'm trying
to get Microsoft to change

103
00:05:59,000 --> 00:06:01,000
that from drop to toast. I'm going
to be working with Tobias

104
00:06:01,000 --> 00:06:05,000
on trying to get that toast command in
there instead of the drop command.

105
00:06:05,000 --> 00:06:08,000
Is there any way we can get that
done by the end of the class?

106
00:06:08,000 --> 00:06:12,000
>> No. Okay. I'll keep working on
that. I'm not going to take

107
00:06:12,000 --> 00:06:15,000
no as an answer yet. So if you all
are in favor of having a toast

108
00:06:15,000 --> 00:06:18,000
command instead of a drop command,
please let us know about that,

109
00:06:18,000 --> 00:06:23,000
and I think we'll see maybe if we get
enough heat on the development

110
00:06:23,000 --> 00:06:26,000
team in SQL you get a toast command
added for the next version.

111
00:06:26,000 --> 00:06:30,000
>> I have been trying to tell Brian
about this NC standard thing

112
00:06:30,000 --> 00:06:34,000
we have going and trying to stay close
to standards, and apparently

113
00:06:34,000 --> 00:06:38,000
they just haven't come to
toast command just yet.

114
00:06:38,000 --> 00:06:40,000
>> Well, I think you could be setting...
you could be setting

115
00:06:40,000 --> 00:06:44,000
up new ground for people. Maybe
everyone would change to the

116
00:06:44,000 --> 00:06:47,000
toast command from the drop command.
It's a cool command.

117
00:06:47,000 --> 00:06:49,000
We're going to use toast a lot
today. Unfortunately I've got

118
00:06:49,000 --> 00:06:52,000
to type in drop, but when I type
in drop, just think I'm thinking

119
00:06:53,000 --> 00:06:54,000
toast when I'm typing that in.

120
00:06:54,000 --> 00:06:57,000
>> But there's not a drop command...
there's not a toast command.

121
00:06:57,000 --> 00:07:00,000
All right. We're also going to talk
about data control statements.

122
00:07:00,000 --> 00:07:04,000
Those are where we control access
to the different objects within

123
00:07:04,000 --> 00:07:08,000
the SQL Server databases. So those
are DCL commands. We'll touch

124
00:07:08,000 --> 00:07:11,000
on those as well. So three different
categories of commands that

125
00:07:11,000 --> 00:07:12,000
we'll be using.

126
00:07:13,000 --> 00:07:18,000
DML, DDL, and DCL. Now, we have
some T-SQL language elements

127
00:07:18,000 --> 00:07:20,000
that we're going to be using, we're
going to drill into a little

128
00:07:20,000 --> 00:07:23,000
bit more detail. I'm going to introduce
them and then let Tobias

129
00:07:23,000 --> 00:07:25,000
kind of just talk a little bit,
a little bit more about these.

130
00:07:25,000 --> 00:07:28,000
But we have what are called predicates
and operators that we

131
00:07:28,000 --> 00:07:31,000
use when we're writing T-SQL commands,
different functions that

132
00:07:31,000 --> 00:07:34,000
we can call. We may want to reuse
something like a store procedure,

133
00:07:34,000 --> 00:07:37,000
so we may want to pass variables.
We have different expressions

134
00:07:37,000 --> 00:07:41,000
that we'll be able to use, ways
to have a batch job and we can

135
00:07:41,000 --> 00:07:45,000
supply separators in for that. We
want to maybe control the flow

136
00:07:45,000 --> 00:07:49,000
so we could use things like an
if/else statement. And then of

137
00:07:49,000 --> 00:07:51,000
course we want to comment because we're
always good about commenting

138
00:07:51,000 --> 00:07:53,000
content, so we want to make sure
we can add comments. And we're

139
00:07:53,000 --> 00:07:58,000
going to show you how we add comments
to this content. So I'm

140
00:07:58,000 --> 00:08:02,000
going to ask Tobias to kind of
walk us through some of these

141
00:08:02,000 --> 00:08:05,000
elements that we can use when
we're working with T-SQL.

142
00:08:07,000 --> 00:08:08,000
>> Thank you, Brian.
Sure.

143
00:08:08,000 --> 00:08:13,000
So what you're seeing there is kind
of the standard things you

144
00:08:13,000 --> 00:08:15,000
would imagine in any programming
language, right?

145
00:08:17,000 --> 00:08:23,000
We have predicates. That includes
the common operators, the common

146
00:08:23,000 --> 00:08:26,000
comparison operators including like,
which is a special string

147
00:08:26,000 --> 00:08:30,000
kind of operator. We have three
logical operators in not, and,

148
00:08:30,000 --> 00:08:31,000
and or.

149
00:08:32,000 --> 00:08:36,000
And we dig into how you use all of
these as we go through the class.

150
00:08:36,000 --> 00:08:39,000
And I think one thing that's interesting
for us to kind of focus

151
00:08:39,000 --> 00:08:45,000
on is what's best practices, how
should you use the language,

152
00:08:45,000 --> 00:08:50,000
what should you think about and
what may you want to avoid for

153
00:08:50,000 --> 00:08:52,000
various reasons. It's all very
context sensitive. Right?

154
00:08:52,000 --> 00:08:57,000
So using the right thing at the
right moment is clearly going

155
00:08:59,000 --> 00:09:01,000
>> All right. So those are the predicates.
Now, also we have functions.

156
00:09:01,000 --> 00:09:04,000
You see we have three categories
of functions. We have string

157
00:09:04,000 --> 00:09:07,000
functions, something like if you
want to extract a portion of

158
00:09:07,000 --> 00:09:11,000
a string or we want to look at the
length of a string. We have

159
00:09:11,000 --> 00:09:13,000
date and time functions. We want
to go out and grab the system

160
00:09:13,000 --> 00:09:18,000
date, we want to maybe check and
add a certain time frame to

161
00:09:18,000 --> 00:09:21,000
a day. And then we have aggregate
functions. We want the average

162
00:09:21,000 --> 00:09:27,000
sale for a particular salesperson or the total
sales for a particular salesperson.

163
00:09:27,000 --> 00:09:30,000
Those fall under the aggregate functions.
So we can use these functions.

164
00:09:30,000 --> 00:09:34,000
And, again, we'll see these as we go
out throughout today's presentation.

165
00:09:34,000 --> 00:09:38,000
And we'll see these actually in
use. Now, the idea of using

166
00:09:38,000 --> 00:09:42,000
variables is very powerful because
this gives us the opportunity

167
00:09:42,000 --> 00:09:45,000
to reuse items like store procedures
that we've created and be

168
00:09:45,000 --> 00:09:49,000
able to pass different variables or different
values with these variables.

169
00:09:49,000 --> 00:09:52,000
So we'll see how we can declare a
variable. Very straightforward.

170
00:09:52,000 --> 00:09:56,000
And we can also now not only declare
it but we can also initialize

171
00:09:56,000 --> 00:09:59,000
at the same time beginning with
SQL Server 2008, in fact.

172
00:09:59,000 --> 00:10:02,000
So we'll talk about the use of variables
and how powerful they

173
00:10:02,000 --> 00:10:05,000
can be to make it a little bit
more flexible and dynamic, for

174
00:10:05,000 --> 00:10:08,000
instance, store procedure, if we're
trying to use that. We have

175
00:10:08,000 --> 00:10:12,000
some expressions here. Like we're
doing a select, a year, the

176
00:10:12,000 --> 00:10:15,000
order date plus 1, we have select
order quantity times unit price.

177
00:10:15,000 --> 00:10:17,000
So we can get very creative with
our select statements. We don't

178
00:10:17,000 --> 00:10:21,000
have to just do select asterisk from
a table name and walk away.

179
00:10:21,000 --> 00:10:24,000
We can actually go in and get creative
and kind of customize

180
00:10:24,000 --> 00:10:26,000
that result set. And we're going to
look at some of the opportunities

181
00:10:26,000 --> 00:10:30,000
we have for the customization of
that result set by using some

182
00:10:30,000 --> 00:10:33,000
of these expressions that we have
available to us. And the use

183
00:10:33,000 --> 00:10:37,000
of batch jobs are really powerful
because this allows us to be

184
00:10:37,000 --> 00:10:40,000
able to go in there and script content,
and then we can schedule

185
00:10:40,000 --> 00:10:45,000
that content to run at a particular
time. And if we have multiple

186
00:10:45,000 --> 00:10:48,000
statements, we may often have to
separate them. We most often

187
00:10:49,000 --> 00:10:52,000
do that with the go statement. Now,
go is not specific to a SQL

188
00:10:53,000 --> 00:10:57,000
Server T-SQL command. It just allows
us to go out there and separate

189
00:10:57,000 --> 00:11:01,000
the different batch jobs or commands
we have within a batch job.

190
00:11:01,000 --> 00:11:04,000
>> And it's important to know here
with go that it's just something

191
00:11:04,000 --> 00:11:08,000
that the tool sees such as SQL Server
Management Studio. It sees

192
00:11:08,000 --> 00:11:11,000
go and it knows that whatever was
before go I'll send separately,

193
00:11:11,000 --> 00:11:14,000
wait for a response from the server
and then I'll send the rest,

194
00:11:14,000 --> 00:11:17,000
right, and do this between the
go. So SQL Server itself, like

195
00:11:17,000 --> 00:11:21,000
Brian mentioned, doesn't really
know what go is. It's different

196
00:11:21,000 --> 00:11:24,000
tools may use different batch
separators. But all our tools

197
00:11:25,000 --> 00:11:28,000
default to go as the batch separator.

198
00:11:29,000 --> 00:11:32,000
>> Some other elements we can use
are control of flow. I'll be

199
00:11:32,000 --> 00:11:36,000
talking about this briefly. If/else, the
while, begin end. Error handling.

200
00:11:36,000 --> 00:11:40,000
We're going to look at some ways
to catch errors and how we're

201
00:11:40,000 --> 00:11:42,000
going to be able to respond to those
errors. We'll look at those

202
00:11:42,000 --> 00:11:46,000
later on today. And, again, we'll
spend some time talking about

203
00:11:46,000 --> 00:11:50,000
transactions, how to define a transaction
within a batch, for

204
00:11:50,000 --> 00:11:53,000
instance, used in a begin transaction,
committing that transaction,

205
00:11:53,000 --> 00:11:55,000
and potentially having to roll
back that transaction.

206
00:11:56,000 --> 00:11:58,000
You're going to want to document,
especially start getting into

207
00:11:58,000 --> 00:12:00,000
batch jobs, you're going to want
to document what's going on

208
00:12:00,000 --> 00:12:03,000
in there. This gives you the opportunity
to use what's called

209
00:12:03,000 --> 00:12:06,000
a block of commented code. This
allows you to set up comments.

210
00:12:06,000 --> 00:12:09,000
As you can see we have a forward
slash asterisk. Anything I

211
00:12:09,000 --> 00:12:13,000
say after that is a comment until
I get the asterisk forward

212
00:12:13,000 --> 00:12:17,000
slash again. That closes that
block of comments out. If you

213
00:12:17,000 --> 00:12:21,000
want just a line of comments, we
can use the dash dash. And then

214
00:12:21,000 --> 00:12:24,000
you'll just... that line will
not be executed. So if you're

215
00:12:24,000 --> 00:12:27,000
working on troubleshooting a particular
script and there's certain

216
00:12:27,000 --> 00:12:30,000
code you don't want to run temporarily,
you can kind of comment

217
00:12:30,000 --> 00:12:34,000
that out using the block of commented
code or these two dashes,

218
00:12:34,000 --> 00:12:37,000
and they'll ignore that temporarily.
Or if you just want to

219
00:12:37,000 --> 00:12:40,000
document what you're doing in there,
you know, as the batch job

220
00:12:40,000 --> 00:12:44,000
is running, you can actually add some
comments to that documentation.

221
00:12:44,000 --> 00:12:47,000
Or as documentation to that
script or that batch job.

222
00:12:48,000 --> 00:12:51,000
Now, the idea of actually executing
a query is pretty... if you

223
00:12:51,000 --> 00:12:54,000
understand that you got this select,
you have the from, you have the...

224
00:12:54,000 --> 00:12:58,000
possibly the where, maybe the group
by, maybe the order by.

225
00:12:58,000 --> 00:13:02,000
Often we look at the way it's written
and we think that's the

226
00:13:02,000 --> 00:13:05,000
way it's actually executed. What
we want to show you here is

227
00:13:05,000 --> 00:13:08,000
the logical order of the query
being processed. So the first

228
00:13:08,000 --> 00:13:10,000
thing that's going to happen is we're
going to jump out to the from.

229
00:13:11,000 --> 00:13:12,000
We're going to find out
to make sure...

230
00:13:12,000 --> 00:13:16,000
>> So, Brian, just why do I care?
Why is it important with the

231
00:13:16,000 --> 00:13:17,000
logical query processing?

232
00:13:17,000 --> 00:13:19,000
>> Because you want to make sure
that you understand when you're

233
00:13:19,000 --> 00:13:21,000
writing this information, and if you're
troubleshooting and trying

234
00:13:21,000 --> 00:13:26,000
to optimize it, if you understand
the order that these options

235
00:13:26,000 --> 00:13:29,000
or these statements are being executed,
it might give you the

236
00:13:29,000 --> 00:13:33,000
opportunity to optimize that and
make it run a little bit faster.

237
00:13:33,000 --> 00:13:35,000
>> Yeah, that's good. And it's also...
it's also important to

238
00:13:35,000 --> 00:13:39,000
know the logical query order because
it helps you understand

239
00:13:39,000 --> 00:13:44,000
exactly what needs to go on under the
surface, right, under the covers.

240
00:13:44,000 --> 00:13:49,000
So if you don't know exactly how the
query language is structured,

241
00:13:49,000 --> 00:13:53,000
right, then you may end up not
getting the results you want.

242
00:13:53,000 --> 00:13:57,000
So it's not... it doesn't mean that
whatever you type is exactly

243
00:13:57,000 --> 00:14:02,000
what we are going to do, because we
have this fancy query optimizer

244
00:14:02,000 --> 00:14:06,000
that figures out the right way, the
best way to give you the results.

245
00:14:06,000 --> 00:14:10,000
But it does mean that we are constrained
so that we will always

246
00:14:10,000 --> 00:14:13,000
logically execute in this order.
So it's super important to

247
00:14:13,000 --> 00:14:15,000
understand the logical query processing.

248
00:14:16,000 --> 00:14:19,000
>> So it actually begins with the
from statement and then it goes

249
00:14:19,000 --> 00:14:20,000
to the where clause. I'm just going
to bring these up, you'll

250
00:14:21,000 --> 00:14:25,000
see, as they're numbered here.
Group by, having, and then it

251
00:14:25,000 --> 00:14:28,000
goes to select. Oddly enough, the
first thing that we write is

252
00:14:28,000 --> 00:14:31,000
the select, but that really isn't a...
you know, isn't addressed until...

253
00:14:32,000 --> 00:14:35,000
well, going to call it step 5. And
if there's an order by, that's

254
00:14:35,000 --> 00:14:39,000
executed last. So, you know, it's
good to understand that order,

255
00:14:39,000 --> 00:14:42,000
that processing order so when you're
writing your queries or

256
00:14:42,000 --> 00:14:44,000
if you're trying to optimize your
queries, you can see where

257
00:14:44,000 --> 00:14:45,000
that information
is coming from.

258
00:14:46,000 --> 00:14:50,000
>> And we get into some more details
on logical query processing

259
00:14:50,000 --> 00:14:53,000
later, because there are some other
elements to the language

260
00:14:53,000 --> 00:14:57,000
that are in addition to what
we're showing on this slide.

261
00:14:58,000 --> 00:15:01,000
>> So this is applying the logical
order of operations here.

262
00:15:01,000 --> 00:15:03,000
We've got this statement here, so
let's start from the top, use

263
00:15:04,000 --> 00:15:07,000
adventure works. We also... we identify
what databases we want

264
00:15:07,000 --> 00:15:11,000
to use, and so I always begin a
statement with a use so I make

265
00:15:11,000 --> 00:15:13,000
sure I'm in the appropriate database.

266
00:15:14,000 --> 00:15:15,000
Then we'll add the from.

267
00:15:16,000 --> 00:15:17,000
This is actually sales.salesorderheader.

268
00:15:18,000 --> 00:15:22,000
Where customer ID equals a certain
number. Group by salesperson.

269
00:15:22,000 --> 00:15:26,000
And then we have to grab the
year off the order date.

270
00:15:26,000 --> 00:15:28,000
Having count greater than 1.

271
00:15:28,000 --> 00:15:31,000
And then we execute, then we have
the select portion. We're not

272
00:15:31,000 --> 00:15:33,000
going write the statement this
way, but, again, this is how a

273
00:15:33,000 --> 00:15:37,000
statement that we have written will
be processed by SQL Server.

274
00:15:37,000 --> 00:15:39,000
And finally wrap up
with the order by.

275
00:15:39,000 --> 00:15:44,000
>> Yes. So one thing I just wanted
to mention on use is we have

276
00:15:44,000 --> 00:15:47,000
two big products out there with
regard to SQL, right? One is

277
00:15:47,000 --> 00:15:50,000
SQL Server 2012, which is our on-premise
product, and then we

278
00:15:50,000 --> 00:15:53,000
have Windows Azure SQL database,
which is our cloud offering

279
00:15:53,000 --> 00:15:57,000
in Azure. And the use statement
itself is not supported in SQL

280
00:15:57,000 --> 00:16:02,000
database because of what we sell
is something different from

281
00:16:02,000 --> 00:16:05,000
SQL Server. We sell databases
rather than instances.

282
00:16:05,000 --> 00:16:09,000
So that's important to know. If you
want to create software that

283
00:16:09,000 --> 00:16:13,000
runs nicely on both of the platforms,
you should not use the

284
00:16:13,000 --> 00:16:16,000
use statement within your program.
You'll very often use it

285
00:16:17,000 --> 00:16:20,000
in SQL Server for debugging or for
testing your scripts, right,

286
00:16:20,000 --> 00:16:25,000
but within your software, your app
that you write, you may want

287
00:16:25,000 --> 00:16:29,000
to avoid using use. The other thing
that's interesting here is

288
00:16:29,000 --> 00:16:33,000
if you're interested in it working
on both our Azure platform

289
00:16:33,000 --> 00:16:37,000
as well as on our on-premise platform,
you should look at trying

290
00:16:37,000 --> 00:16:41,000
on both. And if you develop against
Windows Azure SQL DB, you'll

291
00:16:41,000 --> 00:16:44,000
be able to run on SQL
Server as well.

292
00:16:44,000 --> 00:16:48,000
>> Are you suggesting they should test
the code before they deploy it?

293
00:16:48,000 --> 00:16:51,000
>> Really. Okay. Because I didn't know
we were supposed to do that.

294
00:16:51,000 --> 00:16:54,000
So all right. All right. Let's take
a look at some basic select

295
00:16:54,000 --> 00:16:58,000
statements again. Some of these
are more advanced and might be

296
00:16:58,000 --> 00:17:00,000
familiar with this, but we just
want to make sure everyone's

297
00:17:00,000 --> 00:17:04,000
on the same page here. So we have
a select, we supply the select

298
00:17:04,000 --> 00:17:06,000
list, the from, the where, the
group by, the order by.

299
00:17:06,000 --> 00:17:07,000
We're going to see plenty of these.

300
00:17:08,000 --> 00:17:10,000
Here's where we're looking at retrieving
call in from a group.

301
00:17:10,000 --> 00:17:15,000
We can actually just use select customer
ID, store ID from the sales.customer.

302
00:17:15,000 --> 00:17:20,000
So we're identifying the object
in the schema for that object.

303
00:17:20,000 --> 00:17:22,000
So that's about as basic as you
can get. Well, you can get a

304
00:17:22,000 --> 00:17:25,000
little bit more basic. Instead of
identifying the columns, you

305
00:17:25,000 --> 00:17:27,000
could swap out the column names
and put a asterisk in there.

306
00:17:27,000 --> 00:17:29,000
That's the most basic select
statement you can write.

307
00:17:29,000 --> 00:17:32,000
Select asterisk from table name.
We're actually taking it up

308
00:17:32,000 --> 00:17:33,000
>> We can make it easier.

309
00:17:33,000 --> 00:17:34,000
>> Make it easier than that?

310
00:17:34,000 --> 00:17:36,000
>> Yeah. Select space 1.

311
00:17:36,000 --> 00:17:38,000
>> Select space
1. That's...

312
00:17:38,000 --> 00:17:40,000
>> That's probably the
easiest you can go.

313
00:17:40,000 --> 00:17:43,000
>> Okay. Well, okay. So try that. If
you want the easiest statement

314
00:17:44,000 --> 00:17:47,000
so you can actually go out and brag
that you've actually submitted

315
00:17:47,000 --> 00:17:50,000
and executed a select statement...
what is it, select space 1.

316
00:17:50,000 --> 00:17:53,000
>> And it's actually not... it sounds
like trivial and stupid

317
00:17:53,000 --> 00:17:56,000
and why would you ever do that, right,
but it's actually interesting

318
00:17:56,000 --> 00:17:59,000
when you want to do performance
tuning, if you're interested

319
00:17:59,000 --> 00:18:02,000
in what is the performance of just
speaking to SQL Server, like

320
00:18:02,000 --> 00:18:06,000
network, all of these things, rather
than going into what actually

321
00:18:06,000 --> 00:18:10,000
happens in the database engine, select
1 is a perfect thing to test.

322
00:18:10,000 --> 00:18:13,000
>> Awesome. Definitely.
Select 1.

323
00:18:13,000 --> 00:18:16,000
All right. Using calculations.
So as you can see here, we've

324
00:18:16,000 --> 00:18:19,000
select unit price order quantity
and then we're performing a

325
00:18:19,000 --> 00:18:22,000
calculation unit price times order
quantity and we're going to

326
00:18:22,000 --> 00:18:26,000
grab this from the sales order detail
table. So we can actually

327
00:18:26,000 --> 00:18:28,000
use this. And we have different
calculations. Right now we're

328
00:18:29,000 --> 00:18:32,000
doing a multiplication here. But
we can do an addition, we can

329
00:18:32,000 --> 00:18:35,000
do division, we do percentages. So we
have these different calculations,

330
00:18:35,000 --> 00:18:38,000
scalar calculations that are available
to us. We can also do

331
00:18:38,000 --> 00:18:41,000
a concatenate. We can concatenate
content. Again, we're going

332
00:18:41,000 --> 00:18:43,000
to see some samples of these. We're
just kind of introducing

333
00:18:43,000 --> 00:18:45,000
some of the topics that we're
going to go through here.

334
00:18:47,000 --> 00:18:49,000
All right. Let's take a look at
some basic statements here.

335
00:18:50,000 --> 00:18:53,000
So I'll come out of the

336
00:18:54,000 --> 00:18:58,000
PowerPoint slide and jump
into an image here, then

337
00:19:00,000 --> 00:19:03,000
go into SQL Management Studio.
And again we've got adventure

338
00:19:03,000 --> 00:19:05,000
works and we're already using
adventure works. So we can do

339
00:19:06,000 --> 00:19:07,000
a select again.

340
00:19:07,000 --> 00:19:08,000
>> 1, 1, 1.

341
00:19:08,000 --> 00:19:10,000
>> 1. I'm going to do that one first
just so everyone sees that

342
00:19:10,000 --> 00:19:12,000
this works. And the cool thing, if
you're not familiar with this,

343
00:19:12,000 --> 00:19:15,000
you can highlight this
and just execute it.

344
00:19:15,000 --> 00:19:18,000
And you got no column name but you
get the one returned. So as

345
00:19:18,000 --> 00:19:22,000
you've said, Tobias, we've gone
out, we've communicated with

346
00:19:22,000 --> 00:19:25,000
SQL Server, we've got a response. So
we know that things are working.

347
00:19:25,000 --> 00:19:30,000
We're able to communicate. Now,
you can also take it up a notch

348
00:19:30,000 --> 00:19:33,000
and go and select asterisk
from we'll say sales...

349
00:19:34,000 --> 00:19:37,000
sales... and the cool thing with
intelli-sense because I couldn't

350
00:19:37,000 --> 00:19:40,000
remember exactly what I wanted to
go to, we can go to sales or

351
00:19:40,000 --> 00:19:44,000
table, order header, and then we
can... again, we'll highlight

352
00:19:44,000 --> 00:19:47,000
that and execute just that command.

353
00:19:47,000 --> 00:19:50,000
And now it gives us a bunch of
information here. Probably too

354
00:19:50,000 --> 00:19:52,000
much because now I've got every
row and I really didn't want

355
00:19:52,000 --> 00:19:56,000
every row of... or every column,
excuse me. I wanted just a

356
00:19:56,000 --> 00:19:59,000
few items about the rows in here.
So we can change this again

357
00:19:59,000 --> 00:20:02,000
to do a select. We'll
grab sales order ID.

358
00:20:04,000 --> 00:20:06,000
Let's actually do sales...

359
00:20:06,000 --> 00:20:08,000
we'll do it... spell
it out all the way.

360
00:20:10,000 --> 00:20:12,000
And we'll grab one more.

361
00:20:12,000 --> 00:20:16,000
We'll grab due date just
so we stay with it here.

362
00:20:20,000 --> 00:20:23,000
Due date. And then
we'll specify here

363
00:20:24,000 --> 00:20:29,000
from the sales.salesorderheader
and I'm going to double click

364
00:20:29,000 --> 00:20:32,000
here, save a few keystrokes. So
we'll again highlight that and

365
00:20:32,000 --> 00:20:33,000
execute that.

366
00:20:33,000 --> 00:20:34,000
>> As sales.

367
00:20:35,000 --> 00:20:36,000
>> I'm sorry?

368
00:20:36,000 --> 00:20:41,000
>> You'd have to says as sales
for... you can try.

369
00:20:41,000 --> 00:20:43,000
>> I didn't hear what
you said. Sorry.

370
00:20:43,000 --> 00:20:45,000
>> You have to specify the table
alias since you added it...

371
00:20:46,000 --> 00:20:47,000
you're using sales dot.

372
00:20:47,000 --> 00:20:49,000
>> Oh as sales. Gotcha.

373
00:20:49,000 --> 00:20:50,000
>> Since you added that.

374
00:20:52,000 --> 00:20:55,000
>> From sales.sales.duedate, sales.orderheader.

375
00:20:56,000 --> 00:20:59,000
As sales... didn't really want to
do that this early, but we'll

376
00:20:59,000 --> 00:21:00,000
do it anyways.

377
00:21:01,000 --> 00:21:05,000
There we go. Execute that. There.
So now we've set up different...

378
00:21:05,000 --> 00:21:07,000
so we've got our sales order ID
and our due date set there.

379
00:21:08,000 --> 00:21:11,000
All right. So then we've added that.
Now we can... so let's...

380
00:21:11,000 --> 00:21:13,000
we're seeing all the rows.

381
00:21:13,000 --> 00:21:16,000
Let's see if we can control the
number of rows being returned.

382
00:21:16,000 --> 00:21:17,000
How do we do that?

383
00:21:18,000 --> 00:21:20,000
We do that with a request clause.
So we're going to do...

384
00:21:20,000 --> 00:21:23,000
again, we'll do... I'm going to
reuse this. What am I going

385
00:21:23,000 --> 00:21:24,000
to do here? I'm going to go up
and I'm just going to throw a

386
00:21:24,000 --> 00:21:28,000
where in, where we'll say sales

387
00:21:31,000 --> 00:21:36,000
order ID is equal or less
than or equal to

388
00:21:37,000 --> 00:21:39,000
we'll say 45,000.

389
00:21:40,000 --> 00:21:43,000
Let's do... this's looks
pretty... let's do 44.

390
00:21:45,000 --> 00:21:47,000
So now we're eliminating the number
of rows. We eliminated the

391
00:21:47,000 --> 00:21:50,000
number of columns. Now we're eliminating
the number of rows.

392
00:21:51,000 --> 00:21:54,000
And now you see that if we jump up
to messages real quick, you'll

393
00:21:54,000 --> 00:21:56,000
see that we have 342 rows.

394
00:21:56,000 --> 00:22:01,000
Now, if we ran this without the
where clause as we did before

395
00:22:02,000 --> 00:22:06,000
and go to messages, we had 31,465 rows.
So the where clause relates...

396
00:22:06,000 --> 00:22:10,000
helps reduce the number of rows
that are being returned to you

397
00:22:10,000 --> 00:22:14,000
and it's given very powerful with
managing that type of content.

398
00:22:15,000 --> 00:22:18,000
So we can also, you know, just
throw one more variation here.

399
00:22:18,000 --> 00:22:21,000
We can also do the order by. So
we'll order by kind of...

400
00:22:21,000 --> 00:22:23,000
we'll go with sales order ID.

401
00:22:24,000 --> 00:22:26,000
>> You like the sales order ID.

402
00:22:26,000 --> 00:22:28,000
>> Yeah, it's easy. I like working
with numbers better. I think

403
00:22:28,000 --> 00:22:29,000
that's what it is.

404
00:22:30,000 --> 00:22:33,000
So we're going to do that. We're
going to execute that and now

405
00:22:33,000 --> 00:22:36,000
it orders it or it sorts
it by order ID.

406
00:22:36,000 --> 00:22:39,000
All right. So just a few basic
select statements that kind of

407
00:22:39,000 --> 00:22:44,000
get your feet wet regarding performing
select statements inside

408
00:22:44,000 --> 00:22:48,000
of SQL Server and try to get you
comfortable with the idea we

409
00:22:48,000 --> 00:22:53,000
can use... we're going to be spending
time in SSMS in that we

410
00:22:53,000 --> 00:22:55,000
can highlight whatever we want
to execute and we can build on

411
00:22:55,000 --> 00:22:58,000
our commands as we're entering
these commands in.

412
00:22:59,000 --> 00:23:00,000
All right. Back into PowerPoint here.

413
00:23:02,000 --> 00:23:07,000
What did I do with my slides here?
Over here? I thought I already

414
00:23:07,000 --> 00:23:09,000
opened somewhere. Here it is.

415
00:23:11,000 --> 00:23:15,000
All right. So that's just a quick demo
on writing basic select statements.

416
00:23:15,000 --> 00:23:17,000
Just to review, we introduced the three
different types of commands,

417
00:23:17,000 --> 00:23:22,000
DMLs, DCLs, DDLs. We'll be working
with these throughout the day.

418
00:23:22,000 --> 00:23:25,000
We talked about different elements
like predicates and operators,

419
00:23:25,000 --> 00:23:29,000
functions, variables, commenting,
control of flow. And, again,

420
00:23:29,000 --> 00:23:32,000
we're going to drill into this
in more detail. We looked at

421
00:23:32,000 --> 00:23:35,000
the different elements and how
they are processed. We began

422
00:23:35,000 --> 00:23:39,000
with the from, the where, the group
by, the having, the select,

423
00:23:39,000 --> 00:23:42,000
and then the order by. That's the
order that they're actually

424
00:23:42,000 --> 00:23:45,000
processed when you're executing
a select statement. So that's

425
00:23:45,000 --> 00:23:49,000
going to just wrap up the very first
section where we're going

426
00:23:49,000 --> 00:23:53,000
to come in and we're going to do information
or provide information

427
00:23:53,000 --> 00:23:58,000
on the basic elements of what
we're doing with SQL Server.