Differences in the Null Bitmap between SQL 2005 and 2012

Hello Dear Reader, I’ve been working on a series for BIDN.com, the Business Intelligence Developers Network, on how to read a Data Record.  I’ve always wanted to take the time to go rummaging around in the internals of SQL Data Records and this was my shot to do it.  If you are interested please feel free to read Part 1 Tag bytes, Part 2 The Null Bitmap Offset, Part 3 Fixed Data, and Part 4 The Null Bitmap.

While I was working on part 4 I found that the results that I was getting for the Null Bitmap were a bit different than what I was finding on the blogosphere and the Intrawebs.  I was reading the blogs of some very smart people, so that lead me to one conclusion: I was doing it wrong.  So I started following their examples and comparing the results and well….there’s only so may ways you can type DBCC PAGE before you start thinking maybe I’ve found something.  I was working in SQL 2012 so my first thought was that it changed there.  So I stood up a VM with SQL 2008 R2 on it and I got the same results.  So then I stood up another VM with 2005 and Viola! I was looking at results just like everyone else.  So then I stood up ANOTHER VM with 2008 RTM and once again I was back to what I was initially looking at.

“So Balls,” you say, “What was different?”

Sorry for the Ramble Dear Reader, what I found was that in 2005 when you translate the Null Bitmap if you had a number of columns that doesn’t divide easily into 8, (The Null Bitmap is one byte which translates out to a map of 8 individual bits, and you get 8 whether you need them or not), instead of using 1’s for unallocated bits they use 0’s. 

I understand not exactly a jaw dropping conclusion but if you’re a geek like me that likes to dive into internals this should be a fun read.

LET’S ROCK

So now that we’ve got some extra detail out of the way let’s dive into examples.  First we’ll create our table with two fixed length columns and three variable length.  We’ll keep the variable length columns as nulls at first so we can see how the Null Bitmap changes.

IF EXISTS(SELECT NAME FROM sys.tables WHERE NAME='dataRecord2')

BEGIN

       DROP TABLE dataRecord2

END

GO

CREATE TABLE dataRecord2

                     (myID INT

                     ,myfixedData CHAR(4)

                     ,myVarData1 VARCHAR(6)

                     ,myVarData2 VARCHAR(6)

                     ,myVarData3 VARCHAR(6)

                     )

GO

INSERT INTO dataRecord2(myID, myfixedData, myVarData1, myVarData2, myVarData3)

VALUES (7, 'XXXX', null, null, null)

GO

Now that we’ve got our table and a record inserted let’s use DBCC IND to find our page.

DBCC IND(demoInternals, 'dataRecord2',  1)

GO

Remember type 10 is an allocation page and we want a data page or a PageType 1, we see that Page 288 is our candidate.  Now let’s use DBCC Page to get page 288, don’t forget to turn on Trace Flag 3604.  I’m only going to copy the relevant bits from our page dump and remember your page numbers may differ.  I’m using SQL 2012 for this output.

DBCC TRACEON(3604)

GO

DBCC PAGE('demoInternals', 1, 288, 1)

GO

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP    Record Size = 15

Memory Dump @0x00000000136EA060

0000000000000000:   10000c00 07000000 58585858 05001c             ........XXXX...

Here in Red, Green, and Bold (sorry to anyone who is color blind hence the bold…), is our portion of the record slated as our Null Bitmap.  0500 is the first two bytes of our record, remember two numbers equal a Hex pair.  Our Pairs are 05 & 00, they are flipped so you would read them as Hex 0x0005, which translates out to 5 and we do have 5 columns.  Don’t forget to use our handy Hex to binary convertor, http://easycalculation.com/hex-converter.php. The last portion of the record is the Null Bitmap itself 1c.  In Hex this works out as 0X1c and converts to binary as 00011100.  In order to read this binary value accurately we flip it and it becomes 0011100. 

We break this down per column, because we have two columns that are not null three Variable length columns that are null and then the remaining two 0’s are not in use.  So why are they are you may ask?   Because 1 byte equals 8 bits, and these are left over bits.

