Placement of WITH(NOLOCK) in nested queries
sql, sql-server, sql-server-2008, t-sql
Solution
You should not put `NOLOCK` anywhere in that query. If you are trying to prevent readers from blocking writers, a much better alternative is `READ COMMITTED SNAPSHOT`. Of course, you should read about this, just like you should read about `NOLOCK` before blindly throwing it into your queries:
- Is the `NOLOCK` SQL Server hint bad practice?
- Is `NOLOCK` always bad?
- What risks are there if we enable read committed snapshot in SQL Server?
Also, since you're using SQL Server 2008, you should probably replace your `VBSplit()` function with a table-valued parameter - this will be much more efficient than splitting up a string, even if the function is baked in CLR as implied.
First, create a table type that can hold appropriate strings. I'm going to assume the list is guaranteed to be unique and no individual mnemonic word can be > 900 characters.
CREATE TYPE dbo.Strings AS TABLE(Word NVARCHAR(900) PRIMARY KEY);
Now, you can create a procedure that takes a parameter of this type, and which sets the isolation level of your choosing in one location:
CREATE PROCEDURE dbo.Whatever
@Strings dbo.Strings READONLY
AS
BEGIN
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL --<choose wisely>;
SELECT -- please list your columns here instead of *
FROM @Strings AS s
INNER JOIN dbo.dct -- please always use proper schema prefix
ON dct.concept = s.Word
...
END
GO
Now you can simply pass a collection (such as a DataTable) in from your app, be it C# or whatever, and not have to assemble or deconstruct a messy comma-separated list at all.
Problem
In the Following query where would I place `WITH(NOLOCK)`? ``` SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM dbo.VBsplit(@mnemonicList, ',')) a) b JOIN dct ON dct.concept = b.concept WHERE b.geo = dct.geo) c JOIN dct_rel z ON c.db_int = z.db_int) d JOIN rel_d y ON y.rel_id = d.rel_id WHERE y.update_status = 0 GROUP BY y.rel_id, d.concept, d.geo_rfa ```