Add another temp table to hold the Project & Packages you want to list. Then run the SELECT below.

Btw, you should add a clustering key to the #DateHr table...

Not sure which ClientId column is in Temp_Client, so change if needed:

SELECT [AssigneeID]

FROM [dbo].Assignee

LEFT OUTER JOIN Temp_Client ON Clientid = Client_ID

WHERE @clientid = 'ALL' OR Temp_Client.Clientid IS NOT...

CREATE TRIGGER %%

ON ALL SERVER

WITH EXECUTE AS 'sa'

AFTER

CREATE_TABLE,ALTER_TABLE,DROP_TABLE,CREATE_VIEW, ALTER_VIEW, DROP_VIEW

, CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION

, CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE

AS

SET ANSI_PADDING ON;

SET ANSI_WARNINGS ON;

SET ARITHABORT ON;

SET CONCAT_NULL_YIELDS_NULL ON;

SET NOCOUNT ON;

SET NUMERIC_ROUNDABORT...

DesNorton (4/6/2016)

Hi Scott

This is certainly fast, with similar I/O to the other solutions, but very low CPU numbers. However, I am unable to wrap my head around the math...

Rather than using a gui, use a SQL RESTORE command to do the restore. Then the new files can be specified using the "WITH" clause.

And here's a function I...

I think you can use the (old) view "sysusers", "WHERE isaliased = 1" to find any aliases.

Another possibility is to leave the main table alone, and create a separate table to hold the partial partids to match on. Use a trigger on the main table...

MS provides tablediff.exe for free. It works ... but it is a command-line tool and, like bcp, takes a bit of time/work to get used to and get it...

No, a stop / start of SQL Server will not cause an identity value to go down. Your code must be resetting it for that to happen.

Yes, creating a non-clustered index on the clus index column(s) can indeed help performance for certain queries. It's a valid thing to do when that situation exists.

You could also consider using an A/B table approach. That is, the nightly load goes into a different table name. When the load process is complete, either the...

Most likely a LOOP join will be used. That is not guaranteed, of course, but it is likely.

If you want to be sure what join algorithm will be used,...

Look at the tempdb log file size and growth amount, particularly if it is a % (which you should change to a fixed amount: never use %, no matter what...

Alan.B (3/31/2016)

1. Nothing I posted uses recursion. Look again.

2. People should put unique nonclustered indexes on their dim_date or calendar but dont. It's not like it gets modified all...

We don't really know why MS chose to make the PK by default also the clustering index.

Personally I think it was more about making it "easy to use" so it...