What’s New in SQL Server 2005

1. What’s New in SQL Server 2005 From a Developer’s Perspective

2. SQL Server 2005 Versions • Enterprise Edition • Standard Edition • Workgroup Edition • Developer Edition • Express Edition • Mobile Edition • Everywhere Edition (CTP) ALSO AVAILABLE: • SQL Server 2005 Service Pack 1 • SQL Server 2005 Feature Pack

3. What’s new in SQL Server 2005 Database Engine • Service Broker • HTTP Access • Database Tuning Advisor • Enhanced Read ahead & scan • Indexes with Included Columns • Multiple Active Result Sets • Persisted Computed Columns • Try/Catch in T-SQL statements • Common Table Expressions • Server Events • Snapshot Isolation Level • Partitioning • Synonyms • Dynamic Management Views .NET Framework • Common Language Runtime Integration • CLR-based Types, Functions, & Triggers • SQL Server .NET Data Provider Data Types • CLR-based Data Types • VARCHAR(MAX), VARBINARY(MAX) • XML Datatype Database Failure and Redundancy • Fail-over Clustering (up to 8 node) • Database Mirroring • Database Snapshots • Enhanced Multi-instance Support XML • New XML data type • XML Indexes • XQUERY Support • XML Schema (XSD) support • FOR XML PATH • XML Data Manipulation Language • SQLXML 4.0 Database Maintenance • Backup and Restore Enhancements • Checksum Integrity Checks • Dedicated Administrator Connection • Dynamic Configuration AWE • Highly-available Upgrade • Online Index Operations • Online Restore Management Tools • MDX & XML/A Query Editor • Maintenance Plan Designer • Source Control Support • Profiler access to non-sa • SQLCMD Command Line Tool • Database Mail Performance Tuning • Profiling Analysis Services • Exportable Showplan & Deadlocks • Profiler Enhancements • New Trace Events Full-text Search • Backup/Restore includes FT catalogs • Multi-instance service SQL Client .NET Data Provider • Server Cursor Support • Multiple Active Result Sets Security • Catalog and meta-data security • Password policy enforcement • Fine Grain Administration Rights • Separation of Users and Schema • Surface Area Configuration Notification Services • Embed NS in existing application • User-defined match logic • Analysis Services Event Provider Replication • Seamless DDL replication • Merge Web Sync • Oracle Publication • Peer to Peer Transactional replication • Merge replication perf and scalability • New monitor and improved UI Analysis Services and Data Mining • Analysis Management Objects • Windows Integrated Backup and Restore • Web Service/XML for Analysis • Integration Services and DM Integration • Eight new Data Mining algorithms • Auto Packaging and Deployment • Migration Wizard Integration Services • New high performance architecture • Visual design and debugging environment • Extensible with custom code and scripts • XML task and data source • SAP connectivity • Integrated data cleansing & text mining • Slowly changing dimension wizard • Improved flow control • Integration with other BI products Reporting Services • Report Builder • Analysis Services Query Designer • Enhanced Expression Editor • Multi-valued Parameters • Date Picker • Sharepoint Web Parts • Floating Headers • Custom Report Items • XML Data Provider

4. Database Engine • Service Broker • HTTP Access (web services) • Database Tuning Advisor • Enhanced Read ahead & scan • Indexes with Included Columns • Multiple Active Result Sets • Persisted Computed Columns • Try/Catch in T-SQL • Server Events • Snapshot Isolation Level • Partitioning • Synonyms • Dynamic Management Views

5. T-SQL Enhancements • Varchar(max), Varbinary(max) • In SQL 2000, fields over 8,000 characters used Text, Image but that involved a different programming style than smaller fields. • Now fields of type char, varchar, binary, varbinary can hold up to 2 GB storage with same programming style as small fields.

6. T-SQL Enhancements • Exception Handling • Try/Catch, similar to VB.Net error handling BEGIN TRY DECLARE @X INT -- Divide by zero to generate Error SET @X = 1/0 PRINT 'Command after error in TRY' END TRY BEGIN CATCH PRINT 'Error Detected' END CATCH PRINT 'Command after TRY/CATCH blocks'

7. T-SQL Enhancements • New Functions in CATCH block • ERROR_NUMBER(): Returns a number associated with the error. • ERROR_SEVERITY(): Returns the severity of the error. • ERROR_STATE(): Returns the error state number • ERROR_PROCEDURE(): Returns the name of the stored procedure or trigger in which the error occurred. • ERROR_LINE(): Returns the line number inside the failing routine that caused the error. • ERROR_MESSAGE(): Returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

8. T-SQL Enhancements Some Errors Are Not Trapped • Compile errors, like a syntax error • Deferred name resolution errors created by statement level recompilations. • If a process is terminated by a KILL command • Client interrupt requests or broken client connections Control passes back to the application immediately for errors that are not trapped

9. T-SQL Enhancements • Common Table Expressions (CTEs) • Is a non-persistent table view of a query result set • Can be defined one and used multiple times in query (can be used in FROM clause of subsequent step in db object) • Can use for INSERT, UPDATE, DELETE and CREATE VIEW • Provides capability for recursive queries

10. T-SQL Enhancements • Multiple Active ResultSets (MARS) • SQL Server 2005 introduces the ability for multiple statements to return result sets (forward-only, read-only) at the same time on a single connection. In earlier versions of SQL Server, only one statement at a time could actively return result sets for each connection, and no new statements could be executed until all of the result sets were retrieved. (better support for connection pooling)

11. T-SQL Enhancements • PIVOT, UNPIVOT (new) • Can use in the FROM clause of a query. These operators perform some manipulation on an input table-valued expression, and produce an output table as a result. The PIVOT operator rotates rows into columns, possibly performing aggregations along the way. It widens the input table expression based on a given pivot column, generating an output table with a column for each unique value in the pivot column.

12. T-SQL Enhancements • TOP (expression) • The TOP operator has been enhanced to take any numeric expression (such as a variable name) instead of only an integer number to specify the number of rows returned by the operator. TOP can also now be specified in INSERT, UPDATE, and DELETE statements.