Deterministic

Question

Post reply

Deterministic

jwbart06

Hall of Fame

Points: 3291
More actions
March 21, 2013 at 10:05 pm

#270356

Comments posted to this topic are about the item Deterministic

Viewing 15 posts - 1 through 15 (of 25 total)

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

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 1

Hi,

Sorry, but i am unable to understand question completely.:ermm:

If you increase varchar length then it will give right answer. Please try below T-sql statements

SELECT CONVERT(VARCHAR,GETDATE(),20)

SELECT CONVERT(VARCHAR,GETDATE(),21)

SELECT CONVERT(VARCHAR,GETDATE(),106)

SELECT CONVERT(VARCHAR,GETDATE(),107)

SELECT CONVERT(VARCHAR,GETDATE(),109)

SELECT CONVERT(VARCHAR,GETDATE(),113)

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!

kapil_kk SSC-Insane Points: 21316 More actions · Answer 2

For me this question is NON deterministic 😉

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Mighty SSCrazy Eights Points: 9063 More actions · Answer 3

Danny Ocean (3/21/2013)
Hi,
Sorry, but i am unable to understand question completely.:ermm:
If you increase varchar length then it will give right answer. Please try below T-sql statements
SELECT CONVERT(VARCHAR,GETDATE(),20)
SELECT CONVERT(VARCHAR,GETDATE(),21)
SELECT CONVERT(VARCHAR,GETDATE(),106)
SELECT CONVERT(VARCHAR,GETDATE(),107)
SELECT CONVERT(VARCHAR,GETDATE(),109)
SELECT CONVERT(VARCHAR,GETDATE(),113)

The question is not about the statements executing correctly, but if the result of the CONVERT is deterministic. And that means that the CONVERT always should return the same result, independent of the language or any other setting. This is e.g. not the case with dates having month names in it.

nenad-zivkovic Default port Points: 1448 More actions · Answer 4

SET LANGUAGE Italian

not the greatest example as Marzo will also shorten to mar, try Czech, Croatian or Thai 🙂

_______________________________________________
www.sql-kefalo.net (SQL Server saveti, ideje, fazoni i fore)

Lokesh Vij SSChampion Points: 10836 More actions · Answer 5

kapil_kk (3/21/2013)
For me this question is NON deterministic 😉

I thought the same at first look. After bit of research, I got the curx and answered correctly 🙂

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

Carlo Romagnano SSC-Insane Points: 22669 More actions · Answer 6

These conversions are non deterministic, but from string to date:

from BOL:

Certain datetime Conversions Are Nondeterministic in SQL Server 2005 and Later Versions
In SQL Server 2000, string to date and time conversions are marked as deterministic. However, this is not true for the styles listed in the following table. For these styles, the conversions depend on the language settings. SQL Server 2005 and later versions mark these conversions as nondeterministic.
The following table lists the styles for which the string-to-datetime conversion is nondeterministic.
All styles below 100 (1)
106 107 109 113 130
(1) With the exception of styles 20 and 21

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 7

So a part of the correct answer is "all styles below 100" are non-deterministic, but another answer is "not 20,21", because they are deterministic...

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 8

Thank you for posting, good one.

Never digged so deep on this area, was interesting to learn.

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

kapil_kk SSC-Insane Points: 21316 More actions · Answer 9

Raghavendra Mudugal (3/22/2013)
Thank you for posting, good one.
Never digged so deep on this area, was interesting to learn.

+1 🙂

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

(Bob Brown) SSCrazy Points: 2705 More actions · Answer 10

Great question. Didn't know the answer off the top of my head but dug into the CONVERT command and got it. Thanks.

venoym SSCarpal Tunnel Points: 4161 More actions · Answer 11

May I be the first to point to BOL for Non-Deterministic?

http://msdn.microsoft.com/en-us/library/ms187928(v=sql.100).aspx

Take a look at the Remarks section. There is a table with superscript "1"'s in it... All of the "1"'s are defined as Non-Deterministic.

So, According to BOL, the values:

20, 21, 101, 102, 103, 104, 105, 108, 110, 111, 112, 114, 126, 127, 131 are deterministic, all others are non-deterministic.

sburcombe SSCrazy Points: 2460 More actions · Answer 12

sburcombe

SSCrazy

Points: 2460

March 22, 2013 at 7:57 am

#1599780

Great question. I learned something today.

Simone

sestell1 SSChampion Points: 10230 More actions · Answer 13

Interesting question.

I knew what the question was asking but got hung up on the wording of the answers for a bit. I think I would have phrased things a bit differently, so you don't have to infer "except..." at the end of the first selection.

m mcdonald Hall of Fame Points: 3264 More actions · Answer 14

The way I read there are only 2 correct answers:

not 106,107,109,113

20,21

The answer, and included as a correct answer

Styles greater than 100

Cannot be correct because of the exceptions 106,107,109,113.

The answer appears to have been incorrectly parsed...

From http://msdn.microsoft.com/en-us/library/ms178091.aspx

Source or target type is datetime or smalldatetime, the other source or target type is a character string, and a nondeterministic style is specified. To be deterministic, the style parameter must be a constant. Additionally, styles less than or equal to 100 are nondeterministic, except for styles 20 and 21. Styles greater than 100 are deterministic, except for styles 106, 107, 109 and 113.

</my 2 cents>