Many of my dbs are in simple mode since, for those for the most part, we can reload data from the past 12 hours rather easily.  So I can have...

I really wish SQL would add an option to rebuild within a specified key range.  That would allow the benefits of a full rebuild without the downside of forcing it...

As to reorganization, I believe it's still best practice to use reorg for columnstore tables (I'm on SQL 2016).   Full rowgroups won't be affected at all by this process, but...

There's no magic formula to tell you the best fillfactor for 3,000 indexes.  Or for 10 indexes, for that matter.

Taking a step back for a broader look, the single most...

I'm not 100% sure of what  you need, but I think the total from the first table is being overstated.  If so, then maybe this:

select 
count (H.Customs_Entry_Num)
,max(L.[Child_Customs_Entry_Num_Count])...

Thanks both

It looks like it's the ROW_NUMBER() section that's taking the time to work out.

Is there any sort of alternative to that bit?

I need to get the records in...

I think you do need at least a couple of indexes.  Also, you should capture both pat and ref flags in the same pass of the table and update them...

SELECT OrderNumber
FROM #X_ReadytoPick
GROUP BY OrderNumber
HAVING MAX(CASE WHEN OpsStatus = 'Okay' AND SOPstatus IN ('Acknowledgement', 'DocsPrinted') THEN 0 ELSE 1 END) = 0
ORDER BY OrderNumber /*optional, of course*/

First try "SELECT COUNT(*) FROM sys.dm_exec_cached_plans", to see how many cached plans you actually have.  Then go from there.

It really sounds like you should be using a SEQUENCE rather than an identity, that is, that this INSERT is just to get an identity value,...

My opinion, if it is a sequence or an identity really depends on a lot more than we can accurately guess from what was posted.  It might be that...

It really sounds like you should be using a SEQUENCE rather than an identity, that is, that this INSERT is just to get an identity value, which is...

It really sounds like you should be using a SEQUENCE rather than an identity, that is, that this INSERT is just to get an identity value, which is no longer...

An alternative is like below.  Which performs better depends on total data volume and index(es) present:

Edit: I'm at work so I have only a limited time to review all posts,...

• This reply was modified 5 years, 8 months ago by ScottPletcher.

You corrupted your data (at least for a relational model). You made it non-atomic, since that column now contains two pieces of info (type and id#), not one (just...