The ORDER referred to is when using BULK INSERT, for example loading a physical file into a table.

In those cases, adding "WITH ( ORDER(clus_key1, clus_key2) )" can allow SQL to...

(I originally used the wrong quote with my comments here, so I've repeated them below but with the correct quote first.)

• This reply was modified 5 years, 3 months ago by ScottPletcher.

A lot of places do tran log backups every 15 minutes.

No.  The human brain is designed to recognize patterns.  Sticking with a familiar patterns drastically speeds up...

1 min is just way too often.  Especially for only 1000 rows per day.  That's a miniscule load for SQL Server.

No.  The human brain is designed to recognize patterns.  Sticking with a familiar patterns drastically speeds up recognition of...

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...