I recommend having a clustered PK on all TVPs. I enforce this in code reviews too. 1) it keeps data transfer to a minimum by forcing sloppy development from passing...

Anytime, thanks for the feedback.

In this...

You're updating the table you're selecting from and trying to use NOLOCK on so chances are the NOLOCK will not be respected.

NULL variable-length columns are returned as NULL based on the metadata of the table (and the NULL-bitmap of the record) when the result set is constructed but occupy no space...

For one, NOLOCK is ignored for updates. Think about it.

Also, even if data is not overlapping it does not mean that pages may not overlap. Page splits from updating records...

Because of how NULL variable-length columns are stored you should not see a difference in your two sample tables.

Try changing one of the columns to fixed-length though and you'll...

Shcherbunov Neil (3/3/2013)

---

So - these 2 values can ever be NOT equal?? As I said I had never see this in practical. Can you please give the example of table...

I am assuming you altered the graph before posting since the first query has a syntax error in it:

WITH TableA_CTE

...

Here is what I use to monitor Database Mail. Run it in your environment and see if it turns up any useful information.

DECLARE @days_ago_start INT

---------------------------------------------------------------------------------------

SET @days_ago_start = 7

---------------------------------------------------------------------------------------

SELECT fi.send_request_date...

I am assuming the PK on the parent is an IDENTITY column? In those cases I use an OUTPUT clause on the INSERT...SELECT to capture the new IDENTITY value for...

What comes to mind initially is that @cmd is sometimes ending up NULL due to @c1 or @t1 (coming from the cursor-select) being NULL since anything+NULL=NULL. Try adding some logging...

Nah, see my last post.

If you connect to the Dedicated Admin Connection (DAC) you can run this query against the restored copy of master to retrieve the previous setting:

SELECT value AS [max server...

Was curious so just tried it myself but no dice. sys.configurations uses a rowset and a system view that eventually point back to the real master.