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

Another good use of filtered indexes might be if you have, say, ten years of data in a table but nobody ever queries further than two years back. You could create a filtered index for just those two years and get the same benefits as in the first scenario.

SQL Server Reporting Services

SSRS (SQL Server Reporting Services) has received a major overhaul. For starters, it no longer relies on IIS for its report server capabilities. This is a huge leap forward for SSRS shops because as I've found many times, it can be difficult to get approval for Windows changes, such as installing IIS. Often, these shops don't like to make major changes to a Windows install, and adding a component such as IIS, which is perceived to be difficult to secure, makes them doubly hesitant; not requiring IIS is a good thing.

Another major enhancement, called Tablix, is a new data element type that combines table, matrix, and list. Tablix gives you an easy way to work with grouping by allowing you to group on columns or rows as well as define adjacent or parent/child groups. All of this sounds fairly intuitive, but it does take a little training. I quickly found that building groups the way I did in the previous version left me out in the cold. I had to slightly change the way I authored reports to be successful.

Report Builder has come a long way as well. Microsoft has removed a number of limitations that could make it difficult to work with. Now, you can do almost anything in Report Builder that you can do in Visual Studio, and Report Builder comes with wizards that make formatting data and charts automatic. I have almost no reason to write reports in Visual Studio anymore. This new version of Report Builder (2.0) wasn't available when Katmai shipped, but it is available as a separate download now.

SQL Server Integration Services

Microsoft has taken some big steps to ensure that SSIS (SQL Server Integration Services) can compete directly with competitors such as Informatica by offering lookup caching and CDC. And while CDC isn't strictly an SSIS enhancement, SSIS does take advantage of it to increase the speed of ETL processes. In fact, both lookup caching and CDC can improve the speed of loads by orders of magnitude. Let's examine a couple situations where you'll see dramatic performance gains.

Perhaps you're loading customer data, and you have to load each customer one at a time because you have to look up other information and make load decisions based on that lookup data. In SQL Server 2005, you would put this load inside a "for each" loop container and perform the lookup on each customer as you iterate through the loop. This means that you would perform the lookup itself for every customer. Well, if you have a large lookup table -- say, something in the neighborhood of 500 million rows -- you would have to query that data for every row you import. And if the lookup table is not on the same system or if it's not indexed appropriately for your lookup, those lookups could take a lot of time.

Now, in SQL Server 2008, you can perform that lookup once for all the customers and cache the data in a local file. The upshot is that iterating through the loop will go much faster.

Another scenario where SSIS improvements come into play is in handling changed data. Let's say a customer address change needs to be reflected in the database you're loading. In SQL Server 2005, you have to perform a binary or text comparison, on a row-by-row basis, for each column you want to track. This means that the larger your customer table, the longer your lookup will be. But with CDC, you can easily see which rows and columns have changed and avoid the lookup altogether. Here again, you could reduce the time to load tremendously.

There are other enhancements in SSIS as well. Improved threading in the data pipeline will increase performance by better managing the resources and throttling the pipeline appropriately. There's also a new data-profiling task that makes it easier to identify any data purity issues. Finally, SSIS scripts can now be written in both VB and C#.

Follow Us

Join the newsletter!


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



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