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!
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.
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
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.
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.
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 😆
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
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
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!
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.
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.
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 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.
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 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...
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!
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
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!
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!
Company I'm working for now uses C# and SQL Server. The reporting team wanted access to use sql to make reports and such because they are pretty tech savvy too. So we just created a "Reporting User" and gave it "db_datareader" only. They can query what they want. No sensitive information... its a database of results returned from robots. There are only 4 of us that can actually modify the database in any way. As far as naming convention I still use "sp_" if I'm creating it in master for a common use across our different databases (more things like backup, turn replication on for a database, etc.) Rule of thumb any changes that can be damaging we run the stored procedure to back up the DB before making changes. Yes, databases are still backed up every night though. If I screw something up like an update without a where clause (i've did this before) I can copy that bak. to my local machine get original values to update the production database back to how it was at the moment I backed it up. Worst case we can restore the backup directly in SQL server but with replication this means pretty much bringing that database down for about 1/2 hour or so and resyncing. Fun fun :)
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
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.
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.
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 !
I had many deliberations on sp. Many dissuaded me from using it. I was convinced that sp provides security. But your video provides a very structured reasoning
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!!
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.
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 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.
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 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
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.
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. 💪
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.
One easy way to add SQL objects (definitions for tables, stored procedures, etc.) to a source code control system is to create a script that exports your SQL object definitions to a directory that is under version control. Every time you make changes to your SQL objects you run the script before you make a commit.
The problem with that is that you either cannot include table update scripts in the directory or you can't run the scripts at every commit. I've not found a good way to do it without having a messy implementation. Usually a process like this lasts for only a few changes before it breaks down.
I totally agree with you, we can use visual studio data tools project to use compare changes and have it in source control. Similar to what Red gate does.
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 completely agree with your position on the 'no business logic in the database' edict. In fact, I would argue that a relational database IS an implementation of business logic in itself, since each table represents a logical data type. Entities such as 'customer', 'product' and 'customer-order' are an integral part of most business models!
Exactly, “business logic” encompasses the whole application, both the database and the UI, then each layer performs the role best suited to it to govern that logic
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.
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).
I do like the idea that business logic, i.e. data validation is built-in to each dB, as it might change from one dB to another based on business needs..
Having some data validation on the database can be a good option, since you don't always control who calls your stored procedures. Just don't go crazy with it and bog down your server.
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?
you can manually add all your procedure scripts to source control. No extra cost except that the source control commands must be done from outside of management studio.
I think the same, Stored procedures are faster than EF in my personal opinion, but I haven't found a valid post that tells me the same. Anyways.,I enjoyed this video very much, thank you Tim!
42:28 As far as I know you can execute custom sql since ef 5 or so. I believe the call was context.Database.SQLQuery where you insert your sql query as a string for example
Hi Tim, im a fresh IT graduate and started learning C# language. I loved watching your videos because you just dont show how to do it but also you explain how it works. By the way in terms of security, do you have a videos where you show how to validate user inputs from client side just just before accessing to a database. Thanks.
Not specifically, but that sounds like a good suggestion. Please add it to the list on the suggestion site so others can vote on it as well: suggestions.iamtimcorey.com/
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.
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.
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.
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
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.
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 😮
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!
Thanks for the lesson. I enjoy that you teach more of the WHY and not just the HOW.
You are welcome.
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.
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!
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
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.
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.
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!
This is by far the best video i have ever seen on this topic, Thank you Tim
You are most welcome. Thanks for watching.
Great channel, excellent mentor, coach, and trainer without a ludicrous price tag. Thank you, Thank you, and Thank you.
You are most welcome.
The way you explain things is amazing.
Thank you!
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.
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.
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!
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!
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.
Company I'm working for now uses C# and SQL Server. The reporting team wanted access to use sql to make reports and such because they are pretty tech savvy too. So we just created a "Reporting User" and gave it "db_datareader" only. They can query what they want. No sensitive information... its a database of results returned from robots. There are only 4 of us that can actually modify the database in any way. As far as naming convention I still use "sp_" if I'm creating it in master for a common use across our different databases (more things like backup, turn replication on for a database, etc.) Rule of thumb any changes that can be damaging we run the stored procedure to back up the DB before making changes. Yes, databases are still backed up every night though. If I screw something up like an update without a where clause (i've did this before) I can copy that bak. to my local machine get original values to update the production database back to how it was at the moment I backed it up. Worst case we can restore the backup directly in SQL server but with replication this means pretty much bringing that database down for about 1/2 hour or so and resyncing. Fun fun :)
Best teacher on RUclips
Thank you!
You are doing the lords work sir. Thank you, I needed this for a project at work.
Thanks!
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!
Very clearly and professionally explained. Thank you so much Tim.
You are welcome.
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!
Been using them for 20 years...they are they best...
Great!
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.
Hi Tim! You explain things and make it so simple, thank you !
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.
I had many deliberations on sp. Many dissuaded me from using it. I was convinced that sp provides security. But your video provides a very structured reasoning
Great!
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.
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.
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!
First day at work. So glad I found you!
I hope this channel is a lot of help for you.
seriously one of the best videos I ever watched with a great and detailed explanation.
Glad it was helpful!
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.
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/
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.
Great explanation of SP in such a simple way. Thanks Tim
Glad it was helpful!
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!
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 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
Some of the best training videos. A great starting place.
Thanks!
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 ♡
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.
this class on stored procedure basics was very helpful. thank you!
Glad it was helpful!
for the first time to see Spro well elaborated Kudos Corey
Thank you!
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/
This is excellent, thank you, very clear and concise, one of the best I've seen 🙂
You are welcome.
Tim ! you 're really the best one to explain this.
go on
Thanks!
Omg, after watching your video, SQL Stored Procedure is on my mind. Thank you.
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.
Thank you so much for your brief explanation.
You are welcome.
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.
Excellent tutorial as always.
Thank you for taking the time putting it together.
Regards
You are welcome.
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 Sir! All your Videos are informative and helpful
You are welcome.
Very good explanation Mr. Tim, and thank you so much for that.
You are welcome.
Thank you so much for this tutorial. Concepts are so much clearer.
You're very welcome!
One easy way to add SQL objects (definitions for tables, stored procedures, etc.) to a source code control system is to create a script that exports your SQL object definitions to a directory that is under version control. Every time you make changes to your SQL objects you run the script before you make a commit.
The problem with that is that you either cannot include table update scripts in the directory or you can't run the scripts at every commit. I've not found a good way to do it without having a messy implementation. Usually a process like this lasts for only a few changes before it breaks down.
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.
I totally agree with you, we can use visual studio data tools project to use compare changes and have it in source control. Similar to what Red gate does.
Yep.
37:55 you are correct. Business logic belongs in the database.
Hi Tim I just want to tell you're great developer really Thank you
I appreciate the kind words.
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.
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.
I completely agree with your position on the 'no business logic in the database' edict. In fact, I would argue that a relational database IS an implementation of business logic in itself, since each table represents a logical data type. Entities such as 'customer', 'product' and 'customer-order' are an integral part of most business models!
Thanks for sharing your perspective.
Exactly, “business logic” encompasses the whole application, both the database and the UI, then each layer performs the role best suited to it to govern that logic
You the best Tim , so much love.
Thanks!
Very informative and useful tutorials Tim...
Thank you for sharing and your support!
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 Corey, i love you man.
Thanks!
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).
Thank you Tim.. I aslo would like to use ADO instead Entity framework like you.
You are welcome.
I do like the idea that business logic, i.e. data validation is built-in to each dB, as it might change from one dB to another based on business needs..
Having some data validation on the database can be a good option, since you don't always control who calls your stored procedures. Just don't go crazy with it and bog down your server.
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.
Thanks again bro for another informative video. God Bless and Keep Safe.🤘
You are welcome.
Your videos/tutorials are very useful. Thank you for your work!
You are welcome.
you can manually add all your procedure scripts to source control. No extra cost except that the source control commands must be done from outside of management studio.
This should make life easier for you: ruclips.net/video/ijDcHGxyqE4/видео.html
I think the same, Stored procedures are faster than EF in my personal opinion, but I haven't found a valid post that tells me the same. Anyways.,I enjoyed this video very much, thank you Tim!
Hopefully someone can address this based on some testing, one way or the other.
Great tutorial, please make video on web chat app. Your explanation is excellent.
I will add it to the list. Thanks for the suggestion.
42:28 As far as I know you can execute custom sql since ef 5 or so. I believe the call was context.Database.SQLQuery where you insert your sql query as a string for example
This great video needs a sequel (pun intended) for advanced use of Stored Procedures.
Thank you. I have added this to Tim's list of possible future topics.
Hi Tim, im a fresh IT graduate and started learning C# language. I loved watching your videos because you just dont show how to do it but also you explain how it works. By the way in terms of security, do you have a videos where you show how to validate user inputs from client side just just before accessing to a database. Thanks.
Not specifically, but that sounds like a good suggestion. Please add it to the list on the suggestion site so others can vote on it as well: suggestions.iamtimcorey.com/
I will. Thanks.
Very informative and useful. I appreciate your candor about EF at the end.
Thanks!
Thanks for your teaching
It's my pleasure
thanks a bunch man you've really helped me out
You are welcome.
This was very informative Tim. Thank you so much.
You are welcome.
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.
Very much helpful videos, thanks.
Thanks for watching and supporting Tim.
This is a great video. Excellent work as usual Tim Corey.
Thank you!
Wow!! Thanks a lot, this lecture was really very informative.
Glad it was helpful!
May You Live Long So people Like us dont need to worry
HATS OFF RESPECTED SIR
I appreciate the kind words.
Thank you sir. It is an awesome topic and taught by awesome teacher like you
You are welcome.
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!
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.
that s crazy awesome. Thank you tons of times. God bless you
You are so welcome
I learned something new. Thank you.
Thanks for trusting Tim.
Hi Tim,
Thank you for great job, this video was very helpful.
You are welcome!