To get TEXT data off the main page, before you load the table, issue this command:

EXEC sys.sp_tableoption '<your_tablename>', 'text in row', 0

That will force all TEXT data into the LOB area,...

What's the avg len of the (MAX)?

You should analyze whether forcing them to LOB overflow would be better for your setup.

COMPRESS is slow when inserting, but it can save a...

True, key lookups aren't ideal, but in that case you don't have any choice.  A nonclustered index on the guid with a key lookup is your best option there.

Some other...

Assuming you don't want to return the second if the first gets a match -- you never really stated exactly -- then do this:

SELECT mt.BusinessUnit, mt.PartNum, mt.WONum, mt.TransDate, mt.TransQty, ISNULL(ot1.WOQty,...

So my response did not help you at all?

If you need to return data from the non-main tables conditionally, based on whether the WONum appears in each table or not,...

DELETE FROM dbo.Table WHERE ID IN (12); ... With the temporal table, deadlocks are guaranteed every time. Only one of the deletions will succeed; the rest...

But note that you can get phantom reads and nonrepeatable reads in the default READ COMMITTED mode as well.

And that the majority of issues with NOLOCK are caused by SQL...

Sadly, on 2014, you don't have the COMPRESS function available to help you out.  But when you upgrade to 2016 (or later), be sure to look into that.

(1) No, they are not the same.  SQL will move non-LOB data to out of row storage if otherwise a row cannot fit onto its 8K page.  For example, if...

Based on the limited info we have so far, the JOINs would look something like this:

SELECT *
FROM dbo.main_table mt
LEFT OUTER JOIN dbo.other_table1 ot1 ON ot1.WOQty = mt.WONum
LEFT...

DELETE FROM dbo.Table WHERE ID IN (12);

...

With the temporal table, deadlocks are guaranteed every time. Only one of the deletions will succeed; the rest will fail.

We have tried ......

Many index-suggestions I get from the database I'm working on, very often suggests quite alot of include-columns. Could this be a sign of poor database-design?  As far as I...

No idea.  They're not system tables that are intrinsic to MS.  Some other user/3rd party code/script must be creating them.

Heh... more...

BWAAAA-HAAAA-HAAAA!!!  Ok... I "get it".  Yep... I agree... you never have to mention "IDENTITY" in a logical model.  I have to tell you, though, that the idea of having...