190 reputation
18
bio website
location United States
age 56
visits member for 2 years
seen Oct 3 at 19:38

Design, develop and maintain C# WPF and ASP.NET business application with (typically large) MSSQL backends. Degree in engineering and got into computing via numerical methods.


Sep
25
awarded  Supporter
Sep
25
comment Are RANK() and DENSE_RANK() deterministic or non-deterministic?
@ypercube That depends on your definition of same output. Same rows with correct answer for rank but not in the same order (see answer from Aron) I call non-deterministic.
Sep
25
comment Are RANK() and DENSE_RANK() deterministic or non-deterministic?
@hvd So GetDate() is guaranteed to return A date. It is not guaranteed to return the same date. It is non-deterministic. An example that returns consistent results is not proof of deterministic.
Sep
25
comment Are RANK() and DENSE_RANK() deterministic or non-deterministic?
@hvd If there is only one valid answer then deterministic does not come into play. If there is more than one valid answer - observed deterministic behavior does not prove it is deterministic. If the documentation states non-deterministic behavior then you cannot expect deterministic behavior.
Sep
25
comment Are RANK() and DENSE_RANK() deterministic or non-deterministic?
@ypercube Then trust a non-Microsoft article from 2004. Just because you observed deterministic behavior in the OP's example doe not mean that sample deterministic in all environments. No I am not going to spend my time to try and find an example it is not deterministic.
Sep
25
comment Are RANK() and DENSE_RANK() deterministic or non-deterministic?
@hvd What? I know what the definition is. Yes deterministic is a behavior. And a non-deterministic function is not guaranteed to have deterministic behavior.
Sep
25
comment Are RANK() and DENSE_RANK() deterministic or non-deterministic?
@ypercube Just because you observe deterministic does not mean it is. You can observe a default sort by PK on a select with no order by.
Sep
25
comment Are RANK() and DENSE_RANK() deterministic or non-deterministic?
The Microsoft documentation says non-deterministic. You observe non-deterministic. A 2004 article (not from Microsoft) says deterministic. And you ask who is right?
Sep
25
comment Are RANK() and DENSE_RANK() deterministic or non-deterministic?
@hvd You are mixing the definition of deterministic with behavior. I have two statements. One, not providing the same input if the order is different. Two even if you did provide the same input if the function is nondeterministic you may not get the same output.
Sep
25
comment Are RANK() and DENSE_RANK() deterministic or non-deterministic?
But order by salary is non-deterministic as all salaries are the same. It does not matter that set theory would call those two tables the same. Same input includes same order.
Sep
24
awarded  Autobiographer
Aug
21
awarded  Notable Question
Jul
17
comment Table with 4 million records - different size and performance in development and production
@ypercube But the dev database is 4.5 GB.
Jul
17
revised Table with 4 million records - different size and performance in development and production
deleted 2 characters in body
Jul
17
answered Table with 4 million records - different size and performance in development and production
Jun
20
comment how to solve slow running sub query TSQL
That posted example is not even valid syntax. You cannot know that example represents the performance issues the real query if it does not even run.
Dec
2
comment Huge slowdown to SQL Server query on adding wildcard (or top)
Did you look at the query plans? What do you mean fields are freetext? Freetext is a predicate used in the Transact-SQL WHERE clause.
Sep
18
awarded  Popular Question
Jul
11
accepted SQL Server not using available memory
Jul
11
comment SQL Server not using available memory
Thanks, that is one of the first things I should have checked. Will accept when it lets me.