Sir my question is that supposed I have created a stored procedure and within that I have written select * from employee and same thing I have done using sql function means I have created a table value function and wrote query select * from employee and return table then what is the difference between both approach means to say that both procedure and function is doing same thing then what is the difference between
Thanks for the video. You are a world treasure. If I'm only reading for report purposes (such as ssrs query), does it really matter if I get a dirty read? Asking in case there is something that I don't understand. Thx.
@@PinalDaveSQLAuthority I guess that's my reason for questioning. I think that I understand the concept of a dirty read, and the dangers in a process where you read our some data, do some work, then save back the output of the work. But I don't understand why I hear warnings about dirty reads in a read-only situation, or why we don't just treat all data as stateless as a design standard. Most financial systems want all longitudinal activity to be stored, so dirty-reads should not even matter. We are working with facts (i.e. at X timestamp, the value was Y. ) . so financial systems may not be a good example. (dont get me started on Write Once Read Many systems.) anyway, considering how much overhead goes in to protecting from dirty reads, I feel like the actual danger of harm from them is limited to a pretty small set of use cases. I would love to be convinced otherwise. Thanks again Pinal Dave. You are my hero, your work has saved me many times.
I'm trying to find a way to improve my api which is using an mssql database. Started looking for nolock and was wondering, would the guy I follow on twitter also be here? And yes! Like your work, thanks again!
Al Ball By only locking critical data. Example if you get an orderid, add 1 and then store it again. Only that process in transaction. If many people are creating orders at “the same time”, the transaction will only que this and not the whole process of creating orders, orderitems, address and all other stuff that is going on for creating an order. Like standing in line to recieve an egg. When you get the egg your “transaction” is over and you can start boiling it and the next one in line can get their egg. The next in line does not have to wait for their egg until you have finished boiling your egg.
Al Ball That was actually my point. A lot of data does not need to rollback (in my example the orderid. It just needs to be unique for every user). In many cases the data you add/create is only your data anyway. Big stored procedures (or simular) wrapped around transactions is not always the best practice.
Nolock is not the solution for performance. There are lots of other things one can do to improve the performance of the query. I have already done a few videos on it and will be creating a few more as well. I have entire section blogSQLAuthority.com which is talking about performance tuning only.
Scenario: If TCP and shared memory both protocols are enabled on local sql box and a user execute any query from same machine, which protocol will be used at that time?
@@PinalDaveSQLAuthority The previous developer did it, to avoid locks...but as you explain in your videos that is not a good approach. Now it is difficult to convince other developers that this is not a good practice.
I have a scenario...we read data from CDC tables where in there is a continuous inflow of data in CDC table. While doing a select on the CDC tables, if we do not use the nolock hint then we get a deadlock error. If we use the nolock hint then we have an issue with dirty reads. What is the best possible solution for this?
Hi Great Knowledge of SQL. I wanted to ask so when should we use NOLOCK hint as in our database there are constant LOCKS due lots of Table Read for the transaction and we use NOLOCK and now everything seems fine. If not NOLOCK what's your suggestion to address this issue. Thank You
@@PinalDaveSQLAuthority Thank You. We have use NOLOCK in reporting of our solution because if we do read and write in real time is going on if we generate the the report and made any transaction in our system. In short can we use NOLOCK in Stored Procedures which fetch data for reporting.
This is a good, concise explanation. The only comment I would make it is that for absolute clarity, why not make the update statements absolute values rather than adding 1? Update directly to 100 and 99 accordingly to make it super clear what is going on.
WITH(NOLOCK) will only make your query complete more quickly ('improve' performance) if it happens to be reading dirty records. In which case the query will return the original value as the edited value is yet to be committed. If WITH(NOLOCK) is not used then your query would need to wait until the edited record is committed before it can return the results.
Great example in a short time BUT what would be an alternative for NOLOCK? th-cam.com/video/sbBFHFxxE1Y/w-d-xo.html - at this time you said there's other ways to improve performance. Can you tell us the alternative?
Fantastic, short, simple and to the point. Loved the examples. A lot better than the text descriptions online
Thank you so much.
Simple and straight to the point! Thank you!
Thanks.
Thank you sir. You teach with real time scenarios. Really appreciate.
You are welcome
These tiny sessions are superb refreshers Pinal. Please carry on, you are the best.
Thanks so much!
Great explanation. There's a reason why there is not a single dislike. Great work!
Very kind of you
A simple short smart example
Thanks, Sir
So nice of you
Thanks. Now I fully understand the use of NOLOCK
Thanks so much!
Thanks Pinal. Today I know drawback of nolock in select statement in SQL.
Great to hear that.
You are always helping us very great sir👍
Thanks so much
Tks, really straight to the point
Thanks
Thanks Pinal Dave. Very informative.
Thank you so much
I use nolock only when I need to find approximate count, based on a condition, on a very large busy table.
Good Idea
Sir my question is that supposed I have created a stored procedure and within that I have written select * from employee and same thing I have done using sql function means I have created a table value function and wrote query select * from employee and return table then what is the difference between both approach means to say that both procedure and function is doing same thing then what is the difference between
Function can be used in SELECT is the only difference here.
Finally a digestible explanation for a junior dev🎉🎉🎉
Thanks
Thanks for the video. You are a world treasure.
If I'm only reading for report purposes (such as ssrs query), does it really matter if I get a dirty read? Asking in case there is something that I don't understand. Thx.
You do not want dirty read in financial systems.
@@PinalDaveSQLAuthority I guess that's my reason for questioning.
I think that I understand the concept of a dirty read, and the dangers in a process where you read our some data, do some work, then save back the output of the work.
But I don't understand why I hear warnings about dirty reads in a read-only situation, or why we don't just treat all data as stateless as a design standard.
Most financial systems want all longitudinal activity to be stored, so dirty-reads should not even matter. We are working with facts (i.e. at X timestamp, the value was Y. ) . so financial systems may not be a good example. (dont get me started on Write Once Read Many systems.)
anyway, considering how much overhead goes in to protecting from dirty reads, I feel like the actual danger of harm from them is limited to a pretty small set of use cases. I would love to be convinced otherwise.
Thanks again Pinal Dave. You are my hero, your work has saved me many times.
I'm trying to find a way to improve my api which is using an mssql database. Started looking for nolock and was wondering, would the guy I follow on twitter also be here? And yes! Like your work, thanks again!
Yes, absolutely. Thank you!
The key is how to effectively read tables that are constantly updated avoiding locking (i.e. account balance).
Totally agree
Like Mr.Pinal Dave, I totally agree! But how it is done?
Something about isolation levels ?
Al Ball By only locking critical data. Example if you get an orderid, add 1 and then store it again. Only that process in transaction. If many people are creating orders at “the same time”, the transaction will only que this and not the whole process of creating orders, orderitems, address and all other stuff that is going on for creating an order. Like standing in line to recieve an egg. When you get the egg your “transaction” is over and you can start boiling it and the next one in line can get their egg. The next in line does not have to wait for their egg until you have finished boiling your egg.
Al Ball That was actually my point. A lot of data does not need to rollback (in my example the orderid. It just needs to be unique for every user). In many cases the data you add/create is only your data anyway. Big stored procedures (or simular) wrapped around transactions is not always the best practice.
Sir, So what we use to avoid query wait time? Where more than 100 users are logged in.
Many different techniques. You may find them under performance tuning tag at sqlauthority.com
What can be a good replacement for nolock then, for faster query retrieval and maintain non dirty read.
Nolock is not the solution for performance. There are lots of other things one can do to improve the performance of the query. I have already done a few videos on it and will be creating a few more as well. I have entire section blogSQLAuthority.com which is talking about performance tuning only.
@@PinalDaveSQLAuthority ok thanks :)
very well explained sir.
Thanks and welcome
Hi sir please make video on tablock use in insert data
Sure. Great idea.
Sir what is different between a select query statement on table and select query in sql function in respect to response
Sorry I do not understand the question.
fantastic explanation
Thanks
Scenario:
If TCP and shared memory both protocols are enabled on local sql box and a user execute any query from same machine, which protocol will be used at that time?
It is based on how you connect with SSMS
Lets suppose application and sql server both are on same box.
Based on how you decide to connect.
what if almost in the entire system queries were built with nolock? how is the approach to change this?
Wow, I think you should start removing the locks where they are not essentials.
@@PinalDaveSQLAuthority The previous developer did it, to avoid locks...but as you explain in your videos that is not a good approach. Now it is difficult to convince other developers that this is not a good practice.
I have a scenario...we read data from CDC tables where in there is a continuous inflow of data in CDC table. While doing a select on the CDC tables, if we do not use the nolock hint then we get a deadlock error. If we use the nolock hint then we have an issue with dirty reads. What is the best possible solution for this?
There are few things you should consider... indexes, faster drives or may be the code has to be looked which is creating the deadlock.
@@PinalDaveSQLAuthority Thanks for your reply and for this wonderful initiative. Lots of these videos are very helpful.
Hi
Great Knowledge of SQL. I wanted to ask so when should we use NOLOCK hint as in our database there are constant LOCKS due lots of Table Read for the transaction and we use NOLOCK and now everything seems fine. If not NOLOCK what's your suggestion to address this issue. Thank You
If you have no issue with database integrity you can use nolock.
@@PinalDaveSQLAuthority Thank You. We have use NOLOCK in reporting of our solution because if we do read and write in real time is going on if we generate the the report and made any transaction in our system.
In short can we use NOLOCK in Stored Procedures which fetch data for reporting.
thanks, useful information.
Glad you liked it.
Thank you! Is the solution for this problem is RCSI?
Yes but RCSI introduce another problem, which is a long discussion.
Good one! How to convince people who asked to use 'NoLock' always even when they are aware of dirty read concept... Any suggestions 🤔
The system will eventually convience them...i often see organization doing projects in removing nolock hints.
@@PinalDaveSQLAuthority Thanks for the response. 🙂
This is a good, concise explanation. The only comment I would make it is that for absolute clarity, why not make the update statements absolute values rather than adding 1? Update directly to 100 and 99 accordingly to make it super clear what is going on.
That is also a good point. Only reason I used this one is to point often people say that we are not using external values only from table itself.
@@PinalDaveSQLAuthority Ah makes sense
Thank you sir
You are welcome
Thank you
Welcome!
If I use with (nolock) while selecting data, this will increase query performance?
Not necessarily.
WITH(NOLOCK) will only make your query complete more quickly ('improve' performance) if it happens to be reading dirty records. In which case the query will return the original value as the edited value is yet to be committed. If WITH(NOLOCK) is not used then your query would need to wait until the edited record is committed before it can return the results.
@@gregg1571 food point
Great example in a short time BUT what would be an alternative for NOLOCK?
th-cam.com/video/sbBFHFxxE1Y/w-d-xo.html - at this time you said there's other ways to improve performance. Can you tell us the alternative?
Sure in the future.
Thanks.
You're welcome
as per my architect use nolock with every select statement 🤔
While, I do not prefer that, it is fine if your system is not affected by dirty data.
Lessen is: don´t use WAITFOR DELAY in your transactions... 😉
You can say that as well.
Nice Video
Thanks
Best
Thanks buddy!
3:35 !=
That is totally correct.
And you won't improve the performance of the video with a nolock hint. It will still take 3:35.
@@xShibboleth totally agree
NO to NOLOCK :)
Well said