For performance, first try converting it into an inline TVF; those get compiled directly into the code whereas a multi-line TVF does not.

SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER OFF
GO
CREATE...

D'OH, sorry, didn't realize SQL wouldn't let you OFFLINE the db in that case.  But, one of the reasons I avoid SINGLE_USER like the plague is the issues it causes.

You need to make sure that TABLOCK is specified on the load so that you get minimal logging.  I'm not 100% sure how to do that via SSIS but look...

Again, I'd say create the clustered index before loading the table.  Use row compression, since that could save some I/O which would reduce the time needed.

Are other processes  using "SELECT...

What specifically do you mean by that?  What error message did you get?

CREATE TABLE ##temp_table_order#s ( id int NOT NULL PRIMARY KEY, data_field varchar(8000) );
INSERT INTO ##temp_table_order#s
SELECT id, data_field
FROM ##temp_table
WHERE data_field LIKE 'Order #%'

SELECT
    Order_No,
 ...

I've always liked the idea of assigning a unique standard alias for every table and always using it.  You can add a 1, 2, etc., if you need to use...

No, you can miss user(s) of the db that way.  Instead, force the db offline; then bring it back online and immediately USE the db yourself and then set it...

FROM

dbo.fnGet_NAME]  (@processDate) -- This function is hitting a very big table and I do see some missing IDX hints, however, apart from IDX's I wanted to find out more...

Hmm, interesting.  For the nonclustered index(es)?  For a clus index create, I could see delays as SQL would have to sort the entire contents of the table.

What is the 'cost...

1.       The source is another table in another database and I am using a dataflow task to move it from one table to another. The source is a Stored procedure

There...

Best would be a CHECK constraint if that is possible.  You wouldn't have to write the code and SQL would guarantee that it gets run and works without error (given,...

I guess you have to fall back on querying the msdb.dbo.restorehistory table every nn minutes and looking up the details on all newly restored dbs, since the last check nn...

Rats!  Apparently RESTORE does not trigger a CREATE DATABASE event and there is no RESTORE DATABASE event, at least from what I've found so far.

If a RESTORE DATABASE triggers a "CREATE DATABASE" event, and I'm not sure about that although it seems logical, then you could use a DDL trigger to check for the...