Transcript Building Mission Critical Applications

What’s New in
SQL Server 2005
From a Developer’s Perspective
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
What’s new in SQL Server 2005
Database Engine
Database Maintenance
 Service Broker
 Backup and Restore Enhancements
 HTTP Access
 Checksum Integrity Checks
 Database Tuning Advisor
 Dedicated Administrator Connection
 Enhanced Read ahead & scan
 Dynamic Configuration AWE
 Indexes with Included Columns
 Highly-available Upgrade
 Multiple Active Result Sets
 Online Index Operations
 Persisted Computed Columns
 Online Restore
 Try/Catch in T-SQL statements
Management Tools
 Common Table Expressions
 MDX & XML/A Query Editor
 Server Events
 Maintenance Plan Designer
 Snapshot Isolation Level
 Source Control Support
 Partitioning
 Profiler access to non-sa
 Synonyms
 SQLCMD Command Line Tool
 Dynamic Management Views
 Database Mail
.NET Framework
Performance Tuning
 Common Language Runtime Integration
 Profiling Analysis Services
 CLR-based Types, Functions, & Triggers
 Exportable Showplan & Deadlocks
 SQL Server .NET Data Provider
 Profiler Enhancements
Data Types
 New Trace Events
 CLR-based Data Types
Full-text Search
 VARCHAR(MAX), VARBINARY(MAX)
 Backup/Restore includes FT catalogs
 XML Datatype
 Multi-instance service
Database Failure and Redundancy
SQL Client .NET Data Provider
 Fail-over Clustering (up to 8 node)
 Server Cursor Support
 Database Mirroring
 Multiple Active Result Sets
 Database Snapshots
Security
 Enhanced Multi-instance Support
 Catalog and meta-data security
XML
 Password policy enforcement
 New XML data type
 Fine Grain Administration Rights
 XML Indexes
 Separation of Users and Schema
 XQUERY Support
 Surface Area Configuration
 XML Schema (XSD) support
Notification Services
 FOR XML PATH
 Embed NS in existing application
 XML Data Manipulation Language
 User-defined match logic
 SQLXML 4.0
 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
Database Engine
Service Broker
Try/Catch in T-SQL
HTTP Access (web services)
Server Events
Database Tuning Advisor
Snapshot Isolation Level
Enhanced Read ahead & scan
Partitioning
Indexes with Included Columns
Synonyms
Multiple Active Result Sets
Dynamic Management Views
Persisted Computed Columns
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.
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'
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.
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
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
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)
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.
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.