Thanks Scott, you have waaay too much time on your hands.

That was effectivley what I was going to do but rather than create 300 views, just dump the data...

@pamkagel ,

Ok... you said that the entire table is generated nightly.  That makes me want to assume that you're not actually doing any updates or additional inserts to the...

That is, something like this.  I had to set up directly usable data myself to verify the code, which, btw, took me longer than writing the code itself!  In the...

I would create a view matching each AttributeType.  I think that would be the easiest to use.  Is there some reason that wouldn't work for what you need here?

The lazy programmers' way to delete from complex data structures is to get a data comparison tool to generate the SQL DELETE transaction.  Where I work we've used 2...

I'm also trying to improve overall performance, not just the load time.

What do you mean by row number?  How is that column created and populated - is it really an identity column?  If it is really an identity column then...

NEVER use ISNULL() in a WHERE or JOIN.

Instead, in this specific case, use this method:

select * from #OutputTable

where (@SeasonalAddressFlag = 0 OR SeasonalAddressID IS NOT NULL)

where the Primary Key/Clustered Index is based on SalesOrderID (identity column)

And that's the issue.

If you will often query by OrderDate BETWEEN ..., then cluster the table on:

( OrderDate, SalesOrderID )

in...

I'd recommend trying the approach below.

Specifically, the code below:

1. Changes the fillfactor to 99.  80 is way too small (unless you do massive updates to almost the entire table later?!...

• This reply was modified 5 years, 1 months ago by ScottPletcher. Reason: Added ONLINE = OFF to list of changes I made. I forgot to mention this before, but I did make the change

1. Not generally available. If an index was created by adding a PRIMARY KEY constraint, sys.objects would have the create time of that index.

2. Yes. For example:

SELECT

t.name AS table_name, i.name...

Something like below is what you need to do.  If the column names are the same, you will need to make sure each has a different name in the final...

Why not create the clustered index on the temp table before it gets loaded?  That would save extra writing / rewriting of data, I would think.

Do you mean a separate row for every column that changed?  That's massive overhead.  And if you get the column name(s) dynamically, that's really insane overhead.

Have the first step in the job start another job that monitors the starting job.

For example, if you're trying to monitor JobA, the first step in JobA starts job JobA_monitor...