Vardecimal Storage Format and its implications on Backup/Recovery

Has any one tried restoring or attaching a SQL Server 2005 database on SQL Server 2000? You will find that SQL Server 2000 will fail this restore or attach. The reason is simple. SQL Server 2000 does not understand the physical structure changes in SQL Server 2005 database. SQL Server detects this incompatibility using the… Read more

More undocumented fun: DBCC IND, DBCC PAGE, and off-row columns

(Final blog post of the year for me. Its been a bit of a wild ride the last 6 months – 7 TechEds on 3 continents, 46 blog posts and some major life changes – but now things have calmed down and I should be back to more regular posting in 2007. Tomorrow I fly out to… Read more

So much enthusiasm for SQL Server at TechEd!! (and a teaser for something I’m working on…)

TechEd in Barcelona is going really well. All the DAT sessions last week at the Developers conference were packed – I was particularly pleased that the chalk-talk I did on the internals of query execution in the Storage Engine was so popular that I had to repeat it. That’s a great motivator for me to continue posting… Read more

Enabling vardecimal storage format

First, this feature is only available in EE and Dev SKU.   Enabling vardecimal storage format on a table is a two step process as follows:   First you need to enable database for Vardecimal storage format. This can be done using the stored procedure sp_db_vardecimal_storage_format. The exact command is as follows   exec sp_db_vardecimal_storage_format ‘<dbname>’,… Read more

How to give feedback on SQL Server 2005?

One of the sessions Don Vilen did last week at TechEd:Developers in Barcelona (that I helped with) was called The Doctor Is In: Tell Us Where It Hurts. It’s basically a feedback gathering session for us and a way for customers to be able to discuss issues directly with a people from the SQL Product… Read more

Estimating the space savings with vardecimal storage format

Before enabling Vardecimal storage format, you may want to know the potential reduction in the size of the table. Clearly, if the table has no decimal/numeric columns, there will be no savings. Note, that even if you have a table with decimal/numeric column types, there is no guarantee that you will be able to reduce… Read more

Reducing the Size of your Database in SQL Server 2005/SP2

An exciting new feature in SQL Server 2005/SP2 is Vardecimal Storage Format. This storage format lets you reduce the size of your table significantly if the table has one of more columns of type decimal or numeric without requiring any changes to your application.   Up until now, the decimal and numeric types are stored… Read more

SQL Server 2005 SP2 CTP2 released

We’ve released the second CTP (Community Technology Preview) of the SP2 for SQL Server 2005. All available links, docs, support instructions and info can be found at This release also includes the first look at SQL Express supported on Vista – see Mike Wachal’s SQL Express blog for more details. Please download it, play with… Read more

When can allocation order scans be used?

I know this is jumping the gun a little as I haven’t made it this far in my series on fragmentation, but this came up in a chalk-talk I did yesterday at TechEd Developers in Barcelona and is worth blogging about. You’d expect a select * from mytable query on a table with a clustered… Read more

CHECKDB (Part 6): Consistency checking options for a VLDB

(Yippee – just finished my certification dives and got my PADI Open Water certification – just in time for our dive trip to Indonesia in December 🙂  This is a question that comes up a lot – in fact 3 times this week already – most recently during a guest lecture I did on DBCC… Read more