You're missing the point, Scott. ...

That is a simple q, I say give it a simple answer.  There's no need to over-analyze every q or nit pick its wording.  Of course interview qs don't contain...

If there's any possibility that the NOT IN column checked can be NULL, then you need to explicitly exclude NULL(s).  It doesn't sound like it could be NULL for this...

Naturally b is the answer to that specific question.

As to why:

(1) the table has 1M rows; therefore, creating an index with only the columns you need will typically drastically cut...

SELECT User, ProcessId, COUNT(*) AS ProcessDupCount

FROM dbo.table_name

GROUP BY User, ProcessId

WHERE ProcessId > 1

HAVING COUNT(*) > 1

This should give you the data type of the column:

SELECT TOP (1) wf.SMP_FLAG

INTO #temp

FROM WF_SMP wf

EXEC tempdb.sys.sp_help #temp

Here's the original post:

"I have a little exercise and would like to know your opinion on the most effective/best way to approach.  Please see the result I am looking for...

You're welcome.

Yeah, you'll need lots of extra disk space.

Presumably the log file is already a big size for a db that includes a table that size.  But you might want...

For example purposes, I'll assume your clustered index is on a $IDENTITY column, say "id".

(10) Create a new table, with the new data types, and the clustered index (only!) from...

You need to convert the "NOT IN"(s) to "NOT EXISTS"(s), like this:

NOT EXISTS(SELECT 1 FROM TB_REINPUT TR WITH (NOLOCK) WHERE TR.ORDER_DATE>=@LAST_ORDER_DATE AND TR.STATION_ID=@P_STATION_ID AND

TR.ORDER_DATE+COMMIT_NO = TB_WORK_ORDER_MFD01.ORDER_DATE+TB_WORK_ORDER_MFD01.COMMIT_NO)

You don't really need sys.indexes in the row-count query:

OUTER APPLY (
    SELECT SUM(p.rows) AS TotalRows
    FROM sys.partitions p
   ...

My approach was similar (I couldn't post to this site for a while):

SELECT 
    text_column,
    LTRIM(RTRIM(SUBSTRING(text_column, CASE WHEN position_after_required_name = 0...

Need the sample data in a directly usable format, not in a picture, which we can't write T-SQL against.

The idea being that you can change the starting day to adjust for any day of the week.  0=Mon, 1=Tues, ..., 5= Sat, 6=Sun.

For example, if you wanted the next...

I use a consistent method for any day, so I don't use techniques like the one given.

Instead, I recommend:

DATEADD(DAY, -DATEDIFF(DAY, 5, S1SDAT) % 7 + 7, S1SDAT)

for example:

SELECT

S1SDAT,

DATEADD(DAY, -DATEDIFF(DAY, 5,...