The first command might not shrink the file at all, because of the "TRUNCATEONLY" option.

The second command should work, but shrinks can take a long time.

For best performance, you want to stop using NULL so you don't have to do ISNULL() as part of the WHERE.  Yes, you will have to go back...

Once you get rid of the LOWER on the vehicle type, then the clustering key could be ( VehicleType, ValidFrom, ValidUntil ).

For best performance, you want to stop using NULL so you don't have to do ISNULL() as part of the WHERE.  Yes, you will have to go back and change...

SELECT LEFT(devicename, month_in_date - 2) AS device, MAX(CAST(devicedate AS datetime)) AS max_device_date
FROM #test1
CROSS APPLY (
    SELECT PATINDEX('%[0-9]/%', devicename) AS month_in_date_prelim
) AS ca1
CROSS APPLY (
...

I think CHARINDEXes might be somewhat more efficient here:

;WITH data AS (
    SELECT rl_event_id = 'AB123456_BlahBlah_BESTSELLER_blahBlah'
)
SELECT rl_event_id, SUBSTRING(rl_event_id, pos_of_second_underscore + 1, pos_of_third_underscore - pos_of_second_underscore...

The db that table was in already had space allocated but that had not been used yet.  Therefore, SQL can allocate that space to a table without having to get...

RAM doesn't look too short there, but personally I'd add more if you could.  RAM is the cheapest overall performance boost you can get.  And bump up tempdb size if...

dup, removed

Update T --<<-- *must* use T here (the alias and NOT the table name) to be safe
SET ACTIVE=CASE WHEN S.user IS NULL THEN 'N' ELSE 'Y' END
FROM...

(1) Cluster the "FF" table on ( FileDate, ISIN ) (if it's not already clustered that way).

(2) Some of the JOIN conditions are out of place.  Change this code:

            
August 22, 2023 at 2:41 pm
            
#4268207

            
            
                                                
        

Not enough info provided for us to be able to say.  (1) Is SQL indicating that it's short of memory?  (2) How large are  your databases? (3) How many simultaneous...

It's likely the fragmentation is having some effect on overall performance by causing some small additional I/O.

To clarify, for a single keyed lookup, the index performance is not affected at...

Select * from Table T1

JOIN TableT2 on (T1.A IS NOT NULL AND T1.A =T2.A) OR (T1.A IS NULL AND T1.B = T2.B)

Also how do I check if the table has had any options set for SP_TABLEOPTIONS such as 'LARGE VALUE TYPES OUT OF ROW'. I doubt this would be the...