Option to add WITH (NOLOCK)
I would like SQL Prompt to be able to insert WITH (NOLOCK) when writing SELECT and JOIN statements. When working with production data, the risk of forgetting to write this chunk and locking data rows is very high.
Thanks for all the feedback here.
After review, the team have decided to decline this specific suggestion. But, we’ll take forward Kendra’s code analysis suggestion in the comments below.
I agree with those suggesting that the best approach for those where dirty reads are a requirement (notable use case being ad-hoc queries against production where it is accepted that the results may be transactionally inconsistent/incorrect) is to use "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED", which is easy to snippetize and means that you don't need to worry about missing a hint.
It's not only more reliable but I agree with the other notes in Paul White's comment -- longer term having NOLOCK sprinkled in code makes it much harder to transition to better ways to manage locking, as those hints will need to be removed.
I would much rather have Redgate engineer time be spent adding static code analysis that picks up on NOLOCK hints and explains that it allows completely incorrect, bonkers results to be returned to the user, as many people are not aware of that. (And how would they know if they are new? "NOLOCK" sounds like magic!)
Grant Fritchey commented
I'm really not in favor of this as a default behavior. However, it would be really easy to just add a snippet that does the work. That adds a couple of key strokes rather than making it a part of the code, but then, when you're dealing with places where NOLOCK is ignored (UPDATE, INSERT, DELETE, depending on the query), you don't have to deal with it because you control when and how it gets added.
I know it has been awhile since there have been any posts to this suggestion, but wanted to chime in anyway. I'd love to see this suggestion completely removed, but doubt that will happen. We're not the only experts (I certainly am not, and would even be hard-pressed to put myself in that category), but as has been pointed out several times in this thread, if you are going to have an option to have SQL Prompt add WITH(NOLOCK) to EVERY table in a query, then why not just use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED at the start. You've accomplished the exact same thing without having to sprinkle WITH(NOLOCK) throughout your code.
Per BOL: This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction.
It is sooo much easier to add SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED at the start of a script/proc than making sure you've added WITH(NOLOCK) on every table, especially if you've to 2K lines (or more) of code.
Hugo Sanguino commented
Guys!, guys, please, stop thinking that you are the only experts on SQL and respect the requirements that many of us have, this option could be turned off by default and only those who use it could activate it, please leave the egolatry aside and allow We all count on with a better product that fits our needs. The WITH(NOLOCK) option is a must for many of us, it is not horrible, it is not a bad idea, it is what many of us need.
Steve rook commented
This is a great idea. Give us the option to select a default set of table hints and then apply them when you format the sql.
The Diamond Z commented
This is a bad idea for application coding but for working with data in a running Production environment (i.e. support/debugging) it's often times preferable to not lock a table at the expense of dirty data.
I'm not voting for it, but I think it would be a reasonable feature if you could limit it by environment (Server/Db) and better yet limit it to blocks that are read only.
Clayton Hoyt commented
2 words....bad idea :)
This is a NO-NO. Red Gate people - DON'T EVER DO THIS. Please! NOLOCK will be removed from SQL Server anyway, so that would be time wasted on your part. Big time. Zack, if you need to read dirty data (bad idea all throughout), then change the isolation level do READ UNCOMMITTED or---even better---to one of the SNAPSHOT isolation levels.
Grant Fritchey commented
No offense to anyone working at a place that requires this, but I think it's a horrible idea. Much better to spend time on almost anything else rather than supporting a bad practice and making it easier.
I'm sorry to say that but... it is a very bad, bad, bad idea to have this hint attached to each and every query. Borders on insanity and complete lack of knowledge about how queries, optimizer and isolation work. This should never be encouraged. If you have a need for this, then you should use custom-made snippets as already suggested by someone. Don't let such bad practices spread via SQL Prompt!
Paul White commented
If you're adding WITH (NOLOCK) to everything, you might be much better off writing SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED once, or getting the data access layer to set it at connection time.
Sprinkling NOLOCK hints everywhere makes for a tedious transition to a row-versioning isolation level later. It's also rather error-prone (the risk of forgetting you mention).
David Davis commented
Some shops require WITH(NOLOCK) like the shop I am at. No arguments, even with articles on differences of why or why not to use it. Its their standards and we have to. So this would be invaluable. Mind you, the snippets that @Matt recommends, does work, but it would be nice to turn on an option to catch all of the variation of this automatically. Joins, inner and outer...etc..
You could do as I have done and add a new snipped called wnl - then your typing is just wnl<tab>
Is it that you're 1) on SQL 2000 or 2) on SQL 2005 and above and haven't turned on READ_COMMITTED_SNAPSHOT isolation, or 3) some other reason?
Having inherited a SQL 2005 set of DBs that had WITH (NOLOCK) everywhere and another SQL 2000 set (that we upgraded to 2008) we had the problem of dirty reads and non-repeatable aggregations against live data being a perennial risk. We acquire millions of new rows per day, all day, into the primary DB so data volumes are an issue. Turning READ_COMMITTED_SNAPSHOT isolation on and removing every occurrence of WITH (NOLOCK) has eradicated lock contention and impure data with no ongoing performance degradation - you may encounter initial slowing while row-versioning is set up but once done, queries fly along.
Just wondering why you'd need the option...
Does this help explain: http://msdn.microsoft.com/en-us/library/ms345124(v=sql.90).aspx (in case it's an unknown technology)?
This is an awesome idea! It would save me a lot of typing.