Reputation
Top tag
Next privilege 200 Rep.
See reduced ads
Badges
1 8
Newest
 Supporter
Impact
~6k people reached

  • 0 posts edited
  • 0 helpful flags
  • 4 votes cast
Jun
19
answered TSQL insert table using view — performance issue
Jun
19
comment TSQL insert table using view — performance issue
One that join on P2 kill the left join on P. Pull the conditions in the where up into the join
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