Hey! this is Rajan Arora. Thank you so much Beau for posting my tutorial. I hope this will help all the viewers to tune their SQL Statements in MS SQL Server. I hope we have more collaboration like this in the future!
That was not just a good refresher to me but I also learned some new things, it's 4 hours long but definitely worth it, Well done Rajan, and thanks Free Code Camp for sharing,
As someone that has been writing code for 40 years I totally agree with your initial premise. If you are writing the SQL you need to understand how it operates and the costs/tradeoffs you may be making. Your video content was very well organized and presented.
Timstamps for various topics covered.. 10:08 Spooling: duplicate aggregation: 18:21 Hashmatch: unsorted data 23:54 KeyLookup: missing data ua-cam.com/video/t2R0-xcKw44/v-deo.html
I am a self-taught developer 😍 who after years of Js and React, and on my UA-cam channel 🧑🏻💻 I create projects with React and tailwind or Html and Css… right now I’m learning Python and as you well know, SQL is part of his world. This video comes at the right time 😎
Very elegant presentation with crisp and clear explanations. Thank you for the amazing effort. This helped me immensely and so would it have been for several others there I bet!
Timstamps for various topics covered.. 10:08 Spooling: duplicate aggregation: 18:21 Hashmatch: unsorted data 23:54 KeyLookup: missing data ua-cam.com/video/t2R0-xcKw44/v-deo.html
If possible, run multiple queries at the same time. Results will be easier to compare when you have several execution plans in sight and can just hover a mouse over them for comparison.
I executed the same query as the section 2.6 at (0:52:30) and got a different result that was an empty table. I wonder why ce.CourseOfferingId is specified as NULL. It supposes to have a not-null constraints. Would you please teach me?
The one I just mentioned (starting around 16:50) is also a bit inconsistent. He just talked about a clustered index, then kind of seems to be talking about a table scan, then says indexes will be covered later. When he says a scan operation will read every data page in the table, is he talking about a heap, or a table with a clustered index????
16:50 It is already wrong. It claims that a scan operation reads every data page in the table. Nope. It might, but it might not. A scan starts at the beginning (or ending) of the (clustered or non-clustered) index and reads 8k pages one at a time FOR AS LONG AS IS NEEDED. For example, suppose there is a table that stores information about people, and one of the fields is YearOfBirth, and an index is created on that field. A query with WHERE YearOfBirth < 2000 will start at the first page in the index (with, say, YearOfBirth = 1957) and read every page UNTIL IT READS THE FIRST PAGE WITH A ROW WHERE YearOfBirth is >= 2000: at point, no more pages will be read, because no more rows need to be read. SQL Server knows that no other page that is so far unscanned can contain a row with YearOfBirth < 2000, so SQL Server stops.
When he is comparing execution plans, why does he only look at estimated execution plans?? There can be a lot of useful information in the actual execution plan, which contains the actual values (actual number of rows read, etc.), which can differ a lot from the estimated values .... and such differences can be key in figuring out why a query runs slowly.
Hello @codeMechanic .. I am using M1 MacBook Air and was wondering whether I can do this course? I currently installed Azure Data Studio on Docker but not able to do the students.bak exercise thus won't be able to run the queries.. where can I find the data so I do an import?
I'm running SQL server 2017 and I don't get any results back when I run the query for 2.6. Also, if I compare the estimated execution plans for both queries I don't see an entry for "Top" with the query using "WHERE NOT EXISTS". For the first issue, I would guess that I could delete all records from the CourseEnrollments table where the CourseOfferingId is 56637 or 58317. With the second issue, I can only guess that my version of SQL is coming up with a different execution plan, and I have no control over that? UPDATE: Looking at the video again, I saw that there is an Index Seek on the CourseEnrollments table, so it looks like the database backup is missing that. I added the following index and my execution plan now matches what is shown in the video: USE [Students] GO CREATE NONCLUSTERED INDEX IX_CourseEnrollments_CourseOfferingID ON [dbo].[CourseEnrollments] ([CourseOfferingId]) GO
Here are some courses that may help you: SQL Course: ua-cam.com/video/HXV3zeQKqGY/v-deo.html MySQL Course: ua-cam.com/video/ER8oKX5myE0/v-deo.html University Database Systems Course: ua-cam.com/video/4cWkVbC2bNE/v-deo.html
Probably to an extent, but they are different database management systems. I’m sure a lot of this is applicable to other RDBMS like MySQL and PostgreSQL, though.
What's a great video, are concepts and strategies in this course applicable for PostgresDB ?? or for all SQL languages no matter whether it's SQL server or PostgressDB ????
I don't like SQL Server for example, I need to get a select result and generate script to insert based in values for the query to put in other table or database its impossible without payable tools. In MySQL its easier.
20:03 A grammatical error. It says "a criteria". Criteria is plural. That is like saying "I saw a cats eating food.". The singular form of criteria is criterion. It should say either, "access data by a criterion..." or "access data by criteria....".
I have a query, it works very speed but suddenly it's taking more time, I can optimize but I want to know the root cause why it works before why it's not now ?, can you please tell me what can I do to know the root cause
![SQL Course for Beginners [Full Course]](http://i.ytimg.com/vi/7S_tz1z_5bA/mqdefault.jpg)
![SQL Course for Beginners [Full Course]](/img/tr.png)
![[[ 8.5 HOURS ]] Complete SQL End to End - SQL Server Tutorial { End to End } Full Course Beginners](/img/n.gif)
Hey! this is Rajan Arora. Thank you so much Beau for posting my tutorial. I hope this will help all the viewers to tune their SQL Statements in MS SQL Server. I hope we have more collaboration like this in the future!
Thanks Rajan.. this is really one of the best vidoes i have watched on peformance tuning
@@abdulwahab-rw1yq Thanks for the feedback
That was not just a good refresher to me but I also learned some new things,
it's 4 hours long but definitely worth it,
Well done Rajan, and thanks Free Code Camp for sharing,
Could you please provide link where the DM queries can be accessed from
Thanks, Rajan This course is just excellent.. love it!
As someone that has been writing code for 40 years I totally agree with your initial premise. If you are writing the SQL you need to understand how it operates and the costs/tradeoffs you may be making. Your video content was very well organized and presented.
Thank you for the feedback
Timstamps for various topics covered..
10:08 Spooling: duplicate aggregation: 18:21 Hashmatch: unsorted data 23:54 KeyLookup: missing data
ua-cam.com/video/t2R0-xcKw44/v-deo.html
I am a self-taught developer 😍 who after years of Js and React, and on my UA-cam channel 🧑🏻💻 I create projects with React and tailwind or Html and Css… right now I’m learning Python and as you well know, SQL is part of his world. This video comes at the right time 😎
Very elegant presentation with crisp and clear explanations. Thank you for the amazing effort. This helped me immensely and so would it have been for several others there I bet!
Exactly what I need. Thank you, Mr. Rajan and FCC team.
u r channel deserves 500 million subs
I coincidentally just started my database course yesterday
Kindly create a full course on SQL Server - DBA
Outstanding video and resource, thank you so much for making this!
You guys are the best!
This was what i was looking for!
This is wonderful course. I have been waiting to learn these techniques. Thank you so much for making it.
Thanks for the feedback
Timstamps for various topics covered..
10:08 Spooling: duplicate aggregation: 18:21 Hashmatch: unsorted data 23:54 KeyLookup: missing data
ua-cam.com/video/t2R0-xcKw44/v-deo.html
This tutorial is absolutely great and helpful !!!!!.
Hope to see another vid about MySQL and Postgre !!
Thanks for the feedback
@@ICodeMechanic So, can I do it with Azure DataStudio on a Mac?
Learning about Databases is so important because almost everything in this world contains a database
True
Great Course. Thank you so much.
If possible, run multiple queries at the same time. Results will be easier to compare when you have several execution plans in sight and can just hover a mouse over them for comparison.
I executed the same query as the section 2.6 at (0:52:30) and got a different result that was an empty table. I wonder why ce.CourseOfferingId is specified as NULL. It supposes to have a not-null constraints. Would you please teach me?
Yes, I also got this, and the SQL Execution plan is completely different to what is shown in the video.
Thank you so much Rajan for what i exactly looking for .
💯
Thanks Rajan - great course!
I'm glad have found this jewel. Thank you very much.
The one I just mentioned (starting around 16:50) is also a bit inconsistent. He just talked about a clustered index, then kind of seems to be talking about a table scan, then says indexes will be covered later. When he says a scan operation will read every data page in the table, is he talking about a heap, or a table with a clustered index????
Awesome Rajan ji it is a great one very easy explanation
Wow great.this is what I was expecting.thank you so much
Congratulations, very comprehensive course.
Great tutorial we need more SQL Server tutorials, please in the future make a SQL Server DBA, SSIS, SSRS tuts thank you
I also want
Especially SSIS because jobs everywhere still ask for it but I can’t find resources to learn it.
Thanks for the feedback and your suggested topics are noted
פבחבןבפןב
יי פיב
Great job from you people.
@@sidch15 🙏
Very useful, Thank you !!!
16:50 It is already wrong. It claims that a scan operation reads every data page in the table. Nope. It might, but it might not.
A scan starts at the beginning (or ending) of the (clustered or non-clustered) index and reads 8k pages one at a time FOR AS LONG AS IS NEEDED. For example, suppose there is a table that stores information about people, and one of the fields is YearOfBirth, and an index is created on that field. A query with WHERE YearOfBirth < 2000 will start at the first page in the index (with, say, YearOfBirth = 1957) and read every page UNTIL IT READS THE FIRST PAGE WITH A ROW WHERE YearOfBirth is >= 2000: at point, no more pages will be read, because no more rows need to be read. SQL Server knows that no other page that is so far unscanned can contain a row with YearOfBirth < 2000, so SQL Server stops.
Thank you so much for very imp this and if you have any more like this then please upload it.
Top class quality video
Thanks for the feedback
Thank you so much for this course, exactly what I needed.🤩
great tutorial. Thank you very much!
Great course, exactly what I needed!
When he is comparing execution plans, why does he only look at estimated execution plans?? There can be a lot of useful information in the actual execution plan, which contains the actual values (actual number of rows read, etc.), which can differ a lot from the estimated values .... and such differences can be key in figuring out why a query runs slowly.
I love you guys ❤️❤️❤️
Can you please build a program for Postgres Performance essentials
Try my best to look into this.
@@ICodeMechanic Thank you
Wonderful.. thank you so much 🙏
Presentation was great.Can i get that ppt ?
is this course gonna help in to learn
msql server
Which version of sql server we will learn in this vide?
Hi..very useful video..Is there any training available for SQL performance tuning in dba or architect level?
Hello @codeMechanic .. I am using M1 MacBook Air and was wondering whether I can do this course? I currently installed Azure Data Studio on Docker but not able to do the students.bak exercise thus won't be able to run the queries.. where can I find the data so I do an import?
is there any book from where we can study all these?
I'm running SQL server 2017 and I don't get any results back when I run the query for 2.6. Also, if I compare the estimated execution plans for both queries I don't see an entry for "Top" with the query using "WHERE NOT EXISTS". For the first issue, I would guess that I could delete all records from the CourseEnrollments table where the CourseOfferingId is 56637 or 58317. With the second issue, I can only guess that my version of SQL is coming up with a different execution plan, and I have no control over that? UPDATE: Looking at the video again, I saw that there is an Index Seek on the CourseEnrollments table, so it looks like the database backup is missing that. I added the following index and my execution plan now matches what is shown in the video:
USE [Students]
GO
CREATE NONCLUSTERED INDEX IX_CourseEnrollments_CourseOfferingID
ON [dbo].[CourseEnrollments] ([CourseOfferingId])
GO
At 1:59:11, you have shown an index as lastname,firstname,degreeid. Will the 3rd sql query be able to utilize this index for degreeid?
Wonderful.
The back bone in either backend and front-end.
Thank you for sharing this good course.Can you share the ppt.
SSIS please!
Noted
Please post pl/sql course it will help many people
Here are some courses that may help you:
SQL Course: ua-cam.com/video/HXV3zeQKqGY/v-deo.html
MySQL Course: ua-cam.com/video/ER8oKX5myE0/v-deo.html
University Database Systems Course: ua-cam.com/video/4cWkVbC2bNE/v-deo.html
Noted
Great
Thanks
Great course, help me a lot👍
Where are the links ?
Hi sir, the tools which u are used in this video we can also use in oracle sql developer or not???
Sorry, but this is only for Microsoft SQL Server. it will not be used for Oracle Database
set speed to 1.5x⭐or you will sleep, although very profoundly and well
🙂 Thanks for the feedback
Request you to please make a Video on SOC or SIEM 🙏 please it will help lot of people like me who can't afford Expensive courses #freeeducationforall
KYA BAAT HAI
Does this work for MySQL database?
Probably to an extent, but they are different database management systems. I’m sure a lot of this is applicable to other RDBMS like MySQL and PostgreSQL, though.
No Shawon, it only works with Microsoft SQL Server
@@ICodeMechanicoh why? Because these are implemented very differently in other RDBMS?
Awesome
Another codecamp course that doesn't indicate the version in the title or the description, which probably means it is an OLD version of SQL Server.
What's a great video, are concepts and strategies in this course applicable for PostgresDB ?? or for all SQL languages no matter whether it's SQL server or PostgressDB ????
Did you find an answer for your question ?
Selent!!!!!!
❤️
We have only one like button 😒 Period
That Indian speaking make me smile)))
The Indian makes my ears cry.
I don't like SQL Server for example, I need to get a select result and generate script to insert based in values for the query to put in other table or database its impossible without payable tools. In MySQL its easier.
20:03 A grammatical error. It says "a criteria". Criteria is plural. That is like saying "I saw a cats eating food.". The singular form of criteria is criterion. It should say either, "access data by a criterion..." or "access data by criteria....".
Can you make solid works video plzzzz.
I’m sure they’re working on it. Solid is a fairly new framework, right?
bro became indian in minutes
Can't send 100 likes to your course 😂
Hi freeCodeCamp! I'd like to ask you if you can make tutorial for C++ desktop development. Thanks 😀
They already make it, go check it
I have a query, it works very speed but suddenly it's taking more time, I can optimize but I want to know the root cause why it works before why it's not now ?, can you please tell me what can I do to know the root cause
if youtube allows users to change the accent of the videos based on the scripts, that would be great
hay bruh
Hay bruh
hay bruh
Hay bruh
Indian people english. Suddenly close the video ...
Writing...
Olaaa
You are watching the video 😭
2nd
A+++++
Thanks for the feedback
Your mother
I have idea to make mobile application
Would you like to invest and become a billionaire
not working- download database backup files - students.bak is not visible to download. please check
The Students.bak file is empty