My Top 10 SQL 2005 Features for RTM

Hello All,

This post is all about the top ten features I am looking forward to in SQL Server 2005. You may be surprised it’s not just SSIS nor is it just about reporting services.

1. SQL 2005 RDBMS: There have been some huge improvements in the core database engine. The two major areas of improvement that bring this area as number one are: Query Performance and Disaster Recovery. These two features I will use on every project. Listed below is functionality in detail that made up the number one ranking.

• Query Performance: There are two areas that have improved query performance, Covering Indexes and Partition tables. The major change is in covering indexes. They can have more than 16 columns as long as the total number of page bytes (8k) is not exceeded. This is great for fact tables with a large number of dimension keys that need to be indexed. Partition tables are the other feature that makes a difference to query performance on large tables. This feature not only allows the partitioning of data but indexes as well. Check out: SQL Server 2005: Two Little Known Features That Matter Big! and BOL: Index with Included Columns and also have a look at BOL: Designing Partitioned Tables and Indexes

• Disaster Recovery: The Backup and Recovery have been improved in this release. The best new feature is the Online restore. Allowing User access to the database once the redo logs have start. This is an awesome feature allowing the restore time to be reduced significantly. For more info check out BOL: Performing Online Restores

2. Analysis Services: This is really a tie with the database engine. The new features in AS2005 are almost boundless. The all the limitations of AS2000 have been addressed. One of the key changes is around deployment. Everything is now fully scriptable using the new Analysis Services Scripting Language (ASSL). A new query standard XMLA has been implemented which is widely accepted by front end tools. There have been huge changes in the engine room of analysis services. We no longer have to create virtual cubes to meet the need to service mixed grain cubes. Performance and large cubes have been updated too with proactive caching. Listed below is functionality in detail that made up the number two ranking.

• Deployment of Analysis Services Cubes: The new ASSL scripting allows for creation and maintenance of Analysis Services Databases and Cubes. Check out BOL: Analysis Services Scripting Language (ASSL)
  
• Performance Enhancements: Analysis services is fast becoming the reporting back end for high performance reporting. Features such as Proactive Caching, and the improvement in storage models allows users a fast query response against OLAP and Relational data sources.
  
• Analysis Enhancements: There are several key enhancements in the area of analysis. Key performance indicators (KPI), are now stored within the OLAP cube. Drill through has been enhanced and translations have been created to help developers with international users.
  
• Dimension: One of the limitations that I continue to struggle with in AS2000 is the use of member properties to extend dimension usability. Of course this has been addressed in AS2005. No longer are dimensions based on hierarchies instead they are based on attributes. In line with most reporting requirements. Hierarchies are instead based on attributes.
  
• UDM (AS2005 OLAP Cube): One of my biggest gripes with AS2000 was the inability to handle mixed grain cubes. Generally relating to the multiple fact tables within one cube requirement. Of course we could use virtual cubes to create the big super cube solving this issue. In the case of AS2005 this is no longer an issue. Through the use of measure groups we are able to handle multiple fact tables with different grains. This is further enhanced by dimensions being allowed to handle many to many relationships with fact tables.

3. Reporting Services: RS2005 is jam packed with new features that just makes it better to write reports than the last release. New controls have been enabled for multi parameter selection. Calendars controls for selecting date parameters, and of course direct printing. Listed below is functionality in detail that made up of number three ranking.

• Multi Parameters: One of the missing features in RS2000 was the ability to select multiple values in a list. This has been resolved in RS2005 with a check box list option for parameter selection.
  
• Calendar Control: One of my favourite features in this release. This is automatically enabled as long as your parameter is of a data type “Datetime”. The control will automatically appear when viewing reports.
  
• Direct Printing: The printing functionality in reporting services has been greatly enhanced. ActiveX control has been created to enable printing from within the browser.
  
• MDX Query Designer: Ever had to create a report against an OLAP cube in RS2000? It was not pretty as you had to create MDX statements manually. RS2005 provides you with a query builder for MDX.

4. Report Builder: This is the first attempt by Microsoft to enable report users to create reports from relational database sources and not just an OLAP cube. Report builder was a product called ActiveViews that was purchased by Microsoft sometime ago (See: Microsoft Adds ActiveViews to Free SQL Reporting Services Add-On). This is the first version from Microsoft and currently only supports SQL Server and Analysis Services. This was the product that I really enjoyed presenting at Tech05. It is a great product for the report users to create reports without having to understand the underlying database structure. For an overview Check out BOL: Working with Ad Hoc Reports using Report Models

Listed below is functionality in detail that made up of number four ranking.

• Report Models: The major component of the report builder is a business model that allows report users to create reports. This is the central repositiry for creating: common business calculations, Drillthrough paths and complex queries. Just like integration services and analysis services, report models are based on a data source views. You are able to hide database complexity by creating made queries for complex joins and data source calculations.
  