Now this is one of the differences that I’ve noticed from 2005 to 2008 on up, in 2005 the left over bits where marked as 1’s.  Let’s take a look real quick to back that up.  I set up a VM running Windows 2008 R2 and SQL 2005 Straight up RTM no Service Packs applied.  We’ll run the exact same scripts as above and look at the Null Bitmap output.

IF EXISTS(SELECT NAME FROM sys.tables WHERE NAME='dataRecord2')

BEGIN

       DROP TABLE dataRecord2

END

GO

CREATE TABLE dataRecord2

                     (myID INT

                     ,myfixedData CHAR(4)

                     ,myVarData1 VARCHAR(6)

                     ,myVarData2 VARCHAR(6)

                     ,myVarData3 VARCHAR(6)

                     )

GO

INSERT INTO dataRecord2(myID, myfixedData, myVarData1, myVarData2, myVarData3)

VALUES (7, 'XXXX', null, null, null)

GO

Now that we’ve got our table and a record inserted let’s use DBCC IND to find our page. Notice we use DBCC IND with a -1 if you attempt this with the exact same script as above you’ll get an output error for dump style 1.

DBCC IND(demoInternals, 'dataRecord2',  -1)

GO

Remember type 10 is an allocation page and we want a data page or a PageType 1, we see that Page 153 is our candidate.  Now let’s use DBCC Page to get page 153, don’t forget to turn on Trace Flag 3604.  I’m only going to copy the relevant bits from our page dump and remember your page numbers may differ. 

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP    

Memory Dump @0x0000000008BAC060

0000000000000000:   10000c00 07000000 58585858 0500fc††††........XXXX... 

asdf

As you can see from above we get the output of fc in SQL 2005 instead of 1c like we got in SQL 2012.  If you take the Hex for 0xfc and dump it to binary you get 11111100.  When when we flip works out to 00111111.

Just to reiterate, unless you want to validate this for yourself, I stood up VM’s for 2005 RTM, 2008 RTM, 2008 R2 RTM, and SQL 2012 RTM.  Only 2005 handles unused bit’s as 1’s the rest treat them as 0’s.  The Null Bitmap was an optimization that was added in 2005, Read Paul Randal’s, Paul(@PaulRandal | Blog), blog post on that here.

Since it was introduced in 2005 then there I’m sure there was a good reason to change it in 2008 and up.  My guess is that instead of masking as columns that where just there of offset the bitmap some logic is used based on the number of columns and the bitmap to determine how much of the record remains to be read.

Whatever the case a lot of the documentation I came across regarding this all pointed to the unused bit’s being labeled as 1’s.  That through me for a loop at first and I just wanted to toss this out there to anyone else searching on the subject.

Thanks for stopping by,

Brad

SQL 2000 TIPS AND TRICKS DECK AND DEMOS

Hello Dear Reader today I'm going to be presenting on SQL 2000 Upgrade tips and tricks for Pragmatic Works Training on the T's.  The slide deck and Demo's are live on the Resource Page so please head over there to grab them and let me know what you thought!

Thanks,

Brad

Cross Database Views and Schema Binding

One of the most interesting things I've found as a DBA is learning about features in other RDBMS platforms.  Sure we all work with and love SQL Server, but quite often there are features that you hear about that you learn about that make you go, "Wow, wouldn't that be cool!".  Sybase's ability to have multiple tempdb's, create a tempdb for a particular database to use, or create a tempdb  for a specific user login come to mind.  Oracles ability to make a metadata copy of a base table without causing locking or blocking on the original is another.  But as cool as these features are sometimes they create confusion when working with the business.  Often times you need to understand the internals of how SQL Server works to explain why a particular feature for another RDBMS platform doesn't work in SQL.

CROSS DATABASE VIEWS & SCHEMA BINDING

http://www.flickr.com/photos/incrediblehow/5714219510/

One I ran into recently was Cross Database views that allow update's and inserts on the base tables, (I'm not a Sybase guy so to my Sybase friends please feel free to correct me if I'm wrong).  I was working on creating a distributed topology for two systems that are currently intertwined, that for performance reasons we are tying to separate.  Part of the plan that was proposed was to have a number of cross database views that would allow us to avoid code changes in an application for this first phase of the project.

 So when we started to discuss the actions that would take place against these views, very quickly it was discovered that we wanted to have inserts and updates used against these views.  In SQL Server in order to update a base table from a View that View must be created specifying WITH SCHEMABINDING.  The problem with the request is that the base table and the View are in two different databases.  In SQL this doesn't work, in Sybase (which we are transitioning off of) it does.

