Page & Row Compression Vs. Backwards Compatibility

"My Money is on Compression"

Previously on SQLBalls we discussed Backwards Compatibility and why you would want to use it, Transparent Data Encryption and how you can use that when in a Database Compatibility Level of 80 or 90.  We had also discussed Why you would want to use Page and Row Compression, when I stated my case for SQL Rally .

Now we find Page & Row Compression and Database Compatibility Level all meeting for the first time.

So Dear Reader, let’s dive right in!

PHENOMENAL COSMIC POWERS!!!! ITTY BITTY LIVING SPACE.

I wanted to create a database in SQL 2005, write out  my compression scripts see them fail, because we didn’t have compression in SQL 2005, backup the database restore it on 2008 R2, and then show that while in 90 mode the data still compresses.

 I connect to my SQL 2005 Instance, which needs to be upgraded to SP 4.  And then I run a script to create my Database, my Table, and populate the data.

IF EXISTS(SELECT * FROM SYS.databases WHERE name='compatAndCompression')

BEGIN

    DROP DATABASE compatAndCompression

END

CREATE DATABASE compatAndCompression

GO

USE compatAndCompression

GO

CREATE TABLE DBO.HEAP1(myID int IDENTITY(1,1), myData char(8000) DEFAULT 'A')

GO

DECLARE @i int

SET @i = 0

while (@i<1000)

BEGIN

    INSERT INTO DBO.HEAP1 DEFAULT VALUES

    SET @i = @i + 1

END

GO

So far everything’s fine.  Now let’s try to compress it.

sp_spaceused 'heap1'

GO

ALTER TABLE dbo.heap1 REBUILD

WITH (DATA_COMPRESSION=PAGE)

GO

sp_spaceused 'heap1'

GO

Error that is what we should expect, as 2005 didn’t have compression.

Now let’s backup our database and Connect to our 2008R2 Instance.

Now let’s restore our database.  Look at the restore section, you’ll see that the Database Internal Version is incremented.  This is an important distinction because your SQL Database do not have ONE version number (Database Compatibility Level), they have TWO.

The DBI version is incremented when you restore a database from one Version to another.  This is why if you take an 80 Compatible Database from 2008 R2, and back it up you cannot restore it to SQL 2000.  Because you cannot roll back the Internal DBI Version, for 2005 to RTM 2008 R2 that mean our DBI version will go from 611 to 661.  Let’s do a quick check to validate our database is still in 90.

And it is, so now let’s apply our Compression Script.  We’ll be using Page Compression to get the most bang for our buck.

sp_spaceused 'heap1'

GO

ALTER TABLE dbo.heap1 REBUILD

WITH (DATA_COMPRESSION=PAGE)

GO

sp_spaceused 'heap1'

GO

I wrapped this around an sp_spaceused so that way we can see the size before and after.  And behold our 8 MB table is now in the 200 KB range.

"So Ball's", you say, "Great it works on a Database that is imported from SQL 2005, are you sure it will work on a Database imported from 2000?"

Absolutely Dear Reader, time for a little Yogi Berraism, "Once Your In, Your In".  In short it doesn't matter what version you came from.  SQL Server only supports the Database Compatibility Level of the previous 2 versions.  So as long as it is an 80 or 90 compatible database, your okay.

But this would be the perfect opportunity to do the whole thing again with 2000!  So I'll skip to the good stuff, I created a SQL 2000 Database and inserted data, and then backed it up.  I'm moved it to my XP box that has a 2008 R2 Instance on it and restored it.

I highlighted the part where you see the upgrade of the DBI Version.  SQL 2000 is set to 539, and as part of the restore process the DBI version is upgraded when coming from an earlier version.  So we've got the database, let's confirm that it is still in 80 Compatibility.

And it is, so let's take our script from earlier and apply it to our heap.

sp_spaceused 'heap1'

GO

ALTER TABLE dbo.heap1 REBUILD

WITH (DATA_COMPRESSION=PAGE)

GO

sp_spaceused 'heap1'

GO

And as you can see our table has shrunk once again.

So Dear Reader you can use Transparent Data Encryption and Page & Row Compression with a Database Compatibility Level set to 90 or 80.

Join me next time when we take on Snapshots!

Until then MAKE MINE SQL!

Thanks,

Brad

Backwards Compatibility, Your Database is so Retro

I think there are a lot of misconceptions when it come to Backwards Compatibility, also known as Database Compatibility Level.

