That's very odd code. It looks like whoever wrote it didn't know...

Maybe as below?  I've got a feeling CONCAT may be a bit slow, just like some of the other newer functions.  FORMAT is notoriously slow.

CASE
WHEN Phone IS...

The standard approach is to use ELSE to avoid unnecessary comparisons and show that only one path can be executed.  Something like:

IF @param1 = 1 AND @param2...

SELECT
    FilePath,
    LEFT(FilePath, LEN(FilePath) - CHARINDEX('\', REVERSE(FilePath)))
FROM #Path

I see no need to use a 4 digit year here.  Almost no businesses go back and analyze 100-year-old data, so there's no ambiguity.  Even more so if you...

> how to optimize the query from the table design to create index and statisctis ? <<

As Jeff said, you can't, not with the data in its current form.

You should...

Since PIVOT requires that you put specific values in the PIVOT, you'd at least have to use dynamic SQL to make a PIVOT work.

I do the same thing with transaction log backups... if the "log_reuse_wait_desc" in the sys.databases view contains "NOTHING" for a database, then nothing in the database has...

You could have told us "20 rows" instead of making us count them :-).

Look in the SQL Server error log, there may be additional info there.

Just in case, add another file(s) to tempdb if you can, to give it additional space.  You never...

One thing to look out for is UPDATEs that significantly expand the size of varchar columns because that can cause page splits which can be a real performance issue, both...

I do the same thing with transaction log backups... if the "log_reuse_wait_desc" in the sys.databases view contains "NOTHING" for a database, then nothing in the database has changed and...

In addition to Jonathan's excellent advice, be sure to also pre-allocate enough log space to handle the full INSERT, especially if the log growth is a small amount.  Log space...

It's terribly scary to me that this is a "class".  They're teaching people exactly the wrong things to do.

In my head, I envisioned having the database set up in such...

8<

>8

An is_active flag (someone can be inactive and still not "terminated", e.g. "suspended"), specifically, makes perfect sense to me because the specific inactive date / conditions...