Blog ala Vidar

You’ve probably seen this in a list of features in SQL 2008, but what exactly is it and how can you take advantage of it? A sparse column just means that it will not store NULL. When you have a NULLable field, NULLs are actually stored in normal columns. It doesn’t use much space, but still. So, why is this a problem? Well, if you have an index on a NULLable field, the index will have as many NULLs as the field and therefore not be particularly selectable. In some tables, like the Objects table in CMS there are MANY NULLable fields, which on most records are set to NULL.

A sparse column is stored as XML, so you only want to add this to fields that have at least 50% NULLs, since there’s an overhead for each field that has data. For example, an integer field non sparse uses 4 bytes. The same field with sparse, uses 8 bytes, but for non-NULL data. Let’s do a little experiment.

CREATE TABLE test1 (
	Field1 INT,
	Field2 INT SPARSE NULL,
	Field3 INT SPARSE NULL
)

CREATE TABLE test2 (
	Field1 INT,
	Field2 INT NULL,
	Field3 INT NULL
)

INSERT INTO test1
  (Field1, Field2, Field3)
SELECT TOP 100000
  ROW_NUMBER() OVER (ORDER BY C.id), NULL, NULL
	FROM sys.syscomments AS C
	CROSS JOIN sys.syscolumns

INSERT INTO test2
  (Field1, Field2, Field3)
SELECT TOP 100000
  ROW_NUMBER() OVER (ORDER BY C.id), NULL, NULL
	FROM sys.syscomments AS C
	CROSS JOIN sys.syscolumns

EXEC sp_spaceused 'test1'
EXEC sp_spaceused 'test2'

We create two tables, almost identical. The only thing different between these are that Field2 and 3 are SPARSE in test1. Then we populate both tables with 100000 records, and set NULL for Field2 and 3. This is the result I got from sp_spaceused:

This tells me that for 100.000 records, two NULLable fields will all saves me (about) 1 MB with data when using sparse. Doesn’t sound much, but if you compare it to the one without sparse it’s almost 50%. And that’s with only two NULLable fields and no indexes. In one of our customers CMS database there’s over 300.000 records in their Objects table. This table has 55 NULLable fields. Doing a SELECT * on this tables, I see that most of the NULLable fields are actually NULL more than 80% of the time. Five of them are indexed too.

So, what’s the downside of sparse columns? Well, for starters it uses the double amount of space to store data, when it’s not NULL. You can not use text, ntext, geometry and geography, you can’t have default values on them. It is also not supported by merge replication. But it’s supported by AppFrame R3, so start using it!

One thing I forgot to mention is column sets. If you add a column set to your table with sparse columns, the sparse columns will be stored in this column (remember, it’s all just XML). Keep in mind that using column sets have more restrictions. For instance, you can’t change a column set. If you have a sparse columns in your table and you want to add a column set, you need to re-create the table.

One thing that’s “cool” about column sets though is that when you select * you get the column set (with your sparse columns) as an XML:

CREATE TABLE test3 (
	Field1 INT,
	Field2 INT SPARSE NULL,
	Field3 INT SPARSE NULL,
	MySparseFields XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
)

INSERT INTO test3
  (Field1, Field2, Field3)
VALUES
  (1, 1, 1),
  (2, NULL, NULL),
  (3, 3, 3),
  (4, NULL, NULL)

SELECT *, Field3
	FROM test3
	WHERE Field2 = 1

The result:

If you think this is something you should use, read more about it here:

My favorite bloggers at SQL Skills:

• Bob Beauchemin
• Kimberly L. Tripp
• Paul Randal

MSDN:

Using Sparse Columns

Using Column Sets

I’ve been asked by a couple of people this question. Microsoft released SP1 a couple of weeks ago, but what’s the difference between 2005 and 2008, and when is the next version coming? The next version of SQL Server is scheduled for 2011 (NB! This is a rumor!!!). That’s two years. Then SQL Server 2005 will be… 6 years old. Anyhow, here’s a list of nice features that you don’t got in 2005:

For developers:

• New column types – Date, Time, Datetimeoffset, Datetime2
• MERGE statement
• Sparse Columns – Performs very good compared to large tables with many NULL-able columns
• Spatial Data types – Geometry etc
• Transact-SQL IntelliSense
• Table Valued Parameters
• Grouping sets

For DBAs:

