Menu
Deep dive into SQL Server 2008

Deep dive into SQL Server 2008

Microsoft's 'Katmai' is packed with performance and management features enterprises will love, and even small shops will have reasons to make the upgrade

Nonlogged inserts allow SQL Server to do minimal logging for large inserts. By not logging every data row inserted, you can speed up your data loads by orders of magnitude in some cases. At the very least, you will see significant performance gains.

You could do nonlogged inserts in previous versions of SQL Server using what's known as a "select into" statement, but "select into" actually creates the table for you. This is great for new tables, but if you have existing tables with security and other attributes defined, you don't want to delete and re-create them. Another problem with "select into" is that you have no control where your new table goes; it always joins the default file group.

The new nonlogged insert allows you to insert the rows into an existing table, maintaining the control you have over your space, performance, and security, and still get the benefit of an ultra-fast data load. The general rule of thumb I like to follow for warehouses (and any database, come to think of it) is no moving schema. That means you don't want to delete and re-create permanent objects every day. It's error prone and introduces complexity into your system you don't need, not to mention your tables aren't available during the load because they've been deleted.

In my testing, non-logged inserts performed right on par with their "select into" cousins. As the DBA of a large warehouse that faces data loading problems every day, I love this feature. There's just one thing I would change about it: I wish there were an option I could tack onto my insert statement to throw it into non-logged mode.

Data Compression

SQL Server's first attempt at Data Compression actually looks pretty good. As I noted in my beta preview (see "Microsoft's 'Katmai' is filled to the brim"), SQL Server 2008 provides two types of compression: row and page. Row compression is true compression, in which unused spaces at the ends of columns are removed to save storage. Page compression, aka dictionary compression, normalizes the data on each page and keeps a lookup pointer. In SQL Server 2008, page compression includes row compression. If you have page compression turned on, you get row compression in the bargain.

Microsoft provides a handy compression calculation wizard that will give you a good estimate of the benefits you can expect. The wizard runs a test compression scenario against your data for each compression type (row and page) and tells you what the new size of the table should be. I tested the compression calculator against a number of data sets, and on average the calculation deviated from my final results by only 1 or 2 percent. That's pretty good, considering that the calculation is based on a relatively small amount of data.


Follow Us

Join the newsletter!

Or

Sign up to gain exclusive access to email subscriptions, event invitations, competitions, giveaways, and much more.

Membership is free, and your security and privacy remain protected. View our privacy policy before signing up.

Error: Please check your email address.

Tags databasessql server 2008software applications

Brand Post

How to become the best IT MSP

This article provides guidance for managed service providers (MSPs) that want to grow their business. It is also useful for any IT service provider looking to move from the break-fix model to managed IT services.

Featured

Slideshows

Reseller News Platinum Club celebrates leading partners in 2019

Reseller News Platinum Club celebrates leading partners in 2019

The leading players of the New Zealand channel came together to celebrate a year of achievement at the annual Reseller News Platinum Club lunch in Auckland. Following the Reseller News Innovation Awards, Platinum Club provides a platform to showcase the top performing partners and start-ups of the past 12 months.

Reseller News Platinum Club celebrates leading partners in 2019
Reseller News hosts alumnae breakfast for Women in ICT Awards

Reseller News hosts alumnae breakfast for Women in ICT Awards

Reseller News hosted its second annual alumnae breakfast for the Women in ICT Awards in New Zealand, designed to showcase the leading female leaders in the industry. Held at The Cordis in Auckland, attendees came together to hear inspiring keynotes and panel discussions, alongside high-level networking among peers. Photos by Gino Demeer.

Reseller News hosts alumnae breakfast for Women in ICT Awards
Reseller News Innovation Awards 2019: meet the winners

Reseller News Innovation Awards 2019: meet the winners

Reseller News honoured the standout players of the New Zealand channel in front of more than 480 technology leaders in Auckland on 23 October, recognising the achievements of top partners, emerging entrants and innovative start-ups.

Reseller News Innovation Awards 2019: meet the winners
Show Comments