Questions tagged [determinism]
10 questions
13
votes
1 answer
How deterministic is execution plan creation in SQL Server?
Given the following constants:
The same database with the same structure (tables, indexes, etc)
The same data
The same SQL Server and hardware configuration
The same statistics
The same SET options in the client
The same SQL Server versions
The…
James Lupolt
- 4,278
- 5
- 31
- 46
8
votes
2 answers
MySQL Deterministic Procedures
As a general rule, should I be declaring all my stored procedures with the DETERMINISTIC keyword if they are indeed deterministic?
It seems to me that the majority of stored procedures will be deterministic. Am I right in thinking that the only…
David
- 549
- 3
- 10
6
votes
1 answer
Is my function nondeterministic one
I have two functions: fn_Without_Param and fn_With_Param
CREATE FUNCTION [dbo].[fn_Without_Param]
(
)
...
INNER JOIN .. ON .. AND SubmitDate = CONVERT( varchar(10), GETUTCDATE(), 101 )
and
/*
I am requesting it so:
declare @SubmitDate…
garik
- 6,782
- 10
- 44
- 56
6
votes
1 answer
Are the results of the STRING_SPLIT() function returned in a deterministic order?
I need to split a comma delimited string, manipulate it, and then concatenate it back into a single string retaining the original order of the data (if possible).
For example, take a column definition list of a CREATE TABLE statement (as a string)…
J.D.
- 40,776
- 12
- 62
- 141
5
votes
1 answer
Why am I getting nondeterministic results with the deterministic function STDEV()?
This is type of query I'm trying to run:
WITH CTE_Ordered AS
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY PartitionField ORDER BY DateField) AS PartitionRowId
FROM SourceTable
),
CTE_Top1_PerPartition AS
(
SELECT *
FROM CTE_Ordered
WHERE…
J.D.
- 40,776
- 12
- 62
- 141
4
votes
2 answers
Are table-valued functions deterministic with regard to insertion order?
Is insertion order deterministic in a TABLE-valued function which instantiates the table and populates it with successive inserts, assuming no ORDER BY clause is used?
I needed to take a look at the range of Unicode characters in use in a given…
Tim
- 545
- 4
- 13
3
votes
1 answer
Computed column 'IsWeekend' in table cannot be persisted because the column is non-deterministic
I'm in the process of tuning a query in SQL Server 2012 that is spilling to tempdb due to an incorrect cardinality estimation when using the DATENAME() function on a column to check if the data is weekend or weekday. Due to the the use of the…
Mark Sinkinson
- 10,657
- 4
- 47
- 54
3
votes
1 answer
Does the SQL Server generate the contents for a derived table contents for every matching record in a join
I am on SQL Server 2008 R2. I am executing the following SQL a
use tempdb
go
create table t1 ( c1 int, c2 int, c3 uniqueidentifier null)
go
insert t1(c1,c2) values (1,2),(1,3)
go
update t1
set c2 = 5 + c2,
c3 = t2.c3
from t1 join ( select c1=1,…
QFirstLast
- 451
- 2
- 11
1
vote
0 answers
log_bin_trust_function_creators deterministic always throws an error on server restart
Everytime my rackspace server restarts because of some maintenance or an outage, all of my mysql functions stop working due to this deterministic error.
To fix this, I have to manually pull up the database and run SET GLOBAL…
Sean Clark
- 111
- 2
0
votes
1 answer
Mysql deterministic function, scope?
The documentation for MySQL isn't explicit on this point, but I'm wondering whether the scope for a function with respect to DETERMINISTIC is database global, or by some other scope?
Consider this definition:
CREATE
FUNCTION test()
RETURNS…
Matthias
- 31
- 4