• Easier to maintain multiple servers
• Transparent Data Encryption – Encrypt the physical database files without any application changes
• Auditing
• Resource Governor – ability to set "maximum CPU/memory usage" on heavy queries, users etc
• Data Compression – Reduce the storage requirements of both database and backups
• Performance Data Collection – Makes it easy to find issues and bad performing queries
• Integrated Full Text Search
• Policy management
• Database Mirroring – Improved compared to 2005 with automatic page recovery of corrupted pages
• Hot Add CPU

Looking for management studio? Here’s the direct link: http://download.microsoft.com/download/7/9/4/794bfafa-aea7-45d4-a6ea-4e92f09918e3/SQLEXPRWT_x86_ENU.exe
This includes both the server (express edition) and management studio. In 2008 management studio has the management node on the server, and is also compatible with SQL Server Agent 🙂

Many of you probably noticed TeamDoc had some issues today. It was my fault, but before you shoot me, let me explain what happen, so it won’t happen to you! You’ve maybe noticed when doing changes to big tables, management studio goes to timeout, because it takes too long. The work around for me has always been to press the “Generate Change Script”, copy the script to a new query window, and execute it. Since there’s no timeout set on query windows, this will execute till it’s finished. And in ALL cases this has worked, until today.

We found a bug in TeamDoc, which was related to the Folder field in the Documents-table allowing NULL. We were filtering WHERE Folder + ‘ ‘ + Title LIKE ‘%’ + @SearchText + ‘%’. When Folder is NULL, this doesn’t work. So, instead of changing all SPs to handle NULL-values, we decided to change the field in the table, to not allow NULL, but have (‘’) as default value (because we just changed it to allow NULL). Since I’ve changed the design of this table about 1000 times before, I know it will go to timeout, so I automatically pressed the “Generate Change Script” button, copied the script to a new window, and executed it. Guess what happened! YEAH! Errors.

What happens when you change a field like this is that it creates a new table (tmp_your_table_name), puts the records in this table, then it recreates the original table (DROP, CREATE), then it inserts the records back from tmp_your_table_name, and puts on all indexes etc. The boring thing is that when inserting into tmp_your_table_name it’s not that cleaver that it puts a ISNULL around the Folder-field. And what happens? Well, it doesn’t insert any records, then it drops the original table, and creates it again. What about transactions? Well, it creates transactions inside this script, but not AROUND the script, so there’s no “master” transaction. Therefore, the data of the Documents-table was lost. JIPPI!

We quickly ran in to JanCB to restore a backup, to get the records out as soon as possible. We had a backup from about 09:00, and a log from about 12:00. But, before 12:00 I did some tests (setting the initial size of MDF and LDF files), and took some backups to test if it had any influence on the .bak file. Btw, there were no change to the size of the bak-file. Then we remembered that the old server was still running, so we picked the records of the Documents-table from this. Then we’re only missing the TeamDocs created from Sunday night, till now. There were only 3, so we created them manually by inserting based on the inputs table.

If I hadn’t done any tests, including backups, we could have restored the backup from 09:00, then the log from 12:00 and then a transaction log to a point-in-time, right before I did the change, and picked the data from that database. That would have taken a lot more time though…

ANYHOW. Everything is up and running, and there shouldn’t be anything missing. Sorry for making TeamDocs unavailable 🙂

NB! We found one very nice feature in 2008. I was aware of this, but not that it was THAT efficient! Backup compression! Without compression, it takes about 30 minutes to create the 89GB bak file. With compression, it takes 4 minutes, and the bak file isn’t more than 34GB! That’s impressive!

After you’ve installed 2008, there’s two issues annoying my shiny metal ass:
1.) Shortcuts doesn’t work properly. CTRL + N normally opens a new window, but not anymore! To fix this, go to Tools -> Options -> Environment -> Keyboard and click “Reset to Default”. Now it works 🙂 You should think that you get default when installing, but no…
2.) For (stupid) security reasons, management studio doesn’t allow you to do changes to a table that makes it rebuild the table. Since we always have PrimKey, Created, CreateBy etc at the end of the table, when you add a field it has to be before the PrimKey-field, and therefore you need to rebuild your table. To fix this issue, go to Tools -> Options -> Table and Database Designers and uncheck “Prevent saving changes that require table re-creation”.

Both issues is already reported to Microsoft. The first one was not intentional, but the second one was “by design”. I’ve voted that this should NOT be by design. Let’s hope they’ll listen to me this time!