Deterministic functions

Question

Deterministic functions

Steve Jones - SSC Editor

SSC Guru

Points: 736255
More actions
November 17, 2018 at 11:30 am

#416499

Comments posted to this topic are about the item Deterministic functions

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

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

Stewart "Arturius" Campbell SSC Guru Points: 72626 More actions · Answer 1

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”

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 2

Steve Jones - SSC Editor

SSC Guru

Points: 736255

November 19, 2018 at 8:10 am

#2013962

Thanks

chgn01 SSCarpal Tunnel Points: 4357 More actions · Answer 3

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

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 4

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.

t.ovod-everett Default port Points: 1487 More actions · Answer 5

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).

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 6

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.

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 7

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

Shayn Thomas SSCertifiable Points: 5863 More actions · Answer 8

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

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