For a stand-alone, nonclustered index with just that column in it, REBUILD that index setting the FREESPACE to 50[%].  That will delay any fragmentation for some amount of...

OMG !!

Wow .....the cross apply worked like a Charm.  and it was quick , even though now checking the table index is Clustered on identity, I...

From what I could find and read SQL Server uses memory to cache pages and to cache execution plans.

1.       I know how I set the total memory used by...

OMG !!

Wow .....the cross apply worked like a Charm.  and it was quick , even though now checking the table index is Clustered on identity, I will go...

Try the code below.  If there are a lot more spec_size values than the ones listed here, uncomment the IN list to pull only the ones you need.  If those...

For a stand-alone, nonclustered index with just that column in it, REBUILD that index setting the FREESPACE to 50[%].  That will delay any fragmentation for some amount of time, at...

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