Intro to T-SQL - The Second Language Every Developer Should Know
HTML-код
- Опубликовано: 18 сен 2022
- SQL Server is an important database to understand as a developer, not just as a C# developer. In fact, it is one of the most popular requirements on job listings for developers. So I thought we would take a bit of time and dive into some important parts of SQL. In this lesson, we will look at T-SQL, the scripting language used to create, read, update, and delete data in SQL. While this language is fairly universal among relational databases, we will look specifically at the Microsoft SQL Server version of T-SQL.
Full Training Courses: www.IAmTimCorey.com
Mailing List: signup.iamtimcorey.com/
Love your work, Tim. Very informative and understandable. Keep up the good work!!
Thanks!
Thank you so much for creating this tutorial! I've been struggling to understand this concept for a while, but your explanations and examples made it so much clearer. I appreciate the time and effort you put into making this video, and I'm excited to apply what I've learned to my own projects. Keep up the great work!😍😘
Glad it was helpful!
This video gave me a good revision of sql quries and so many tips to keep in mind. I will be confident to handling queries now. Thank you for your hard work in posting such informative and well formatted video. I really learn quite a lot listening and watching your videos.
Excellent!
Nice intro Tim! While there's no undo button per se, if you need to update production manually for whatever reason and want a mini safeguard, you can make use of transactions. This is a hacky use of transactions but you'd first run "begin tran", then do your inserts/updates/deletes, then run select statements to verify your changes went as intended. If you want to save it, run "commit", otherwise run "rollback" to undo your changes.
That’s what we do to test changes, for actual production commits we produce a deploy and rollback script. The rollback takes probably twice as long to write due to tricky things like Id preservation etc but it does give that ‘undo’ feature.
Another problem you want to avoid is accidentally hitting execute before you're ready (although this may be just me - I was using a keyboard with an extra set of function keys, including another F5 key right next to the Ctrl key).
First fix - write your code offline (e.g. in Visual Studio).
If you must edit directly within SSMS, you can right click your query window and disconnect it so you get a 2nd chance to avoid execution by cancelling the connection dialog. This disables intelligence though so another option is to run "set noexec on", meaning your code won't execute (until you run "set noexec off") when you hit F5.
You really are AWESOME Tim. Thank you for all your help.
You are welcome.
Thank you so much Tim for doing this video!
You are welcome.
Thank you so much for sharing this useful video, Tim!!!
You are welcome.
Tim Corey, you are the best! Thank you for everything that you do for this community.
You are welcome.
Hi Tim. Thank you for this informative video. However, have you ever thought of dong a transactional replication on SQL using Scripting variables? may be you should consider doing one
SQL is soooo much fun, when it works. Thanks for helping me understand how to work it. It took me 7 weeks building relational databases to understand foreign keys, btw. Not easy stuff by any means in the beginning.
You are welcome.
Thanks for another video on SQL Server
You are welcome.
Yes, I agree 100% with the update statement happened to me in the production. Luckily I always do it once with rollback to check if I didn't mess it up and I had before table, after table one rows updated 230553 rows :D:D.
Yeah, seeing those big numbers for records updated is scary.
Thank you Tim Corey
You are welcome.
Thanks for delivering awesome contents, i wonder if you could make a video about EFCore db relationships (1:1, 1:many,...), seeding db with those relationships ,EF fluent apis, etc...
Thanks for the suggestion. Please add it to the list on the suggestion site so others can vote on it as well: suggestions.iamtimcorey.com/
Any chance you want to do a video about SQL indexes? I would love a video on those in your teaching style because I always struggle with them!
Thanks for the suggestion. Please add it to the list on the suggestion site so others can vote on it as well: suggestions.iamtimcorey.com/
51:20 I've been working in SQL Server for 8 years and I never knew you could do multiple inserts in one statement! Mind blown!
I’m glad you learned something new!
Thanks Tim! Great video. I'm not new to SQL but new to T SQL and SMSS. How do you turn on the hints as you type like that?
its automatically on as long as you are online and connected to the database
Hi tim, thanks for the video.
What makes u happy tim?
What motivates u to do a video every week in and out?
I like helping developers. I struggled to learn these topics when I was a new developer. I like being able to make the learning journey of others easier than my journey was.
Great explanation. Also, taking courses from your website. You must know someone from Elyria, OH because you actually pronounced the city correctly!
😂 Yep, my in-laws lived there for years (now they live with us in Texas). Technically, they lived in LaGrange, but even locals are more familiar with Elyria.
Thank you so much for your great lessons, Mr. Corey! You truly have a great boon of teaching others. I learned so much from you. Can you please tell me if it is a good idea to use transactions inside of the stored procedures? For example (in my case I have a Customer parent table and Addresses and Emails child tables) I am using transaction to insert data into several tables at once with one stored procedure because if I just insert Customer Id number of primary table to foreign keys columns of the child tables then Id of primary table is always equals to zero for some cryptic reason (I checked via debugging of my Blazor Server app) and insertion into Addresses and Emails fail (I am using Dapper by the way). If I use transaction this is not a problem anymore but some people write that it is bad to use transaction inside of the stored procedures. I don’t know why they say it like this.
Stored procedures use implicit transactions by default. Making that transaction explicit allows you to control the outcomes, plus it allows you to be explicit in what happens inside your transaction. They are absolutely fine to call inside stored procedures.
@@IAmTimCorey thank you for your answer, Mr.Corey! You really help me to feel myself more confident and not feel like everybody despise me and trying to bully.
I use TSQL procedures as an API layer for my applications, since the database is better at optimising queries.
You can even wire those procedures into Entity Framework, if required, but I prefer writing my own procedure calls.
That should make for a more efficient system.
The thing I don’t like about EF is it gets every column from every table and adds an “order by”, which just kills performance and will add larger memory grants. It’s a little better if you use hydrated objects.
Great content!!! Qq, How to fix the gap when id shifts from 4 to 1000? So if It comes back online and we wish to fix it, how do we do it?
You don't because there is nothing to fix. Here is a short video that explains in more depth: ruclips.net/video/h9ksr2ucNck/видео.htmlsi=aCKDHOBTn6_kUh5M
Hello ..Please i have some question ..i want to nkow if lt's better to use sorted procedure or in a app developpement.i am so confuse
Here is a video on that topic: ruclips.net/video/Sggdhot-MoM/видео.htmlsi=yFaIjul5nDg_79l4
Hi Tim, another great video! I won't give up on this - a 15-minute FOR JSON follow-up video to this one would be amazing.
select p.Id, p.Firstname, p.LastName, a.City, p.State,
(
select a.*
from dbo.Addresses a
where a.PersonId = p.Id
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) as Address
from dbo.People p
FOR JSON PATH
I think it should at least be shown as an option.
It is on the suggestion list ( suggestions.iamtimcorey.com/Details/626c98bdba4aa304a98cb160 ) if you want to upvote it (unless it was your suggestion).
@@IAmTimCorey It was mine lol
@@IAmTimCorey Unfortunately (and to my point), most people aren't aware of it, much less how powerful and useful it is. So it probably won't get enough attention on your suggestions site.
I don't strictly follow the "most votes gets the video". Instead, I review all solutions and use the votes as simply a weighting system. Like you said, if people don't know about something, they don't know to ask for it.
@@IAmTimCorey I created a document that I give my new employees on FOR JSON. Can I email that document to you?
Very basic, which is good thing. I kinda thought when I clicked you were going to show us how to replace C# using SQL ;-)
Hadn’t thought about it that way, but no. This is an additional language to know.
One question I have is around 42 minutes where it says on. If a person can use where, then what is the purpose of using on. Wouldn't it be easier to just use the where instead of on?
The "on" is used to link the two tables together. Technically, you can do the same thing in a where clause, but it is less clear and it makes it more difficult to read and understand the query. Use "on" to link the tables together and use "where" to filter the results.
My work has a RedGate tool that auto completes a lot of stuff and forces 'Screaming Text'. Lowercase code in our Stored Procs now is an indicator of how old the code I am looking at is. LOL
you should talk about transaction blocks, to make it possible to make a rollback
Thanks for the suggestion. Please add it to the list on the suggestion site so others can vote on it as well: suggestions.iamtimcorey.com/
“The key, the whole key, and nothing but the key, so help me Codd” … that’s was drummed into my head relative to putting “meaningful” data in keys
lol
@IAmTimCorey nice video but shouldn't you also instruct people to add a semi-colon at the and of SQL query statements ? That seems to be the recommended method of doing things.
I do that in the video. It is something that is not required, but has been recommended for a while. I've been doing this long enough to have had the pre-semi-colon ways burned into my brain. I sometimes forget to add it.
I understand it may sound good to say a query should be optimized and as simplified as possible. In my experience, those two words don’t always go together in a complex application. This is a good starter video but if you’re really interested in SQL Server, my advice is reading the Microsoft online documentation. SQL Server is very complex and understanding the beast is your best bet.
I would disagree that things can't be simplified and optimized. I think that optimization includes simplification. The key to being a senior developer is writing simple code that does complex work. The same is true for SQL. Simplified doesn't necessarily mean simple. You do need to make sure that your code is as simple as can be, though.
@@IAmTimCorey I agree that it needs to be as simple as possible. However, having a optimized query will not always look the cleanest. You may need to sacrifice readabily for optimization at times. It's also cheaper to have a poorly written C# application that is fairly cheap to horizontally scale vs SQL Server that is very expensive. Which is why it's a good rule of thumb to sort in your application because sorting on the database can be expensive.
I'm not sure where you are getting that "rule of thumb" but no, you shouldn't be sorting in your application instead of SQL. Absolutely sort in SQL. You might be thinking of subsequent sorts after the initial sort, which makes more sense to do in your application rather than re-transferring the data from SQL but otherwise no, let SQL do what it does best.
As to your point that application servers are cheaper than database servers so do all your work on the application side, that's true in ultra-large scenarios (eBay does this, for example). However, in almost every business environment, that is not necessary or valid. A SQL Server can handle a LOT of traffic before you should consider offloading work onto application servers. If you are having a problem, the issue is more likely a bad configuration on your SQL Server or bad queries (or a really bad database design). I worked for a company once that handled thousands of concurrent connections and millions of records that ran it all off of one SQL Server that wasn't even that beefy. The "trick" was that we had a SQL administrator that actually paid attention to performance and optimized our queries.
@@IAmTimCorey The rule of thumb comes from looking at SQL Server execution plans. The sort operator can be an expensive operator and can cause many problems like spills, large memory grants, reduced page life expectancy, IO, to name a few issues. This doesn’t even include the sorts that may get added to your execution plan without you even knowing. So anytime you can offload an expensive operator from SQL Server, it’s a good rule of thumb. So, I stand by what I said, and you should offload the sort operator from your SQL Server to your application when possible. My point where the work should be offloaded the application is true in all size organizations, not just large. If you have a small company and you have an in-house SQL Server, just upgrading the SQL Server hardware can cost you more money in licenses. It cost even more if you need to go from Standard to Enterprise edition. Even if you’re using a cloud solution like Azure, it can get very expensive very fast to turn that nob to turn up your DTU/VCORES. Especially with a company that need to keep their cost down or have small margins.
I would encourage you to spend more time learning how to optimize SQL calls instead of just falling back on the idea that you can do the job elsewhere. As a former DBA for dozens of companies of all sizes (I was a consultant for years), I cannot recall even one time where the right call was to offload a sort, nor can I recall a time when a SQL command was too much for the existing server except at scale. In those cases, the scale was massive and it was logical to upgrade the server. It was never logical to offload SQL processing onto application servers. SQL is meant to serve millions of calls without an issue. You just need to know how to manage it. I don’t believe Stack Overflow offloads any calls to application servers, yet it has a massive SQL database.
could you do a video about IEnumerator and IEnumerable?
Thanks for the suggestion. Please add it to the list on the suggestion site so others can vote on it as well: suggestions.iamtimcorey.com/
"You dont want to make C# do what its not good at which is effectively filtering or managing data"
EntityFramework: let me reintroduce my self.
Yeah, that’s the problem, right. EF does those things, but it isn’t the best at them. So we sacrifice performance for convenience, which can be fine until we need the performance. Then it is too late.
It's worth any developer learning T-SQL even as a reason to not have to use entity framework for your ORM.
Absolutely.
Miami of Ohio is in Oxford, Ohio, about 40 minutes north of Cincinnati
Good to know.
54:20 "Every developer have done this in developement (if they're lucky) or in production if ehm.. they are normal". . I guess I'm just normal them :-/ - First day on the job. Thank you for backup.
Absolutely. That's actually a really common day for things like that to happen.
55:00 Every time you need to update start by typing out Update set where. Now go back and insert the tabel, then values to set and then the where clause. If you for some reason happen to run the query before the where clause have been entered you'll just get an error instead for a wrecked database. There are other methods but this one have stuck with me since I made the mistake of not having a where claus. -- Also never have update statements with 'set' and 'where' are on different lines as shown in video. That is errorprone if you happen to mark the query fast and pressing F5. You could even up missing the where clause as Tim does on porpuse in the video. If it's on the same line you will most like end up not marking the entire clause and then get an error instedet for a wracked database table.
I'm good with a lot of protective measures, but I don't like the where clause being on the same line. That makes it harder to read. I also don't put every set statement on that same line, so there's even more reason why where on the same line doesn't work well for me. There is always going to be danger writing update statements. Since you should never write a statement that changes data (insert, update, or delete) in production first anyway, it shouldn't be a problem. Always write in dev and then copy over the verified code to production.
This is cool. However, I do want to point out one subtlety that burns beginners every damn time. In your example of the left join with the "where" clause referencing a column in the table being "left joined," it is worth pointing out that putting a "where" clause with a non-null condition against any column in the "left joined" table effectively turns the query into an "inner join." This is because when a SQL statement is executed, the engine performs--or at least ACTS as if it performs--the "join" first, and then evaluates the "where" clauses against the results. So in your example, the "left join" will (logically, at least) first generate the results as if the "where" isn't there, and THEN it will throw out all the results that don't match the "where" conditions, and this naturally excludes cases where the "left join" returned a "NULL" in the target column. Which is exactly the same as if it were an "inner join." So if you're running a "left join" and want it to behave as a left join, you almost NEVER want columns from the "left joined" table to appear in the where clause. The one interesting exception to this is that if you say "WHERE left_joined_table.column_name IS NULL," then that can be a useful way to identify rows in the primary table that have no corresponding rows in the "left joined" table, at least as long as the column_name from the "left joined" table that you specify in the where statement is one that cannot be null. There IS a clever way to put conditions on columns in the "left joined" table should you want to exclude particular results from the "left joined" table from showing up in your results while still retaining the "left join" behavior of the query--namely by making the conditions part of the join conditions, i.e., you make them part of the "ON" condition. This is subtle and a tad advanced. Sorry to butt in with this, which may be WAAAAAY more than anybody wants to know, but just in case.....
Guids are awesome, I did not quite understand why do you hate them.
I don't hate them, but I don't think they make sense in a lot of cases. For instance, let's say I have a Person table. If I use an incrementing integer for the ID, referencing that record manually is very easy. So, if I have an API that takes the ID to look up the record, I can test it by putting in 1, then 2, then 3. That's not the case with GUIDs. That's the human side of things. On the SQL side of things, the records in a table are sorted by their primary key when they are stored on disk. When you insert a new record, SQL has to figure out where to put that record. In the case of incrementing integers, it just puts the record at the end of the previous record. In the case of a GUID, it needs to figure out where the new GUID fits, then squeeze the new record in between the existing records. That requires memory manipulation not required for an ordered integer. Then, when it comes to linking two records, it is very easy to link two integers (both for the humans and for the computer). Linking GUIDs is a bit harder. The list goes on.
That you find upper case letters "scream" at you suggests you suffer from some mental health issue in this regard.
Huh? You do realize that's what uppercase letters mean when written, right? That the person is yelling? Or are you one of those people that leaves caps lock on to text/email people? By the way, here is an article about the origination of all caps and shouting: www.wired.com/story/all-caps-because-internet-gretchen-mcculloch/
Second
👋
Zeroth
👋
Bro put T-SQL and is teaching basic SQL 👀👀
What were you expecting in an "intro" video?
First
nope I am first
@@Hemecan. no it's me
👋
You can rename column names by just specifying the new name - ie select p.Id, p.FirstName GivenName, p.LastName,p.[State] from dbo.people p
Basically you can rename the column or the table/view names the same way - with or without the as clause.
select
p.id,
p.FirstName GivenName,
p.LastName
from
dbo.people p
and
select
p.id,
p.FirstName as GivenName,
p.LastName
from
dbo.people as p
Do exactly the same thing. The " as " are optional
You can, but it is very unclear when reading it. That's why I don't recommend it, or even show it off.