[Music]
>> Well, hello and welcome, live from Microsoft headquarters here
in Bellevue/Redmond/Seattle-ish area. We're going to be presenting
a Querying Microsoft SQL Server 2012 class with you today.
Thank you very much for taking time to join us. We want some
interaction for you to be able to kind of give us some feedback,
so as you're viewing the presentation throughout the next several
hours, to the right of your screen there's a chat interface in
there, feel comfortable replying or sending in some comments
to there or some chats to there. We've got tons of SMEs, subject
matter exerts, working behind the scenes with us. And myself,
which is Brian Alderman, myself as I will be presenting, we will
also be monitoring the chats as well here from the studio.
And the cool thing is if you type in a really good question,
it will be sent up to us and we'll take that question live and
get that answer to you. The good news is you're not going to
have to listen to me all day alone. I also have joining me in
the studio Tobias. How you doing?
>> Not too bad, Brian. Feels good to be here.
>> It's good to see you. Thanks for joining us today. And what
we're going to talk about, I want to make sure everyone understands
what you've gotten yourself into. This is more of a Level 300 class.
And we're trying to meet the audience that's trying to learn
the technical skills required for some of the advanced Transact-SQL
queries within SQL Server. So you should have a basic understanding
at least of relational databases, understanding the use of the
SSMS, or the SQL Server Management Studio, because we're going
to be spending a lot of time in there, and then a basic knowledge
of some of the Microsoft Windows operating system and some of
its core functionality. Let's take a look at what we're going
to discuss throughout the day here. Or evening, depending where
you're dialed in from. We're going to begin with just an introduction
to SQL Server 2012. We've got a couple modules, just kind of
levelling the playing field to make sure everyone understands
what it is that we're going to address throughout the day, and
some of the terms and concepts that we'll be addressing or using
throughout the day. We'll then move into the second module and
start looking at some of the advanced select statements, things
like the distinct clause, the cases, the joins, the merges, filtering
and sorting data, and how do we deal with those pesky null values.
We'll move into data types. Because, as you know, when we build
out new objects in SQL Server, it's very important for us to
understand the appropriate data type and apply that data type.
So we'll talk about that, the conversion of data types, and actually
introduce some SQL Server functions.
We'll then move into grouping and aggregating data. And in that
area we'll talk about group by and having clauses. We'll talk
about subqueries. We'll talk about different views, inline tables,
valid functions, and derived tables. So some of the more advanced
titles we're talking about grouping and aggregating... aggregating data.
We'll then break for lunch. We're posting up there roughly 60 minutes.
It may be 45. It may be 60. It may be two hours. No, it won't
be two hours, I promise. But will be a roughly 60-minute lunch
hour, depending on what time we are, where we are in the curriculum
as we go through today's event. We'll then move in after lunch,
or roughly somewhere throughout the day, we're going to talk
about set operators.
Set operators are we're going to include Windows functions, grouping
sets using pivot and cubes and rollups. We'll introduce those
topics and talk about those for you. We'll talk about modifying
data, because it's not all about retrieving data, we need to
know how to properly insert, update, and delete content.
And we'll look at the use of constraints, default constraints,
triggers, to make sure that when we're adding data, we're kind
of enforcing data integrity by setting up different defaults,
constraints, and triggers. We'll look at programming with T-SQL, Transact-SQL.
We'll look some of the programming elements, some error handling,
and understanding and implementing transactions within your programming environment.
And then we'll wrap up today's class with retrieving SQL Server
metadata and improving SQL Server query performance. So we'll
look at things like system catalogs, dynamic management views,
or DMVs. We'll talk about creating and executing store procedures,
and then an overall understanding of ways that we can improve
the SQL Server performance specifically when we're querying.
So that's what we're going to take a look at. We're going to
begin, and like we said, with an introduction to Microsoft SQL Server.
Again, feel comfortable using that chat to the right. If you
have any great questions for us, feel free to chat in to us so
we can get any answers... any questions answered for you as we
have a plethora... I love using that word... a plethora of SMEs
working behind the scenes working with us. And Tobias and I
will also take some live questions as they come into us. So let's
go ahead and begin with an introduction to Microsoft SQL Server.
And this is going to be Module 1. And, again, kind of like just
kind of laying a foundation and understanding or kind of levelling
the playing field for people. We're just going to talk a little
bit about some of the different types of commands and statement
elements that we have out there, and then look at a couple basic
select statements where we can actually go in and talk to these...
the basic select statements that you might perform.
So three types of commands that you can find out there. We have
DML, or data manipulation language. And with this class being
querying SQL Server, the primary focus is going to be on DML
specifically the select statement, but we are... as we talked
about, we are going to talk about the insert, the update, and
delete commands, which falls in that data manipulation language
category of statements. We're also going to talk about DDLs,
how we create objects, whether it be a database or a table or
a store procedure. And after we create we may need to modify that.
We'll talk about ways to alter that. And eventually we may have
a reason to drop that. Now, my goal is to change that drop command
to the toast command. So I'm trying to get Microsoft to change
that from drop to toast. I'm going to be working with Tobias
on trying to get that toast command in there instead of the drop command.
Is there any way we can get that done by the end of the class?
>> No. Okay. I'll keep working on that. I'm not going to take
no as an answer yet. So if you all are in favor of having a toast
command instead of a drop command, please let us know about that,
and I think we'll see maybe if we get enough heat on the development
team in SQL you get a toast command added for the next version.
>> I have been trying to tell Brian about this NC standard thing
we have going and trying to stay close to standards, and apparently
they just haven't come to toast command just yet.
>> Well, I think you could be setting... you could be setting
up new ground for people. Maybe everyone would change to the
toast command from the drop command. It's a cool command.
We're going to use toast a lot today. Unfortunately I've got
to type in drop, but when I type in drop, just think I'm thinking
toast when I'm typing that in.
>> But there's not a drop command... there's not a toast command.
All right. We're also going to talk about data control statements.
Those are where we control access to the different objects within
the SQL Server databases. So those are DCL commands. We'll touch
on those as well. So three different categories of commands that
we'll be using.
DML, DDL, and DCL. Now, we have some T-SQL language elements
that we're going to be using, we're going to drill into a little
bit more detail. I'm going to introduce them and then let Tobias
kind of just talk a little bit, a little bit more about these.
But we have what are called predicates and operators that we
use when we're writing T-SQL commands, different functions that
we can call. We may want to reuse something like a store procedure,
so we may want to pass variables. We have different expressions
that we'll be able to use, ways to have a batch job and we can
supply separators in for that. We want to maybe control the flow
so we could use things like an if/else statement. And then of
course we want to comment because we're always good about commenting
content, so we want to make sure we can add comments. And we're
going to show you how we add comments to this content. So I'm
going to ask Tobias to kind of walk us through some of these
elements that we can use when we're working with T-SQL.
>> Thank you, Brian. Sure.
So what you're seeing there is kind of the standard things you
would imagine in any programming language, right?
We have predicates. That includes the common operators, the common
comparison operators including like, which is a special string
kind of operator. We have three logical operators in not, and,
and or.
And we dig into how you use all of these as we go through the class.
And I think one thing that's interesting for us to kind of focus
on is what's best practices, how should you use the language,
what should you think about and what may you want to avoid for
various reasons. It's all very context sensitive. Right?
So using the right thing at the right moment is clearly going
>> All right. So those are the predicates. Now, also we have functions.
You see we have three categories of functions. We have string
functions, something like if you want to extract a portion of
a string or we want to look at the length of a string. We have
date and time functions. We want to go out and grab the system
date, we want to maybe check and add a certain time frame to
a day. And then we have aggregate functions. We want the average
sale for a particular salesperson or the total sales for a particular salesperson.
Those fall under the aggregate functions. So we can use these functions.
And, again, we'll see these as we go out throughout today's presentation.
And we'll see these actually in use. Now, the idea of using
variables is very powerful because this gives us the opportunity
to reuse items like store procedures that we've created and be
able to pass different variables or different values with these variables.
So we'll see how we can declare a variable. Very straightforward.
And we can also now not only declare it but we can also initialize
at the same time beginning with SQL Server 2008, in fact.
So we'll talk about the use of variables and how powerful they
can be to make it a little bit more flexible and dynamic, for
instance, store procedure, if we're trying to use that. We have
some expressions here. Like we're doing a select, a year, the
order date plus 1, we have select order quantity times unit price.
So we can get very creative with our select statements. We don't
have to just do select asterisk from a table name and walk away.
We can actually go in and get creative and kind of customize
that result set. And we're going to look at some of the opportunities
we have for the customization of that result set by using some
of these expressions that we have available to us. And the use
of batch jobs are really powerful because this allows us to be
able to go in there and script content, and then we can schedule
that content to run at a particular time. And if we have multiple
statements, we may often have to separate them. We most often
do that with the go statement. Now, go is not specific to a SQL
Server T-SQL command. It just allows us to go out there and separate
the different batch jobs or commands we have within a batch job.
>> And it's important to know here with go that it's just something
that the tool sees such as SQL Server Management Studio. It sees
go and it knows that whatever was before go I'll send separately,
wait for a response from the server and then I'll send the rest,
right, and do this between the go. So SQL Server itself, like
Brian mentioned, doesn't really know what go is. It's different
tools may use different batch separators. But all our tools
default to go as the batch separator.
>> Some other elements we can use are control of flow. I'll be
talking about this briefly. If/else, the while, begin end. Error handling.
We're going to look at some ways to catch errors and how we're
going to be able to respond to those errors. We'll look at those
later on today. And, again, we'll spend some time talking about
transactions, how to define a transaction within a batch, for
instance, used in a begin transaction, committing that transaction,
and potentially having to roll back that transaction.
You're going to want to document, especially start getting into
batch jobs, you're going to want to document what's going on
in there. This gives you the opportunity to use what's called
a block of commented code. This allows you to set up comments.
As you can see we have a forward slash asterisk. Anything I
say after that is a comment until I get the asterisk forward
slash again. That closes that block of comments out. If you
want just a line of comments, we can use the dash dash. And then
you'll just... that line will not be executed. So if you're
working on troubleshooting a particular script and there's certain
code you don't want to run temporarily, you can kind of comment
that out using the block of commented code or these two dashes,
and they'll ignore that temporarily. Or if you just want to
document what you're doing in there, you know, as the batch job
is running, you can actually add some comments to that documentation.
Or as documentation to that script or that batch job.
Now, the idea of actually executing a query is pretty... if you
understand that you got this select, you have the from, you have the...
possibly the where, maybe the group by, maybe the order by.
Often we look at the way it's written and we think that's the
way it's actually executed. What we want to show you here is
the logical order of the query being processed. So the first
thing that's going to happen is we're going to jump out to the from.
We're going to find out to make sure...
>> So, Brian, just why do I care? Why is it important with the
logical query processing?
>> Because you want to make sure that you understand when you're
writing this information, and if you're troubleshooting and trying
to optimize it, if you understand the order that these options
or these statements are being executed, it might give you the
opportunity to optimize that and make it run a little bit faster.
>> Yeah, that's good. And it's also... it's also important to
know the logical query order because it helps you understand
exactly what needs to go on under the surface, right, under the covers.
So if you don't know exactly how the query language is structured,
right, then you may end up not getting the results you want.
So it's not... it doesn't mean that whatever you type is exactly
what we are going to do, because we have this fancy query optimizer
that figures out the right way, the best way to give you the results.
But it does mean that we are constrained so that we will always
logically execute in this order. So it's super important to
understand the logical query processing.
>> So it actually begins with the from statement and then it goes
to the where clause. I'm just going to bring these up, you'll
see, as they're numbered here. Group by, having, and then it
goes to select. Oddly enough, the first thing that we write is
the select, but that really isn't a... you know, isn't addressed until...
well, going to call it step 5. And if there's an order by, that's
executed last. So, you know, it's good to understand that order,
that processing order so when you're writing your queries or
if you're trying to optimize your queries, you can see where
that information is coming from.
>> And we get into some more details on logical query processing
later, because there are some other elements to the language
that are in addition to what we're showing on this slide.
>> So this is applying the logical order of operations here.
We've got this statement here, so let's start from the top, use
adventure works. We also... we identify what databases we want
to use, and so I always begin a statement with a use so I make
sure I'm in the appropriate database.
Then we'll add the from.
This is actually sales.salesorderheader.
Where customer ID equals a certain number. Group by salesperson.
And then we have to grab the year off the order date.
Having count greater than 1.
And then we execute, then we have the select portion. We're not
going write the statement this way, but, again, this is how a
statement that we have written will be processed by SQL Server.
And finally wrap up with the order by.
>> Yes. So one thing I just wanted to mention on use is we have
two big products out there with regard to SQL, right? One is
SQL Server 2012, which is our on-premise product, and then we
have Windows Azure SQL database, which is our cloud offering
in Azure. And the use statement itself is not supported in SQL
database because of what we sell is something different from
SQL Server. We sell databases rather than instances.
So that's important to know. If you want to create software that
runs nicely on both of the platforms, you should not use the
use statement within your program. You'll very often use it
in SQL Server for debugging or for testing your scripts, right,
but within your software, your app that you write, you may want
to avoid using use. The other thing that's interesting here is
if you're interested in it working on both our Azure platform
as well as on our on-premise platform, you should look at trying
on both. And if you develop against Windows Azure SQL DB, you'll
be able to run on SQL Server as well.
>> Are you suggesting they should test the code before they deploy it?
>> Really. Okay. Because I didn't know we were supposed to do that.
So all right. All right. Let's take a look at some basic select
statements again. Some of these are more advanced and might be
familiar with this, but we just want to make sure everyone's
on the same page here. So we have a select, we supply the select
list, the from, the where, the group by, the order by.
We're going to see plenty of these.
Here's where we're looking at retrieving call in from a group.
We can actually just use select customer ID, store ID from the sales.customer.
So we're identifying the object in the schema for that object.
So that's about as basic as you can get. Well, you can get a
little bit more basic. Instead of identifying the columns, you
could swap out the column names and put a asterisk in there.
That's the most basic select statement you can write.
Select asterisk from table name. We're actually taking it up
>> We can make it easier.
>> Make it easier than that?
>> Yeah. Select space 1.
>> Select space 1. That's...
>> That's probably the easiest you can go.
>> Okay. Well, okay. So try that. If you want the easiest statement
so you can actually go out and brag that you've actually submitted
and executed a select statement... what is it, select space 1.
>> And it's actually not... it sounds like trivial and stupid
and why would you ever do that, right, but it's actually interesting
when you want to do performance tuning, if you're interested
in what is the performance of just speaking to SQL Server, like
network, all of these things, rather than going into what actually
happens in the database engine, select 1 is a perfect thing to test.
>> Awesome. Definitely. Select 1.
All right. Using calculations. So as you can see here, we've
select unit price order quantity and then we're performing a
calculation unit price times order quantity and we're going to
grab this from the sales order detail table. So we can actually
use this. And we have different calculations. Right now we're
doing a multiplication here. But we can do an addition, we can
do division, we do percentages. So we have these different calculations,
scalar calculations that are available to us. We can also do
a concatenate. We can concatenate content. Again, we're going
to see some samples of these. We're just kind of introducing
some of the topics that we're going to go through here.
All right. Let's take a look at some basic statements here.
So I'll come out of the
PowerPoint slide and jump into an image here, then
go into SQL Management Studio. And again we've got adventure
works and we're already using adventure works. So we can do
a select again.
>> 1, 1, 1.
>> 1. I'm going to do that one first just so everyone sees that
this works. And the cool thing, if you're not familiar with this,
you can highlight this and just execute it.
And you got no column name but you get the one returned. So as
you've said, Tobias, we've gone out, we've communicated with
SQL Server, we've got a response. So we know that things are working.
We're able to communicate. Now, you can also take it up a notch
and go and select asterisk from we'll say sales...
sales... and the cool thing with intelli-sense because I couldn't
remember exactly what I wanted to go to, we can go to sales or
table, order header, and then we can... again, we'll highlight
that and execute just that command.
And now it gives us a bunch of information here. Probably too
much because now I've got every row and I really didn't want
every row of... or every column, excuse me. I wanted just a
few items about the rows in here. So we can change this again
to do a select. We'll grab sales order ID.
Let's actually do sales...
we'll do it... spell it out all the way.
And we'll grab one more.
We'll grab due date just so we stay with it here.
Due date. And then we'll specify here
from the sales.salesorderheader and I'm going to double click
here, save a few keystrokes. So we'll again highlight that and
execute that.
>> As sales.
>> I'm sorry?
>> You'd have to says as sales for... you can try.
>> I didn't hear what you said. Sorry.
>> You have to specify the table alias since you added it...
you're using sales dot.
>> Oh as sales. Gotcha.
>> Since you added that.
>> From sales.sales.duedate, sales.orderheader.
As sales... didn't really want to do that this early, but we'll
do it anyways.
There we go. Execute that. There. So now we've set up different...
so we've got our sales order ID and our due date set there.
All right. So then we've added that. Now we can... so let's...
we're seeing all the rows.
Let's see if we can control the number of rows being returned.
How do we do that?
We do that with a request clause. So we're going to do...
again, we'll do... I'm going to reuse this. What am I going
to do here? I'm going to go up and I'm just going to throw a
where in, where we'll say sales
order ID is equal or less than or equal to
we'll say 45,000.
Let's do... this's looks pretty... let's do 44.
So now we're eliminating the number of rows. We eliminated the
number of columns. Now we're eliminating the number of rows.
And now you see that if we jump up to messages real quick, you'll
see that we have 342 rows.
Now, if we ran this without the where clause as we did before
and go to messages, we had 31,465 rows. So the where clause relates...
helps reduce the number of rows that are being returned to you
and it's given very powerful with managing that type of content.
So we can also, you know, just throw one more variation here.
We can also do the order by. So we'll order by kind of...
we'll go with sales order ID.
>> You like the sales order ID.
>> Yeah, it's easy. I like working with numbers better. I think
that's what it is.
So we're going to do that. We're going to execute that and now
it orders it or it sorts it by order ID.
All right. So just a few basic select statements that kind of
get your feet wet regarding performing select statements inside
of SQL Server and try to get you comfortable with the idea we
can use... we're going to be spending time in SSMS in that we
can highlight whatever we want to execute and we can build on
our commands as we're entering these commands in.
All right. Back into PowerPoint here.
What did I do with my slides here? Over here? I thought I already
opened somewhere. Here it is.
All right. So that's just a quick demo on writing basic select statements.
Just to review, we introduced the three different types of commands,
DMLs, DCLs, DDLs. We'll be working with these throughout the day.
We talked about different elements like predicates and operators,
functions, variables, commenting, control of flow. And, again,
we're going to drill into this in more detail. We looked at
the different elements and how they are processed. We began
with the from, the where, the group by, the having, the select,
and then the order by. That's the order that they're actually
processed when you're executing a select statement. So that's
going to just wrap up the very first section where we're going
to come in and we're going to do information or provide information
on the basic elements of what we're doing with SQL Server.