The Heap™ – Consultancy ©®

« first day (4040 days earlier) ← previous day next day → last day (811 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.

Paul White

1:20 AM

Q: NEWID() In Joined Virtual Table Causes Unintended Cross Apply Behavior

My 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...

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...

sql-server t-sql cte

Cade Roux

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…

Paul White

4:59 AM

A: SQL Server splits A <> B into A < B OR A > B, yielding strange results if B is non-deterministic

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…

Michael Green

11:22 AM

Morning all

1 hour later…

John K. N.

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

Update 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...

discussion featured design announcements greatest-hits

That has gathered some momentum.

371

A: New post summary designs on site home pages and greatest hits now; everywhere else eventually

feature-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...