Trim functions deal only with spaces. The easiest way to take care of all whitespace is to use REPLACEs, something like this:

LEFT OUTER JOIN LedgerGroups lg ON

...

Verify that the db does not have autoclose or autoshrink set to on.

I'd shrink the log file:

DBCC SHRINKFILE ( 2, 4096 )

Rick Todd (2/10/2015)

---

Nevyn (2/10/2015)

---

What is the cash_pickups table? Seems to be in the plan but not the query? Is one of those tables a view? Or do you maybe have...

Also, verify that you have enough free log space on that db already allocated to handle all the deletes.

Make sure you have nonclustered indexes on:

transaction_hdr ( float_no )

transaction_tenders ( float_no )

You want SQL to scan the floats table, not use an index, since it will delete > 70%...

You might want to consider putting each client schema in a different db, then using synonyms in the main db to point to those objects. It will look to...

;WITH cteCustRecentOrders AS (

SELECT

AA.[CustomerCode],

AA.[OrderDate],

AA.CustomerProductID,

AA.Rank_ID

FROM (

...

True, I...

In the log file?

Yeah, that should be plenty, so the statement should run as fast it can.

Are there triggers, replication or...

I'd cluster the table by:

Payroll_Year, PayPeriod, EmployeeID and, optionally, CompanyID

That way the clustering key is generally increasing, which will reduce fragmentation. That's not the main criterion for a clus...

That statement is going to have the scan the table regardless of what you do.

The critical thing for performance is to pre-allocate (and thus pre-format) enough log space for the...

Yes. Why risk not recompiling it?

Get the query plan id and remove all plans related to this proc from the cache using:

DBCC FREEPROCCACHE { plan_handle }

JJR333 (2/5/2015)

---

Thank you Scott. It is slowly dawning.

The first NOT clause makes all the difference.

Is this T-SQL logic different from say Excel VBA logic?

SUM the turnover of all stores NOT...

You can put the SELECTs directly in the ISNULL, you don't have to use separate queries:

SELECT ISNULL((select H from table_1 where a = 'X' and b = 'Y'),

...