To be absolutely clear, you want a:

2) unique clustered [index] on (tenant,year,id)

And if you partition the data (which typically wouldn't be necessary here), partition it...

don't worry about it.

thanks Jeff.

regarding the "performance and cartesian joins" - splitting the CTE to insert the BASE dates onto a temp table and then joining that temp table onto the main table...

• This reply was modified 2 years, 9 months ago by frederico_fonseca. Reason: add machine spec

To be absolutely clear, you want a:

2) unique clustered [index] on (tenant,year,id)

And if you partition the data (which typically wouldn't be necessary here), partition it by (tenant,...

I added 1000 "sets" of 3 groups, each with 13 dates.  The Cartesian products persisted in the execution plan, each of them doing reads of 39,078,039 rows.

Too bad because,...

@frederico_fonseca I had the same idea as you initially, but your solution will not work either. What if there were 20 groups instead of 3? What if the gaps...

in order to split a string like that into individual columns you have to first split into rows then aggregate onto columns

sample code below -  function DelimitedSplit8K_LEAD is from Erikur...

@jeff-moden - did a small change on original code to "correct" one duplicated entry that was on both group 2 and 3 (darn time part messing up)

• This reply was modified 2 years, 9 months ago by frederico_fonseca.
• This reply was modified 2 years, 9 months ago by frederico_fonseca.

This is a delayed message as I...

my attempt at this.

Do note that the 5 days interval is partially misleading as to the final result because of the time part.

,(7 ,'2017-11-17 09:38:00.000') --Group 2 starts here

,(7 ,'2017-11-12...

• This reply was modified 2 years, 9 months ago by frederico_fonseca. Reason: change to remove an error - lines changed tagged with " -- CHANGE to line below"

regardless of other items, there is one thing you do need to confirm - is the HOST set to have cpu at full power all the time (or not) and...

CTE is defined BEFORE any of the remaining sql.

with cte1 as (select ...), cte2 as (select ...), cte3 as (select ...)
select *
from table f
inner join cte1 on...

neither does it deal with scientific values where the whole string IS the value

declare @String varchar(30)
select @String = '1E02'
select 
 try_convert(float, @string)
,case 
when ISNUMERIC(@String) = 0 then...

while this looks like spam post I'll give it a go in case it isn't.

you should first read the manuals for triggers in sql server to fully understand how...

in my shop all instances are created using a powershell script - with defaults set for everything and potentially override...