I was thinking about this too.   One question though. ...

Why not creating a NONCLUSTERED Index on the datetime column and "walk in small batches" through the table.

Small chunks of data prevent LOCK ESCALATION and you can run it...

Thanks a lot.  I can make a plan from that.

I also read about using 'SWITCH' to map the data to the backup table, then insert the records back into the original table.   Once the data dump is done, I'll...

Hehe.  Ok.  Thanks.

I stumped everybody?  wow. hehe

Recently ran into this and none of the solutions I found online worked, so posting my solution here.

I found my issue by checking the SQL server system event logs and...

As an example, if I move the where clause to the query, the filter is applied before the dense_rank window.

drop view if exists drview;
go
create view drview
AS
with cte...

• This reply was modified 2 years, 11 months ago by Jackie Lowery.

Sorry, field 3 is a field in the table, it's not the dense rank value.  I've updated the original question to include your code with a small update.

• This reply was modified 2 years, 11 months ago by Jackie Lowery.

add_cus_cnt = distinct count of customers per address.

cus_cnt = distinct count of customers

The idea is that getting the addresses that have a distinct count of customers equal to the distinct...

This is what I've come up with so far.  Can someone grade me on this, or say if you think there's a better way?

with ad as (
...

I've just been reading that 2019 has a lot of improvement in the area of the query optimizer, table variables, etc.  Not really looking for new features.  Just wondering if...

Actually, it depends on how the table is searched.

Do you often filter the rows by only type when SELECTing?  If so, then the clus index makes sense as is. ...

Looking at the Clustered Index for the oeordlin_sql table, I'm a wee bit concerned (especially for insert and update performance) over having the leading column be in the "ultra...

Here is all the indexes for the tables. If you need different format, let me know.  P.S., the view only creates aliases for some of the columns in the...