3 ms·
in practice SQL engines will know whether functions are determinsitic or non-deterministic. So with random() it will know it needs to re-evauluate it all the ti
by codeulike 3y ago
in practice SQL engines will know whether functions are determinsitic or non-deterministic. So with random() it will know it needs to re-evauluate it all the time because its non-deterministic. with left(somecolumn,2) it knows its deterministic so will only re-evaluate it when somecolumn changes
- dspillett 3y ago> So with random() it will know it needs to re-evauluate it all the time because its non-deterministic. This is often not true. SQL Server for one, and I know it isn't the only common DB that behaves this way: SELECT RAND() AS NotSoRandom , RAND() AS NotSoRandomAgain FROM SomeTable results in: NotSoRandom NotSoRandomAgain 0.417713566427078 0.417713566427078 0.417713566427078 0.417713566427078 0.417713566427078 0.417713566427078 0.417713566427078 0.417713566427078 ... This often confuses people trying to select random rows (or all results in a random order) with ORDER BY RAND().
- codeulike 3y agoActually yes now you remind me, SQL Server doesn't recognise Rand() as non-determinstic but it does recognise some functions as being non-deterministic, e.g. if you use newid() you get a new value for every row