 |
| "My Money is on Compression" |
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.
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