• Ad hoc Reporting: Once a report model been created and published. Any user can navigate the underlying RDBMS without having to join any tables. It’s a great tool for reducing the complexity of creating reports. Reports can take three forms cross tab, table, and my favourite charts. All reports are published to the report manager portal. If the report model supports Drillthrough functionality users are able to navigate the data model.
  
• Report Manager: Drillthrough functionality is one of the best things this happened to reports in reporting services. Report builder reports also support floating table headings. With scrollable headings large reports can be displayed in the browser in a greatly improved format.
  

5. Integration Services: I liked DTS, but I really love integration services. This should really have a top ten feature list of its own. Perhaps I will have to do one after this blog. The key feature I’m really interested in are around reusability of data in the data pipe. Its a great feature and one of the biggest changes. Other features include Fuzzy Lookups, Slowly Changing Dimensions Transformation, Loops, the list goes on. Listed below is functionality in detail that made up of number five ranking.

Control/Data Flow: One of the first features that most DTS developers will notice when using integration services. The control of flow allows developers to seprate workflow from transfromation tasks. Transfroming with DTS 2000 was a read once write once operation. Data flow allows you to read data in and transform multiple times(read once write mutlipule operations). This reduces disk I/O but ultimately requires more memory. This results in a huge performance gain with reduction of disk operations. Check out:BOL: Integration Services Programming

Fuzzy Lookups: Data quality affects every data warehouse project. A new transformation called fuzzy lookups is a tool to help address this problem. The functionality allows you to apply some data mining techniques to profile data that would normally fail on load.

Slowly Changing Dimensions Transformation: OK why did I not rank this features number one. By far this is one of the cooler features in integration services. This transformation allows you to handle type I and type II slowly changing dimensions. This is one of the things I’ve had to create my own architecture in SQL server 2000 to handle. Check out :BOL: Slowly Changing Dimension Transformation

Loops: What can I say all of us have wanted to do loops with DTS 2000, it is now standard feature with an integration services. The best example of this is looping through a set of text files and loading them with one data transformation.

6. Data Mining: Seven Data mining Algorithms! OK hands up who has done data mining with AS2000? I myself have only done it on three projects. The biggest limitation with data mining in 2000 was a lack of a work bench to test your mining hypothesis. Then if you want to display the results you had a limited number of display options. Needless to say data mining has been greatly improved in this release. Microsoft research has continued their quest in the development of new algorithms for us to use. The two algorithms provided in SQL server 2000 have been greatly improved. If the statue with data mining to have this tutorial for books online: BOL: Creating a Forecasting Mining Model Structure (Data Mining Tutorial)

7. T-SQL: New SQL functions - finally I can rank. T-SQL has been approved with inclusion of new features such as CLR support, and new text Data types. Check out: BOL: Transact-SQL Data Types

• Rank(): I can honestly say I have need something like this for a long time. With this function I can rank my results with a rank number. I can even rank with a partition of the result set. Check this out if you use Reporting Services and need to rank the results I bet you will use this function. See: BOL: RANK (Transact-SQL)
• Replacement text data types: The search limitations of using text and ntext was handled by using the full text search engine. Two new data types have been created varchar(Max) and nvarchar(Max). The new data types can be used to in the where clause is standard conditions.
• XML data type: XML is now treated with its own data type. The new data type fully supports XQuery. See: XQuery Against the xml Data Type

8. Database Tunning Advisor: Now we can tune the whole database. No really the whole database. DTA can be used in a similar way to the index tuning wizard only this time it looks at the entire database right down to the file groups and database table structure. It is well worth a look if you are trying to understand some of the new performance enhancements SQL Server 2005 has to offer. To get started with the database tuning adviser, check out:BOL: Database Engine Tuning Advisor Tutorial

9. User Interfaces: Ok it’s a bit lame but the complete refresh of the UI was required. And this time someone listen to us developers and created an integrated workbench for the development of business intelligence projects. The old user interfaces have been replaced by two new applications, the SQL server management studio and business intelligence development studio.

• SQL Server Management Studio: a cross between the old enterprise manager and query analyzer. To it as a single point for managing all server instances this includes Reporting Services, Analysis Services and Integration Services. Management studio is also the main query engine for SQL server.
  
• Business Intelligence Development Studio: fully integrated into visual studio 2005, developers are able to create end to end business intelligence solutions. This a single point for developing integrated services, reporting services, and analysis services projects.

10. Upgrade Advisor: Need to upgrade? Check out your current SQL server 2000 instance before installation. The first step should be the upgrade adviser. This can be found on the installation CD and is not installed by default and can be run independently of SQL server 2005 applications. It requires .Net framework version 2.0. You may wish to migrate instead of upgrading; SQL Server 2005 can be run as a side by side installation with 2000.

To download the CTP version of Upgrade Advisor check out: Upgrade Advisor - Community Technology Preview (CTP) September 2005

This list is based on the September CTP so it’s missing DB Mirroring which was in my top ten. I haven’t begun to talk about the new BI products such as the new scorecard accelerator that will have to be in another post.

What are you’re top ten features you’re looking forward too in SQL 2005?
Don’t be shy feel free to leave a comment as I have enabled anonymous user comments.