Post reply

UDF Isolation

  • February 28, 2012 at 2:28 am

    #251447

    Hi All,

    I know that you cannot specify the isolation level in the top of a UDF but what if you call the function with the NOLOCK hint? Does it have the same effect?

    Thanks in advance

    SQL DBA
    Every day is a school day, and don't trust anyone who tells you any different.
    http://sqlblogness.blogspot.co.uk

  • February 28, 2012 at 3:27 am

    #1452468

    Locking hints cannot be specified against TVFs:

    SELECT *

    FROM dbo.someFunction() AS F

    OPTION (TABLE HINT(F, NOLOCK))

    Msg 8724, Level 16, State 1, Line 2

    Cannot execute query. Table-valued or OPENROWSET function 'F' cannot be specified in the TABLE HINT clause.

    NOLOCK, however, is not a good idea, unless you want inconsistent data.

    Moreover, it won't make the query run faster. If you're experiencing long lasting locks, tune the queries in the first place.

    -- Gianluca Sartori

  • February 28, 2012 at 3:56 am

    #1452479

    Thanks - It was for a search form that did not worry about dirty data

    SQL DBA
    Every day is a school day, and don't trust anyone who tells you any different.
    http://sqlblogness.blogspot.co.uk

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply