Menu
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
A subquery can be placed in a number of SQL clauses like WHERE clause, FROM clause, HAVING clause. You can use Subquery with SELECT, UPDATE, INSERT, DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN, etc. A subquery is a query within another query.
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.
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.
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.
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
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
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
