What would the best way be to test the performance/improvement if any of using a clustered index view?

<Ans> You can drop or disable the index before load and then recreate...

Yes... I think 1 or 2 min will be too short (if you don’t know when they will upload the file) and again it’s depending on your requirement how soon...

I think the suggestions which you got from John and Daniel are good but..

who do you know full data file is available on source location? suppose we have 10000 records...

I am providing 2 queries if it could help:-

SELECT CONVERT(VARCHAR(15), Cast(sdate AS DATE), 101) AS Startdate,

CONVERT(VARCHAR(15), Cast(edate AS DATE), 101) AS...

You can use Merge join available in SSIS package

create table dbo.SalesOrder(SalesOrderID int ,ItemID int ,SalesAmount int)

insert into dbo.SalesOrder(SalesOrderID,ItemID,SalesAmount)

select 1,101,100

union all

select 1,104,120

union all

select 2,101,200

union all

select 2,103,225

union all

select 3,101,200

union all

select 3,102,240

union all

select 3,103,450

union all

select 4,101,100

union all

select 4,102,480

union all

select 4,103,450

union all

select...