Skip to content

SteveStedman

Weird Join Delete

This confused me for a few minutes today and I wanted to share to help avoid further confusion.

 

The specific code was this:


DELETE t2
FROM [dbo].[Table1] t1
INNER JOIN [dbo].[Table2] t2 on t1.favColor = t2.id;

Names have been changed to protect the innocent.

 

In the above delete statement which table will have rows deleted from it?

A: Table1

B: Table2

C: Both Table1 and Table2

D: Neither Table1 and Table2

 

Read More »Weird Join Delete

SQL Server Row Data Linking to Off Row Data

 

The question came up as how to find a link from blog storage that is corrupt back to the table and row that contains that data.

The is no link from the blob storage back to the table and row, but this is a link from the data page containing the table and row off to the blob data.

 

First let’s start with row data and off row data. When SQL Server stores a row that contains variable size data like a VARCHAR, if all the lengths of all the values in the row are less than about 8k, SQL Server stores that entire row in a single data page. If a row contains a set of data larger than 8K, then some if it can be moved off row and stored in blob storage.

Picture the following table:


CREATE table test1
 (
 ID INTEGER IDENTITY PRIMARY KEY CLUSTERED,
 bigVarchar1 varchar(max),
 bigVarchar2 varchar(max)
 );

If you were to put small values into the varchar columns then the entire row would fit in a single data page (8k). But if you put 4k of data into one varchar(max) and 8k of data into the next varchar(max) then it will not fit in a page. One of the varchar values will be moved off page into a page that contains blob storage. No rocket science here yet, but when you want to find the relationship between these page it gets difficult.

 


CREATE DATABASE blobTest;
GO
USE blobTest;
GO

CREATE table test1
(
 ID INTEGER IDENTITY PRIMARY KEY CLUSTERED,
 bigVarchar1 varchar(max),
 bigVarchar2 varchar(max)
);
GO
-- fits into a single page
INSERT INTO test1(bigVarchar1, bigVarchar2) VALUES ('test row ', 'test row ');

--SELECT * FROM test1;

DBCC IND('blobTest','test1',-1) WITH NO_INFOMSGS;

Using the undocumented DBCC IND command you can see that the table (clustered index) is comprised of 2 data pages, the first one of type 10 which is the IAM page or Index Allocation Map, and the secon page of page type 1 which is a standard data row.
<img class=”aligncenter size-large wp-image-6595″ src=”http://stevestedman.com/wp-content/uploads/rowData1-1024×82.png” alt=”” width=”605″ height=”48″ />

Next we add another row.

INSERT INTO test1(bigVarchar1, bigVarchar2) VALUES ('test row ', 'test row ');
DBCC IND('blobTest','test1',-1) WITH NO_INFOMSGS;

And we can see that both rows are still only using the same 2 pages.

Read More »SQL Server Row Data Linking to Off Row Data