Post reply

Deterministic functions

  • November 17, 2018 at 11:30 am

    #416499

    Comments posted to this topic are about the item Deterministic functions

  • November 18, 2018 at 10:05 pm

    #2013913

    Nice question to start the week on, thanks Steve

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • November 19, 2018 at 8:10 am

    #2013962

    Thanks

  • November 19, 2018 at 9:02 am

    #2013971

    PARSENAME system functions is deterministic per http://www.informit.com/articles/article.aspx?p=21329&seqNum=6 link

    --------------------------------------
    ;-)“Everything has beauty, but not everyone sees it.” ― Confucius

  • November 19, 2018 at 9:38 am

    #2013983

    It's not listed in BOL. Perhaps because there could be some issues with AGs/clustering and having the database move between nodes. In that case, the same database you connected to might return different values.

  • November 19, 2018 at 3:30 pm

    #2014042

    Steve Jones - SSC Editor - Monday, November 19, 2018 9:38 AM

    It's not listed in BOL. Perhaps because there could be some issues with AGs/clustering and having the database move between nodes. In that case, the same database you connected to might return different values.

    I found this interesting Stack Overflow thread: https://stackoverflow.com/questions/571065/why-does-sql-2005-say-this-udf-is-non-deterministic .  Looks like the SQL Server 2005 docs said it was deterministic, but the docs were incorrect (at least as of SQL 2005).  Not quite sure how moving the database around could result in different values, since PARSENAME doesn't infer missing pieces (it just returns NULL).

  • November 19, 2018 at 4:24 pm

    #2014046

    I wonder if you could put in the virtual server.db.schema.object and this return different values in an AG. Haven't tested, but that was all I could think of for the function.

  • November 20, 2018 at 10:16 am

    #2014117

    Steve Jones - SSC Editor - Monday, November 19, 2018 4:24 PM

    I wonder if you could put in the virtual server.db.schema.object and this return different values in an AG.

    Hi Steve. I highly doubt that PARSENAME would be affected by AG since it is only supposed to split a string of no more than 3 periods. It doesn't really matter what the string is comprised of, as long as the resulting piece is no more than 128 characters. It really seems like this function should be deterministic, but the following test proves that it is, in fact, not:

    -- Control since we know that NEWID() is non-deterministic:
    CREATE TABLE #Deterministic (Col1 INT, Col2 AS (NEWID()) PERSISTED);
    /*
    Msg 4936, Level 16, State 1, Line XXXXX
    Computed column 'Col2' in table '#Deterministic' cannot be persisted because the column is non-deterministic.
    */

    -- Just to be sure:
    CREATE TABLE #Deterministic (Col1 INT, Col2 AS (FORMAT(1212.45454, N'#.##')) PERSISTED);
    /*
    Msg 4936, Level 16, State 1, Line XXXXX
    Computed column 'Col2' in table '#Deterministic' cannot be persisted because the column is non-deterministic.
    */

    -- Survey says:
    CREATE TABLE #Deterministic (Col1 INT, Col2 AS (PARSENAME(N'a.b.c.d', 1)) PERSISTED);
    /*
    Msg 4936, Level 16, State 1, Line XXXXX
    Computed column 'Col2' in table '#Deterministic' cannot be persisted because the column is non-deterministic.
    */

    -- And just to make sure that this actually does work under the right conditions:
    CREATE TABLE #Deterministic (Col1 INT, Col2 AS (QUOTENAME(N'a.b.c.d')) PERSISTED);
    -- Success!

    Now, I have no idea why this isn't deterministic. There must be a dependency on the environment somehow (session setting, etc), but it doesn't behave differently by setting QUOTED_IDENTIFIERS on vs off, so it must be something else:

    SET QUOTED_IDENTIFIER OFF;  -- ON

    SELECT PARSENAME(N'"a]""2"..{c}.d', 4);   -- a]"2
    SELECT PARSENAME(N'"a.2".]"3].{c}.d', 3); -- b]"3
    SELECT PARSENAME(N'"a.2"..{c}.d', 2); -- {c}
    SELECT PARSENAME(N'"a.2"..{c}.''d''', 1); -- 'd'

    Take  care, Solomon...

    SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
    Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
    Info sites — Collations     •     Module Signing     •     SQLCLR

  • November 21, 2018 at 5:59 am

    #2014199

    I too incorrectly went with PARSENAME, ah well, better luck next time 🙂

    ---------------------------------------------------------------------------------------
    The more you know, the more you know that you dont know

Viewing 9 posts - 1 through 8 (of 8 total)

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