SQL Stored Procedures - What They Are, Best Practices, Security, and More...
HTML-код
- Опубликовано: 28 май 2024
- Newsletter signup (with exclusive discounts): signup.iamtimcorey.com/ (your email will be kept safe and you will not be spammed).
Facebook Page: / iamtimcorey
Want to see a full C# application built from scratch?
Check out this course: iamtimcorey.com/courses/cshar...
Microsoft SQL can be confusing. There are a TON of features, options, and settings to consider when attempting to use it. One of my favorite features is stored procedures. These pre-compiled queries allow you to safely and securely access and modify data. In this video, you will learn the basics of how to build a stored procedure, as well as what the benefits and drawbacks of stored procedures are. You will also learn some best practices, including how to secure your entire database in just a few simple commands.
Thanks to Ralfs HBK for timestamping the video:
0:00 - Intro
1:12 - Demo Data Base overview
2:00 - Creating new stored procedure
7:35 - Calling the stored procedure
10:11 - Changing existing stored procedure
11:12 - Stored procedure with variables
16:51 - Stored procedures: Recap
19:54 - Benefits of stored procedures: Security
22:57 - DB security: Users and Roles
32:49 - Other benefits of stored procedures
34:18 - Drawbacks: No source control
37:36 - Drawbacks: Business logic in DB
41:48 - Drawbacks: Using Entity Framework
43:26 - Summary and concluding remarks
Out of all the years of watching computer and programming tutorials, you teach things so much better and explain everything and explain it in a way that makes sense, thanks!
I am glad my training is helpful.
Seriously this is the best channel about programming, at least for me, hope you continue with these videos that are really helpful. Thank you very much!
I will definitely be continuing. I'm glad you are enjoying the videos.
Amazing video! Not only do you explain what stored procedures are, but you also give detailed background information and use cases. Keep up the great work!
Thanks! Will do.
Very easy to follow introduction to stored procedures. Just enough depth to get the general idea without getting bogged down in the details. Thank you for the great free content you provide!
You are most welcome.
This is by far the best video i have ever seen on this topic, Thank you Tim
You are most welcome. Thanks for watching.
Im a professional, experienced developer and theres always something to learn from your videos, even with 'easy' topics such as this one. Thank you for your videos. Great work.
I appreciate the kind words.
thats why its so overrated, in uni they think we need to know everything xd then i see people saying who are experienced developers that they learn from the simple videos xD
Hi Tim! You explain things and make it so simple, thank you !
You are welcome.
The way you explain things is amazing.
Thank you!
Thanks for pointing out that security matters! It's such a big deal today yet I still see so many tutorials where they completely disregard security matters.
You are most welcome. Thanks for watching.
Hi Tim, I've watched a dozen of your videos and they are very useful. I'm also learning from you how to explain complex technical ideas in plain English to non-technical audience and even to junior programmers. Thank you! From Guangdong China.
Awesome!
Fantastic video! Just had a recent interview for web dev in the financial sector and I have fairly little experience with T-SQL. One of the things they asked for me to do is additional research on Stored Procedures and this is the most clear and comprehensive guide I've seen thus far. I really appreciate it and I hope you're happy to hear how helpful you are for helping me get my next job!
Awesome! Glad I could help.
Thanks for the lesson. I enjoy that you teach more of the WHY and not just the HOW.
You are welcome.
I'm a MySQL user but I've learned a lot from this video and hope to apply my understanding to MySQL.
That bit about granting regular users only access to stored procedures but not access to entire tables, and then only granting them an execute permission, is the crux on which I hope to predicate a solution to a problem I'm working on. Sorry I know I should not end a sentence on a preposition.
Whereby, I gotta make some data accessible to people who don't really know how to write SQL.
Hopefully I'm smart enough to do the job.
Thanks for the video sir.
You are welcome.
This was one of the most understandable, cohesive, well structured and best introductions I have seen in my whole life. I came with no understanding of anything besides SQL-statements and programming (java etc.) and I feel, I have a decent idea of procedures and role-management now.
Fantastic! Thanks for sharing. Please check out my other training videos - ruclips.net/channel/UC-ptWR16ITQyYOglXyQmpzw
I was just preparing to move my classical ASP app ( including SP’s) to MVC with EF. Of course, I’m pleased with speed of execution of SP’s, especially in reports, but DB dependency is another consideration for me. I watched so many videos, even bought some trainings. Then I watched this video. It’s a perfect eye opener for me. I’ll watch your tutorial at first, then decide which way to go. Thank you this valuable information. Best regards.
I'm glad to be of help.
I've been watching your video's since the start of my programming journey 1 and a half years ago. Thank you for yet another great tutorial!
You're very welcome!
Clear and efficient explanations. One of the best tutorial I saw till now on this subject. Thank's very much!
You are most welcome. Thanks for watching.
Thank you so very Much Tim. You have contributed so much to my career, truly i can't thank you enough, though i am yet having not much enough to subscribe to your paid programs and articles, i am looking forward to someday.
This video is great, i often used Stored Procedures in my code but i found them to be time consuming and extra work to face so i began to do more of the unsecure query, query thing you know... but with this video, i think i will redirect my energy towards understanding it more and leverage on its benefits. Once again, Tim, Thank you a ton.
Tim, thanks for another great video!! Although I am now retired and never employed formally as an "engineer", much of my career was around managing a business that was highly secure and mission critical service network. Stored Procs are the way of life in such environments and, like you, NOBODY felt comfortable enough to executed the Entity Framework in such an environment. The business had to be able to fix ANYTHING that happened in the shortest time possible and performance was an issue. With about 1.5M connected end points sending in asynchronous signals, you can imagine how much data was coming into the network and why performance and security were so important. Kudos to you - you are correct about security!!
Thanks for sharing based on your experiences. This type validation helps others understand the importance of the lessons and concepts Tim shares.
Hello Tim I have been watching your videos for a couple of weeks now. Thank you for creating great content and make it easy to understand.
That has always been Tim's goal. Please check out his full channel - ruclips.net/channel/UC-ptWR16ITQyYOglXyQmpzw
You are welcome.
This, and the rest of your videos the videos on this channel, is absolutely golden! The SQL course on your site is worth way more than you're charging. You and Derek Banas are The best source for this stuff. Thank you very much, Corey!!
Thanks for the compliment. I appreciate it.
Great channel, excellent mentor, coach, and trainer without a ludicrous price tag. Thank you, Thank you, and Thank you.
You are most welcome.
I've been using Stored Procedures for a least 20 years now, so watched more out of curiosity than anything else. However you never know what you might learn and believe me I have learnt plenty from your wonderful videos. The best of this for me was your commentary on entity framework which just reinforces my belief that I'm better off doing what I've always done. Each to their own I guess, but with so many people now only using entity framework I feel like a dinosaur because I'm not.
Awesome! I'm glad it was helpful.
We're not using EF in the company i work for and it's so difficult to find tutorials for adding that data layer without it! So definitely not a dinosaur 😆
Sir need your help to understand procedure n fuctions
I love working with stored procedures. The front end guys would argue with me how having it in c# was better. Until you try to read their logic and say those 20 lines can be done with a single case statement. Thanks for sharing great work
44 😮
Srs. You are one of the best explainers and teachers out there. So precise and slow enough to follow. And the concepts are presented so nicely. Great job man! :)
Thanks for your trust in Tim
First day at work. So glad I found you!
I hope this channel is a lot of help for you.
This is the best tutorial series I have seen. Its ridiculous that you have less than 100K subscribers. You should have at least 500K subscribers.
I appreciate the kind words.
Very clearly and professionally explained. Thank you so much Tim.
You are welcome.
Thank you tim for all your amzing content, i got a job in IT and your videos were super helpfull both in recruiting and during actual work.
Best of luck for you and thanks for helping me turn my life around !
Cheers from Portugal !
Great to hear! Thanks for sharing.
0:00 - Intro
1:12 - Demo Data Base overview
2:00 - Creating new stored procedure
7:35 - Calling the stored procedure
10:11 - Changing existing stored procedure
11:12 - Stored procedure with variables
16:51 - Stored procedures: Recap
19:54 - Benefits of stored procedures: Security
22:57 - DB security: Users and Roles
32:49 - Other benefits of stored procedures
34:18 - Drawbacks: No source control
37:36 - Drawbacks: Business logic in DB
41:48 - Drawbacks: Using Entity Framework
43:26 - Summary and concluding remarks
Thank you kind sir!
This is a great video. Excellent work as usual Tim Corey.
Thank you!
Almost 3 years later, i found this, and... well...subbed and THANK YOU! It helped me a lot!
Ok, i reached the end of the video now, and i just have to THANK YOU AGAIN! Awesome, really, you made it click so many times in my head and filled me with confidence, awesome awesome awesome! Thanks! Please continue doing this, i think i will watch a good chunk of your videos from now. :)
I am glad it was so helpful!
Dear Tim, I think You have a precious gift to explain concepts.
also, using Cached execution plans is one of the most important benefits of Stored Procedures
Thanks!
This is the first time I've ever seen a meaningful use of stored procedures.Thank you so much Tim Corey.The sql databases are most of time shrouded in secrecy and most of developers just know the fundamentals of them .I have been struggling for longtemps to level my expertise in sql databases up to my mastery in Mongodb but just in vain: I am just left with fundementals.It is really crazy.
I am glad you found it valuable.
I never watched about procedures before. And I thought it will be necessary to watch several tutorials. But you made me understand watching only one. Thanks
Excellent!
Great explanation of SP in such a simple way. Thanks Tim
Glad it was helpful!
This was very informative Tim. Thank you so much.
You are welcome.
Very informative and useful. I appreciate your candor about EF at the end.
Thanks!
Waiting for a full course on SQL by you, because it's brilliantly explained!
I do have a full SQL course: www.iamtimcorey.com/courses/accelerate-microsoft-sql/
this class on stored procedure basics was very helpful. thank you!
Glad it was helpful!
You are doing the lords work sir. Thank you, I needed this for a project at work.
Thanks!
Some of the best training videos. A great starting place.
Thanks!
This was a very good video, a lot of the stuff I knew from your other videos on SQL, but I learned a lot of things about Security!
Glad it was helpful!
Your videos/tutorials are very useful. Thank you for your work!
You are welcome.
I have a clear pic of what a stored procedure is by watching this amazing content. I clicked on subscribe option before even close to watch this video. Please keep uploading...
I am glad it was so helpful.
Thank you very much Tim! I learnt so much about stored procedures in 44 minutes than I did in my entire semester. Please upload a video about triggers and functions if possible. I'll be looking forward to it. 💪
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/
Hi Tim, this is a really fantastic video and you do such a great job of clearly explaining everything! I’m currently studying my MCSA 70-461 and this has helped massively! I wonder if you have any videos on Triggers and Transaction Isolation Levels?
I don't, sorry.
Omg, after watching your video, SQL Stored Procedure is on my mind. Thank you.
You are welcome!
seriously one of the best videos I ever watched with a great and detailed explanation.
Glad it was helpful!
Thank you so much for this tutorial. Concepts are so much clearer.
You're very welcome!
Excellent tutorial as always.
Thank you for taking the time putting it together.
Regards
You are welcome.
Thank you so much for your brief explanation.
You are welcome.
It is the only yt channel where I always give a like button before I start watching. Many thanks for all tutorials !
Glad you like them!
Just want to say, I love your videos and I totally understand where you are coming from. I've worked on systems that used both SP and Linq to EF. Using SP when performance becomes an issue. But Linq is definitely very addictive because it's easy to read and compose complex queries conditionally. But it's also where the performance issue crops up because it's so easy to nest subqueries without thinking about it.
Yep, there are a lot of hidden gotchas.
Thank you very much! Your explanations are great and amazing! Solid content Tim 🔥
You are welcome.
Thanks a lot Tim about this great video. I personally build all my ASP.NET projects on simple and complex stored procedures and I will always keep using them, all the reasons you have mentioned are correct 100%, I feel very confident and relaxed when I use stored procedures, and once the stored procedure does its job, I know that it will never give me any surprised error in the futre, they are very controllables, what you write is what you get and no code is behind the scene also they are independant from each others. Great job Tim. Please keep it up as usual.
Thanks for sharing
@@IAmTimCorey Thanks for your efforts dear.
Hi Tim I just want to tell you're great developer really Thank you
I appreciate the kind words.
for the first time to see Spro well elaborated Kudos Corey
Thank you!
Thank you Sir! All your Videos are informative and helpful
You are welcome.
This is excellent, thank you, very clear and concise, one of the best I've seen 🙂
You are welcome.
WOOW i don't know what to say u cleared my mind i definitely got the idea of SP thank you so much Tim i recommend ur channel to friends i liked it
Happy to help.
Great Information. More than expected. Thank you
You are welcome.
Hi Tim,
Thank you for great job, this video was very helpful.
You are welcome!
I'm a DBA and it's good to hear a DEV perspective advocating SPs. :-)
ORM tools like entity framework seem to be quite fashionable with developers. I get the appeal but you give up a lot by not using stored procs - explained very nicely in this video.
I'm glad you approve.
Always a delight and informative videos Tim :)
Great!
Very good explanation Mr. Tim, and thank you so much for that.
You are welcome.
Very informative as always. Thank you sir!
You are welcome.
Very informative and useful tutorials Tim...
Thank you for sharing and your support!
the most underrated channel on earth! thanks man.
I appreciate the kind words.
@@IAmTimCorey you can't imagine how much you helped in my career, hope your life gets happier and easier like what you doing to ours ♡
Thanks you. Simple, effective and efficient.
You are welcome.
Tim ! you 're really the best one to explain this.
go on
Thanks!
Very much helpful videos, thanks.
Thanks for watching and supporting Tim.
You the best Tim , so much love.
Thanks!
Thanks again bro for another informative video. God Bless and Keep Safe.🤘
You are welcome.
Great video Tim, I’ve never quite understood that “business logic in the UI only” mantra. Business logic should be wherever it’s most appropriate.
Thanks!
Wow!! Thanks a lot, this lecture was really very informative.
Glad it was helpful!
thanks a bunch man you've really helped me out
You are welcome.
Super helpful, thanks. Subscribed!
Great!
Thanks for your teaching
It's my pleasure
By far the best sp tutorial i have seen thank you & 1 think i want to mention is we can use visual studio database project template if we want to take an advantage of source control for our database streamline
I have a video on that very topic. Thanks for pointing it out.
Love your videos. I am very new at creating web apps with MVC in C# and I totally agree with you that EF is not the way to go when working with a SQL Server database. I have over 25 years of experience developing SQL server databases and stored procedures are the way to go for all the reasons that you mentioned. Now I need to learn how to used SP with a WebApp.
Thanks for sharing!
Thank you so much for the content. I found myself having to implement the same tables and SPs to another DB version 2016; mine was 2017. I tried import/export, and the backup feature unsuccessfully. I did not know I could back up my 2017 DB to a 2016 version. Given the scope of my task, I just recreated the DB and SPs on the new version.
I did want to mention that I guess replication would not work, because I did not have direct connection from the source to the destination. Not sure if I am right or not.
Regardless, I am progressing at a great pace thanks to your videos. After watching the episode on how to ask for help, I was curious about what CodePen was and watched another video on it. I am so impressed and excited about it. I like to use it and learn more about it.
Glad you are enjoying it.
I am a beginner of SQL sp coding. Your video is super detailed and concise. Thank you so much for your help. This is exactly what I need now. Instead of linked server, I would try a new SQL authentication account and use as connection string credentials in our custom script. I will update here on the results.
Glad it helped!
Excellent video! Thank you very much! You helped me a lot! I will be watching more of your videos! Thank you again! :)
You are welcome!
Best teacher on RUclips
Thank you!
Thank you for the tutorial
You are welcome.
thank you so much!! finally a good SQL video
You're welcome!
Thank you sir. It is an awesome topic and taught by awesome teacher like you
You are welcome.
that s crazy awesome. Thank you tons of times. God bless you
You are so welcome
Hi Tim, thanks for sharing your knowledge with your community. Even as an experienced developer you can always find something new while watching your videos, which is really great.
About when to use SP: in my opinion it's a question of the environment and how big the database and project is. For rich clients - yes, SP can be a very good option. For client/server - not really, at least not for everything, because the security aspect is obsolete in this case.
Also, the bigger your DB and project is, the faster you'll get lost. Imagine you have a DB with hundreds of tables and many more different queries. You don't want to use SP for all of them. But there can be a point where SP makes sense, when you have high complex queries and also you can always mix SP with plain TSQL in your project.
So it varies a lot on what you have and what you want to achive. It's not like saying do or don't do. It matters on your project, environment and the effort you're being able to put in.
The client/server scenario doesn't eliminate the need for security of the connection credentials, it just makes it less vulnerable. Security in depth is important. Yes, if someone gets access to your server, you have a problem but you can limit how much of a problem that will be. Also, getting file access to the server isn't the same thing as logging onto the server but both give you access to the connection string information.
As for getting lost in a big project, I don't see the solution there as putting queries in your code. That just means that you have more places to look to find the information you need and it makes it even harder to find. If good naming isn't enough to help you out and good documentation is also failing you, you can develop your server using SQL Server Developer Tools so that you can segment your stored procedures into folders and sub-folders so that you can better organize your objects. This would also work for tables and views. At the end of the day, if your database has enough objects in it to be confusing, spreading out that confusion will not make things better.
I do believe that what you do depends on the project, though. I just think there is a heavier weight on taking the secure, optimized route more often and only deviating when you see a significant reason to do so.
hey its amazing to learn from you Tim you are really one of the best teachers out here, i would like to ask you if you can upload a video on how to use stored procedures on the server side with .net 5/6 i used to work with EF before but i think SP really gives you more control and security like you said thanks!!
Please consider posting your recommendation on Tim's suggestion app (suggestions.iamtimcorey.com/). You may find other ideas there that you can vote up.
Great video. As far as source control you can always created a repository of your stored procedures in .sql or even text files.
Yeah, I've tried that but it always becomes a mess. Normally a rollback of a commit means you get the rolled back code but you can't really do that with SQL. Sure, you could put a drop statement in the create for a stored procedure, which would work (just run all scripts then) but that doesn't work for tables unless they are empty.
I was talking about just the SP code. I have pushed my department to store batch and script files into our local GIT repo. I'm even implementing Word and other procedure docs (even mainframe card members).
37:55 you are correct. Business logic belongs in the database.
Thanks for the explanation. Props to ya
You are welcome.
For SQL Server newbies like me that always used Windows authentication locally, when you create a new SQL Server User you also need to allow SQL Server Authentication in SSMS -> Select Server (right click) -> Server Properties -> Security -> Server authentication. For some reason mine was set to Windows mode only by default.
Thanks :D
Thanks for sharing.
You are the best!!! Thank you very much. Thumb up!
You are welcome.
Super beneficial video!
Excellent!
I learned something new. Thank you.
Thanks for trusting Tim.
Awesome. Thanks so much.
You are welcome.
This is the best in my opinion. Thanks
Thank you!
I didn''t know about the sp_ naming prefix. I gotta stop naming stored procedures that way. Thanks for the help sir. Very helpful for a beginner in stored procedures
Thanks for trusting Tim as you build your skills