Haven't yet upgraded to SQL Server 2005?

SQL Server 2005 Enterprise Edition provides something for nearly everyone. SQL Programmers
have a more powerful version of the Transact-SQL (T-SQL) language, plus the ability to write certain functions using any .NET language. Business analysts have a more robust set of tools (SQL Analysis Services) for analyzing data. Developers have a new Integration Service (SSIS) that replaces DTS for integrating information from other data sources. Finally, developers/power users have a new version of a reporting tool for working against data. If you're still debating on whether to upgrade to SQL Server 2005, this article will provide a detailed list of the capabilities of SQL Server 2005 to help you decide.

SQL Server 2005 Language Enhancements

Microsoft hit a home run with new SQL language enhancements to improve developer productivity. Some enhancements are based on customer requests, and others increase Microsoft's compliance with the ANSI SQL-99 standard. As a speaker, one of my most popular sessions over the past year was, "T-SQL 2005 for Application Developers." I presented code samples from "real world" situations, how I handled them in SQL 2000, and how I could handle them more easily in SQL 2005. (You can find the code samples on my Website, at ww.CommonGroundSolutions.net/TSQL2005.ZIP.) Here are the new language enhancements:

• A PIVOT statement to convert rows of data into analytical views.
  
• A new variable TOP N feature so that developers don't need to resort to Dynamic SQL.
  
• A new APPLY operator, to more easily integrate table-valued userdefined functions into queries.
  
• New OUTPUT and OUTPUT INTO statements that provide access to the SQL Server Inserted and Deleted system tables.
  
• New RANKING functions to assign sequential ranking values to result sets.
  
• New INTERSECT and EXCEPT statements as cleaner alternatives to existing IN/NOT IN syntax.
  
• A new Isolation Level that takes a Snapshot of data so that developers can query committed data without using shared locks - or wait for locks to clear.
  
• Improved ERROR HANDLING, so that developers can implement nested TRY...CATCH logic, and raise exceptions back to the calling application - all resulting in a more robust error-handling
  environment.
  
• An entire set of XML language enhancements and functions to more easily work with XML data. This includes new XML query language called XQUERY, so that developers can query XML data.
  
• New Common Table Expression and Recursive Query capabilities, which provide incredible
  power for querying hierarchical data.
  

CLR/.NET Framework Integration

Prior to SQL 2005, database developers sometimes coveted the language features of C# and VB.NET when performing certain CPU intensive calculations. For example, a programmer writing a special
database function to compute commission rates might prefer the object-oriented constructs of .NET languages and the rich functionality of the .NET framework, instead of the using the procedural nature
of the T-SQL language. SQL Server 2005 now makes this possible through integration with the .NET Framework and .NET Common Language Runtime. The new architecture allows developers to write
stored procedures, triggers, and user-defined functions in the .NET language of their choice.

Analysis Services

Microsoft completely rebuilt Analysis Services to provide more powerful tools for data warehousing, data mining and on-line analytical processing (OLAP). Here are some of the major new features in SSAS 2005:

• Developers can now create Business Intelligence Solutions with Visual Studio, using new templates to organize data sources, OLAP cubes and dimensions.
  
• SSAS 2005 utilizes and supports the Universal Dimensional Model (UDM), which simplifies working with data sources.
  
• There are new Multidimensional Expression (MDX) functionality and language features in SSAS 2005.
  
• SSAS 2005 provides support for Failover clustering (four-node clustering for 32-bit systems, and eight-node clustering for 64-bit systems).
  
• Users have the ability to create Key Performance Indicators (KPIs) to quantify business objectives.
  
• There is full integration with SQL Server Integration Services (SSIS).
  

Integration Services

Microsoft has transformed Data Transformation Services (DTS) from a service into a full-blown platform known as SQL Server Integration Services, which integrates data from different sources. Here is a partial list of new key features in SSIS 2005:

• Graphical tools and wizards for creating, debugging, and deploying SSIS 2005 packages.
  
• SSIS Programmer APIs, and the ability to create SSIS packages in Visual Studio 2005.
  
• More powerful options for aggregating, merging and copying data from different sources.
  
• An email messaging interface.
  

SQL 2005 Reporting Services

Microsoft has added several new enhancements to Reporting Services to improve the process of building and deploying reports. These enhancements include:

• New Windows Forms and Web Forms Report Viewer controls for Visual Studio 2005, and a .NET programming interface.
  
• The ability for reports to query against XML Web services.
  
• The ability to use Analysis Service queries.
  
• Packaging of Reporting Services with SQL 2005 Express.
  

Additional features:

SQL Server 2005 contains several other important enhancements:

A new security model for DBAs to manage permissions at a granular level. SQL Server 2005 contains a new database object ownership model, which separates the owners and the schemas in the SQL Server administration space. Additionally, SQL Server 2005 contains built-in encryption capabilities and can encrypt client/server communications.

• 64-bit and Itanium 2 and x64 support.
  
• A new Query Notification Service that notifies .NET code when any DML operations are performed.
  
• Support for Multiple Active Result Sets (MARS) so that .NET developers can use multiple readers on a single database connection object.
  
• The ability to expose SQL Server stored procedures and user-defined functions over HTTP (or secure HTTP), by setting up native SQL Server Web services.
  

About the author:

Kevin S. Goff is a software developer / consultant, as well as a professional writer, speaker and trainer. He has been a Microsoft .NET MVP since 2005. He is a the author of Pro VS 2005 Reporting using SQL Server and Crystal Reports, published by Apress. Kevin is currently working on a second book. He can be reached at info@computertrainingmag.com