« first day (4040 days earlier)      last day (119 days later) » 

12:21 AM
Well, learned something today about not using NEWID() in CTEs. That was super weird. Even though the CTE was referenced only once, it was like they were getting re-evaluated in the joins and causing the same "row" to fall into different disjoint ranges in my histogram.
`WITH Seq AS (
	SELECT Number
		, FirstNameSeq = ABS(CHECKSUM(NEWID())) % @FirstName_Range + @FirstName_StartValue
		, MiddleNameSeq = ABS(CHECKSUM(NEWID())) % @FirstName_Range + @FirstName_StartValue
		, LastNameSeq = ABS(CHECKSUM(NEWID())) % @LastName_Range + @LastName_StartValue
		, BirthYearSeq = ABS(CHECKSUM(NEWID())) % @BirthYear_Range + @BirthYear_StartValue
		, StudySigSeq = ABS(CHECKSUM(NEWID())) % @StudySig_Range + @StudySig_StartValue
	FROM DataSampling.dbo.Numbers
	WHERE Number BETWEEN 1 AND @BirthYearCount
If instead of using the table first, and using Seq CTE and joining like the second query I was getting more rows than were in the Seq set, so the inner joins were matching different parts of the histogram, like their random number was changing.
 
1:20 AM
10
Q: NEWID() In Joined Virtual Table Causes Unintended Cross Apply Behavior

JM HicksMy actual work query was an inner join, but this simple example with cross join seems to nearly always reproduce the problem. SELECT * FROM ( SELECT 1 UNION ALL SELECT 2 ) AA ( A ) CROSS JOIN ( SELECT NEWID() TEST_ID ) BB ( B ) With my inner join I had many rows for which I added t...

19
Q: Create a plan guide to cache (lazy spool) CTE result

孔夫子I normally create plan guides by first constructing a query that uses the correct plan, and copying it across to the similar query that doesn't. However, that is sometimes tricky, especially if the query is not exactly the same. What is the right way of creating plan guides from scratch? SQLKi...

 
1:53 AM
Yeah, thank goodness it didn't catch me for long
I assume what what happening was that the BETWEEN was being expanded into exp >= lb AND exp <= ub, and the expression was being expanded into each and executed twice in the join.
And then actually giving wrong answers. I bet some were not satisfying either bound and getting suppressed and others hitting multiple buckets.
 
 
3 hours later…
4:59 AM
22
A: SQL Server splits A <> B into A < B OR A > B, yielding strange results if B is non-deterministic

Paul White Is SQL Server allowed to evaluate A <> B as A < B OR A > B, even if one of the expressions is non-deterministic? This is a somewhat controversial point, and the answer is a qualified "yes". The best discussion I am aware of was given in answer to Itzik Ben-Gan's Connect bug report Bug with ...

 
 
6 hours later…
11:22 AM
Morning all
 
 
1 hour later…
12:42 PM
Morning
fookin' 'ell 43'00 cases in Switzerland reported today.
43'000 (Just in case)
-420
Q: New post summary designs on site home pages and greatest hits now; everywhere else eventually

Ben KellyUpdate 1 - 2022-01-25 Most of the reported bugs have been squashed. We're currently gathering together all the discussion and feature-request feedback items to decide how we want to alter the design. Thank you everyone for your feedback. Original Post tl;dr We’re starting a rollout of the new po...

That has gathered some momentum.
371
A: New post summary designs on site home pages and greatest hits now; everywhere else eventually

Thomas Markovfeature-request I cared about question scores, but now my head hurts. On the previous layout, I could quickly survey the question scores by reading down the votes column: Now, I simply cannot quickly survey the front page for question scores, as those scores are quite small and buried between ot...

 
@JohnK.N. but deaths are falling, right?
that should be good.
Deaths in the UK are steady at about 250 per day on average
which is big. about 3.5 deaths per million.
I see Switzerland is at about 1.0 deaths per million per day
Greece is at 8.0 at the moment :(
 
1:07 PM
@ypercubeᵀᴹ Yes, that is correct.
 

« first day (4040 days earlier)      last day (119 days later) »