“Balls”, you say, “What is Database Compatibility Level?”

Great Question Dear Reader, man your smart!

GONNA GO BACK IN TIME!

Database Compatibility is a setting on a Database that tells SQL Server to use a particular set of Rules, when judging SQL Statements on a syntactical level.  Each version of SQL has three levels of Backwards Compatibility.  If you look at the versions of SQL over the years each has a number associated with it.  In SQL 2008 and SQL 2008 R2 the Database Compatibility levels available are 80, 90, and 100.  80 is equivalent to SQL 2000, 90 is 2005, and 100 is 2008 & 2008 R2.

 The Compatibility level is by default the most current version, UNLESS you set the Model Database to have a Compatibility level that is less than the current version.  But unless you do that, a SQL 2000 database should be in 80, 2005 should be in 90, and SQL 2008 & 2008 R2 should be in 100.

One of the simplest comparisons would to that of an emulator, by that carries some misconceptions.  I had heard previously that if you used a database version that was other that the current release you where working in then you couldn’t utilized the Advanced Features that were used in that release.  Well Dear Reader, as I’m finding out that is just flat out wrong. 

FAT, DRUNK, AND BACKWARDS COMPATIBLE IS NO WAY TO GO THROUGH LIFE

The better comparison would be to College.  One thing I’ve never forgotten is that when you enter College, you get a college catalogue.  In it the requirements are outlined for all of the different Disciplines that you could get a degree for, and what those requirements are.  Those requirements can change over time, but will stay the same for you as those laid out in your college catalogue.

“Balls”, you say, “Why would I use Database Compatibility Level or change my database to another?”

Well Dear Reader, under most circumstances you wouldn’t.  If your upgrading from SQL 2000 to 2005 one step you would take is to change your Compatibility Level from 80 to 90.  This will change the syntax rules for your database, the same way the next year of new freshman will get a new college catalogue when they start school.

If you wanted to run at a different Compatibility the reason to do so is to buy your Developers some time.  Let’s say the application has some syntax that needs to change from 2000 to 2005, or from 2005 to 2008.  How do you find that and fix that?

You can use the Upgrade Advisor, and that will work perfectly well, as long as all of your queries are in Stored Procedures.  In Perfmon you can use the Deprecated Features SQL Counter and in Profiler you can track statements by Deprecated Features to try to find Statements that contain language that is not supported in a future release, this was introduced in SQL 2008 so you cannot use this if you’re on 2005 or 2008.  There are some free 3^rd party tools that will replay traces and identify statements, but this could be a little hit or miss, and there is no way to guarantee that there are statements in an application that didn’t run and are still out there.

Depending on the load of work the business has for the Developers, this could be a great reason, to run for a brief time in a lower Database Compatibility Level.  You are able to accomplish a Database Instance Upgrade, and you can show a little Developer solidarity by giving them some extra time.  Keep in mind these features where deprecated for a reason, if you have the time in your project you should plan on altering the Compatibility Level in conjunction with the Database upgrade.

DON’T TOSS THE BABY

So the big misconception is that you have to use the previous versions Advanced Features when you use a lower Compatibility Mode.  I call shenanigans get your broom!  You don’t have to toss the Baby out with the Bathwater.  The Baby’s just fine, leave them alone.

Last week I wrote about how you can use Transparent Data Encryption in a 2008 R2 instance when the Database Compatibility Level is set to 80.  I’ve got more coming this week, but I wanted to talk about what Backwards Compatibility AKA Database Compatibility Level is and why you would use it.

Keep in mind Dear Reader, that when you look at the Database Engine it is split between Relational & Storage.  The Relational Engine handles Syntax, Query Plans, Optimization, and many other things related to how we ask for a query and the path we take to return it.  The Storage Engine not only retrieves the data, but it manages how the data is stored, it’s physical structure, placing data into cache, compression, Transparent Data Encryption, Snapshots, Backups, Mirroring, Backup Compression, and more.

Last week, Paul Randal (blog|@PaulRandal) had answered a quick question for me, about what internal Storage Engine component handled Transparent Data Encryption, to find out go look through last week’s SQL Skills (website| @SQLSkills) twitter contests, the ones on 4/27 in particular.   Needless to say a light bulb went off.

So next up Dear Reader we will tackle the myth that you cannot use Page & Row Compression on an 80 or 90 Database.  One last bit of resources if you'd like to read more about Database Compatibility Level go to MSDN and read the article on it.

Thanks,

Brad