Use EXISTS() instead of a JOIN (although SQL likely already is doing the equivalent of that in the plan, it doesn't hurt to be sure).

The only other thing you could...

If you typically/most often query the table by Final_date, you need to cluster the table first on Final_date.  If  you have a column that would naturally make the index unique,...

I think you would want something more like this:

DECLARE @DestinationTableName SYSNAME = 'dbo.tableA'
,@id NVARCHAR(30)
,@ProcessName VARCHAR(100) = 'ETL_InitialLoad'
,@SQL NVARCHAR(MAX)

SELECT @SQL = 'IF NOT EXISTS (SELECT * FROM dbo.ETL_lOG...

>> will process and return a table with Say DynamicMaster with column Distance added along with respective Data <<

How, specifically, does it return a table?  Does it create a temp...

Great.  Glad it helped, and that the abbreviated way I posted it made sense.

select SAR.ENC_IDN                         ...

(1) Make sure any relevant statistics on the table are up to date.

(2) Easiest might be to add:

OPTION(RECOMPILE)

to the end of the SQL and see if it helps; iirc, SQL...

Maybe like this?:

select 
       s.MBR_NAME                ...

Or maybe just this:

SELECT CompanyName, 
    LEFT(CompanyName, EndOfWord1) AS [First Word], 
    LEFT(CompanyName, EndOfWord2) AS [First  + Second Word],
 ...

You need to look at the "Estimated" plan for the one that doesn't work, just to be sure.  See if anywhere SQL is referencing or "output"ing the restricted column.

What is the specific error message (and error number and status) you get? (Using dummy column name(s) as needed, of course).

What is the query plan?  Does it give any help...

• This reply was modified 2 years ago by ScottPletcher.

You don't need to export to Excel to do the comparison, you can do it directly in SQL Server.

Look at the files on the drive and see if there is a large, obsolete file(s) (perhaps old backups?).

Depending on the version of SQL Server, you might also be able...

Try this.  It may not work because of the GROUPing.  You might have to do the GROUPing after concatenating the subquery results.

SELECT
DISTINCT 
G.[Period], 
G.[Sector], 
G.RID, 
STUFF
((
SELECT 
DISTINCT
...

Is your tempdb database on the C: drive?  If so, that could theoretically cause the problem.

By the way, 9GB is a very tiny amount of free space on a drive. ...