I worked up this demo to show my friends this limitation for Views and just wanted to pass it along to you Dear Reader.

/*

First Let's Create our Database

that will hold our base table

*/

IF EXISTS(SELECT name FROM sys.databases WHERE name='test1')

BEGIN

    DROP DATABASE test1

END

CREATE DATABASE test1

/*

Now let's create our base table

*/

USE test1

GO

IF EXISTS(SELECT name FROM sys.tables WHERE name='myTable1')

BEGIN

    DROP TABLE dbo.myTable1

END

CREATE TABLE myTable1(

             myID INT IDENTITY(1,1)

             ,mychar CHAR(500) NOT NULL DEFAULT 'a'

             ,CONSTRAINT pk_myid_1 PRIMARY KEY CLUSTERED(myID)

             )

/*

Let's insert some rows

into our base table

*/

DECLARE @i INT

SET @i=0

WHILE (@i<15000)

BEGIN

    INSERT INTO dbo.mytable1

    DEFAULT VALUES

   

    SET @i=@i+1

END

/*

Now let's create our second database

that will hold our view pointing to

the base table, dbo.myTable1, in our

test1 Database

*/

IF EXISTS(SELECT name FROM sys.databases WHERE name='test2')

BEGIN

    DROP DATABASE test2

END

CREATE DATABASE test2

/*

Now let's create our view

*/

USE test2

GO

IF EXISTS(SELECT name FROM sys.objects WHERE name='v_myTable1')

BEGIN

    DROP VIEW dbo.v_myTable1

END

GO

CREATE VIEW v_myTable1

AS

SELECT

    myid

    ,mychar

FROM

    test1.dbo.myTable1

GO

  

/*

Our Regular View is created successfully

and we can do a select from it and see

that data is returned successfully

*/

SELECT

    *

FROM

    dbo.v_myTable1

 Our view returns just fine.  And if the business only wanted to perform read operations against the view, this would have met our requirements just fine.  However we need to create a view that allows updates and inserts.  

   

/*

In order to make a view that can

recieve inserts and updates we

need to re-create our view

and specify WITH SCHEMABINDING

(This will fail in a cross database view)

*/

USE test2

GO

IF EXISTS(SELECT name FROM sys.objects WHERE name='v_myTable1')

BEGIN

    DROP VIEW dbo.v_myTable1

END

GO

CREATE VIEW v_myTable1

WITH SCHEMABINDING

AS

SELECT

    myid

    ,mychar

FROM

    test1.dbo.myTable1

GO

When you run this statement it fails with the following error.

Msg 4512, Level 16, State 3, Procedure v_myTable1, Line 4

Cannot schema bind view 'v_myTable1' because name 'test1.dbo.myTable1' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Schema Binding only allows for two part names.  This means that we can only schema bind within our database.  This also means that if we wanted to use an Indexed View on the Cross Database View, we could not do that either. 

IT ALL MAKES SENSE

http://www.flickr.com/photos/dach_art/7126010381/

If you think about it, a View is just a select statement standing by waiting to be executed.  When you allow Schema Binding you allow that view to be a pass through to the base table.  This means you would need to give one database ownership of objects within another database.  This is not how SQL Server works currently.  Objects are allocated within a database, in SQL 2012 this is taken a step further with Contained Database.

When looking at Indexed Views it becomes even clearer.  An Indexed View is essentially a Materialized View.  All of the data in the view instead of existing as a select statement is persisted to the physical disk.  The way the data is read for an index view is quicker because you are performing a seek or a scan against one object that is dependent upon its base table.

You couldn’t have a Materialized View dependent upon Base tables within another database.  If a database when offline or they entered redo and recovery at different points you could potentially have transactions that were at different states within different databases, slight chance but still the implications are head-ache inducing.

So the long and short of it, you cannot do a cross database view using Schema Binding in SQL Server.

Thanks again for stopping by.

Thanks,

Brad