I am trying to add with (nolock) in a report query that when run locks the full db making imposssible for other users to use the db. I can't figure out how to use in this case: <pre class="prettyprint"><code>-- this is just an example: SELECT FIELDS FROM (SELECT * FROM ATABLE) AS SUB </code></pre> This gives syntax error: <pre class="prettyprint"><code>SELECT FIELDS FROM (SELECT * FROM ATABLE) WITH (NOLOCK) AS SUB </code></pre> where <code>WITH (NOLOCK)</code> shuold be put? I am not saying this is a solution to all problems, it is just a test i want to. Thanks!

If there are more tables involved and more than one query invloved and you don't care about dirty reads then set the Isolation level of your transaction to <code>read uncommited</code> instead of writing <code>Nolock</code> everywhere <pre class="prettyprint"><code>SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED </code></pre>

I would put it here but the thing to note is you are using a view so really it should go on the tables in the view: <pre class="prettyprint"><code>SELECT FIELDS FROM (SELECT * FROM MYVIEW WITH (NOLOCK)) AS SUB </code></pre>

WTH (NOLOCK) syntax fo subquery

Q: What command do you use for a subquery with insert?

Subqueries with the INSERT StatementThe INSERT statement uses the data returned from the subquery to insert into another table. The selected data in the subquery can be modified with any of the character, date or number functions.

Q: What is use of with Nolock in SQL?

The WITH (NOLOCK) table hint is used to override the default transaction isolation level of the table or the tables within the view in a specific query, by allowing the user to retrieve the data without being affected by the locks, on the requested data, due to another process that is changing it.

Q: Can I use in operator with subquery?

Can I use in operator with subquery? A subquery can be used with the IN operator as "expression IN (subquery)". The subquery should return a single column with one or more rows to form a list of values to be used by the IN operation.

Tags:

sql-server

nolock

sql-server-2008-r2

I am trying to add with (nolock) in a report query that when run locks the full db making imposssible for other users to use the db.

I can't figure out how to use in this case:

-- this is just an example:
SELECT FIELDS FROM (SELECT * FROM ATABLE) AS SUB

This gives syntax error:

SELECT FIELDS FROM (SELECT * FROM ATABLE) WITH (NOLOCK) AS SUB

where WITH (NOLOCK) shuold be put?

I am not saying this is a solution to all problems, it is just a test i want to.

Thanks!

526

asked Jun 16 '17 15:06

LaBracca

2 Answers

If there are more tables involved and more than one query invloved and you don't care about dirty reads then set the Isolation level of your transaction to read uncommited instead of writing Nolock everywhere

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

126

answered Oct 03 '22 07:10

Pரதீப்

I would put it here but the thing to note is you are using a view so really it should go on the tables in the view:

SELECT FIELDS FROM (SELECT * FROM MYVIEW WITH (NOLOCK)) AS SUB

answered Oct 03 '22 08:10

Jesse

Related questions
                            
                                How to make a SQL Server agent job step failure on purpose
                            
                                get both output parameter and ResultSet values from SQL Server stored procedure
                            
                                SQL Server 'Invalid column name' in transaction
                            
                                T-SQL - Filtered dates between a range
                            
                                Concerning Deprecated features (More than two-part column name)
                            
                                FIND_IN_SET() equivalent in SQL Server
                            
                                msSql hex to base64
                            
                                Remove last comma from dynamic sql
                            
                                Chinese collation for MS SQL
                            
                                Does SSRS Report Builder always default date to MM/dd/yyyy?
                            
                                Using PouchDB with SQL Server and CouchDB
                            
                                PATINDEX with letter range exclude diacritics (accented characters)
                            
                                How to get the all table(s) name which are used in particular stored procedure?
                            
                                Error Inserting Data into tables in SQL Server
                            
                                EF6 - not doing what I expected for .Any()
                            
                                Sql Server Session Context Limit
                            
                                double inner join in linq/ lambda query?
                            
                                SQL Server add index without dropping table
                            
                                How to query Code First entities based on rowversion/timestamp value?
                            
                                SQL - CTE vs VIEW

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!

Donate Us With