SQL 2005 Features

SQL Server 2005 Database Engine Features as they appear in Books Online SP3.

The following abbreviations represent SQL Server 2005 editions:
  • EE = SQL Server 2005 Enterprise Edition
  • DE = SQL Server 2005 Developer Edition
  • SE = SQL Server 2005 Standard Edition
  • WG = SQL Server 2005 Workgroup Edition
  • SSE = SQL Server 2005 Express Edition
  • SSEA = SQL Server 2005 Express Edition with Advanced Services

Features/Database Engine Enhancements EE (32-bit) EE (64-bit) DE (32-bit) DE (64-bit) SE (32-bit) SE (64-bit) WG (32-bit) SSE (32-bit) SSEA (32-bit)
Programmability Features          
Microsoft .NET Framework Yes Yes No No No
Common language runtime integration Yes Yes Yes Yes Yes
User-defined Aggregates Yes Yes Yes Yes Yes
User-defined Data Types Yes Yes Yes Yes Yes
User-defined Functions Yes Yes Yes Yes Yes
SQL Server Managed Provider (managed data access) Yes Yes Yes Yes Yes
DML/DDL Triggers Yes Yes Yes Yes Yes
Logon Triggers Yes5 Yes5 Yes5 Yes5 Yes5
Eventing Yes Yes Yes Yes Yes
Vardecimal Storage Format Yes5 No No No No
Features/Database Engine Enhancements EE (32-bit)EE (64-bit)DE (32-bit)DE (64-bit) SE (32-bit)SE (64-bit) WG (32-bit) SSE (32-bit) SSEA (32-bit)
Data Types          
CLR User-defined Data Types Yes Yes Yes Yes Yes
New XML Data Type Yes Yes Yes Yes Yes
Features/Database Engine Enhancements EE (32-bit)EE (64-bit)DE (32-bit)DE (64-bit) SE (32-bit)SE (64-bit) WG (32-bit) SSE (32-bit) SSEA (32-bit)
Transact-SQL Enhancements          
Exception Handling Yes Yes Yes Yes Yes
Extensions for Querying Support Yes Yes Yes Yes Yes
FOR XML Enhancements Yes Yes Yes Yes Yes
Intrinsics (String Function) Yes Yes Yes Yes Yes
LOB Programming Model Yes Yes Yes Yes Yes
New Analytic Functions (Ranking) Yes Yes Yes Yes Yes
New Apply Operator (Relational Operator) Yes Yes Yes Yes Yes
New Pivot and Unpivot Operators (Relational Operators) Yes Yes Yes Yes Yes
Notification Support Yes Yes Yes Yes Yes
Recursive Queries Yes Yes Yes Yes Yes
Top Enhancements Yes Yes Yes Yes Yes
Distributed Query Yes Yes Yes Yes Yes
Features/Database Engine Enhancements EE (32-bit)EE (64-bit)DE (32-bit)DE (64-bit) SE (32-bit)SE (64-bit) WG (32-bit) SSE (32-bit) SSEA (32-bit)
Full-Text Search Enhancements          
Full-Text Linked Server Queries Yes Yes Yes No Yes
Full-Text Search Across Multiple Columns Yes Yes Yes No Yes
Specification of Language in Query Yes Yes Yes No Yes
Service Broker Yes Yes Yes Yes4 Yes4
HTTP Support (Native HTTP) Yes Yes Yes No No
Features/Database Engine Enhancements EE (32-bit)EE (64-bit)DE (32-bit)DE (64-bit) SE (32-bit)SE (64-bit) WG (32-bit) SSE (32-bit) SSEA (32-bit)
Manageability Features          
Backup and Restore of Full-text Catalogs Yes Yes Yes No Yes
Full-text catalogs included in database attach/detach operations Yes Yes Yes No Yes
Full-Text Search Upgrade Yes Yes Yes No Yes
Query Against Views (Formerly Indexing of Views) Yes Yes Yes No No
Indexing of XML Datatype Yes Yes Yes Yes Yes
Index Performance Improvements (formerly Inline Gathering , MSSearch 3.0) Yes Yes Yes No No
Parallel Service Security (formerly Security Context Change) Yes Yes Yes No No
Rich Status Reporting Yes Yes Yes No No
Features/Database Engine Enhancements EE (32-bit)EE (64-bit)DE (32-bit)DE (64-bit) SE (32-bit)SE (64-bit) WG (32-bit) SSE (32-bit) SSEA (32-bit)
Security Features          
All Permissions Grantable Yes Yes Yes Yes Yes
Encryption Yes Yes Yes Yes Yes
Fine-Grained Administration Rights Yes Yes Yes Yes Yes
Integrated Security for SQL Authorization Yes Yes Yes Yes Yes
Separation of Users and Schema Yes Yes Yes Yes Yes
Common Criteria Compliance Yes5 No No No No
Features/Database Engine Enhancements EE (32-bit)EE (64-bit)DE (32-bit)DE (64-bit) SE (32-bit)SE (64-bit) WG (32-bit) SSE (32-bit) SSEA (32-bit)
Availability Features (Database Failure and Redundancy)          
Failover Clustering Yes 2-node No No No
Multi-Instance Support 50 16 16 16 16
Database Mirroring Yes Safety FULL only No No; can be used as a witness No; can be used as a witness
Log Shipping Yes Yes Yes No No
Database Snapshot Yes No No No No
Features/Database Engine Enhancements EE (32-bit)EE (64-bit)DE (32-bit)DE (64-bit) SE (32-bit)SE (64-bit) WG (32-bit) SSE (32-bit) SSEA (32-bit)
Database Maintenance Features          
Data and Backup Checksum Integrity Checks Yes Yes Yes Yes Yes
Dedicated Administrator Connection Yes Yes Yes Yes1 Yes1
Dynamic AWE Yes Yes No No No
Database Available during Recovery Undo Yes No No No No
Hot Add Memory2 Yes No No No No
Mirrored Backup Media Yes No No No No
Online Index Operations Yes No No No No
Page Restore Yes Yes Yes Yes Yes
Online Page and File Restore Yes No No No No
Parallel Index Operations Yes No No No No
Features/Database Engine Enhancements EE (32-bit)EE (64-bit)DE (32-bit)DE (64-bit) SE (32-bit)SE (64-bit) WG (32-bit) SSE (32-bit) SSEA (32-bit)
XML Features          
XML Datatype Yes Yes Yes Yes Yes
Xquery Support Yes Yes Yes Yes Yes
XML Data Manipulation Language (XML DML) Yes Yes Yes Yes Yes
XML Views Yes Yes Yes Yes Yes
FOR XML Enhancements Yes Yes Yes Yes Yes
XML Type System Support (XML Schema Support) Yes Yes Yes Yes Yes
Features/Database Engine Enhancements EE (32-bit)EE (64-bit)DE (32-bit)DE (64-bit) SE (32-bit)SE (64-bit) WG (32-bit) SSE (32-bit) SSEA (32-bit)
Scalability and Performance Features          
Database Engine Tuning Advisor (Replaces Index Tuning Wizard) Yes Yes Yes No No
Updateable Distributed Partitioned Views Yes No No No No
Enhanced Read-ahead and Scan (Super Scan) Yes No No No No
Indexed Views (Materialized Views)3 Yes Yes Yes Yes Yes
Multiple Active Result Sets (MARS) Yes Yes Yes Yes Yes
Persisted Computed Columns Yes Yes Yes Yes Yes
Queuing Support Yes Yes Yes Yes Yes
Snapshot Isolation Level (Row-level Versioning) Yes Yes Yes Yes Yes
Table and Index Partitioning Yes No No No No
VIA Support Yes No No No No
Parallel DBCC Yes No No No No
Features/Database Engine Enhancements EE (32-bit)EE (64-bit)DE (32-bit)DE (64-bit) SE (32-bit)SE (64-bit) WG (32-bit) SSE (32-bit) SSEA (32-bit)
International Features (Collation, Character Support, and Global Deployment)          
Complex Script/Bi-directional Support Yes Yes Yes Yes Yes
GB18030 Support Yes Yes Yes Yes Yes
Surrogate Support Yes Yes Yes Yes Yes
Language Pack Yes Yes Yes Yes Yes

1The Dedicated Administrator Connection feature for SQL Server Express Edition is supported with the trace flag 7806. For more information about trace flags, see the “Trace Flags (Transact-SQL)” topic in SQL Server 2005 Books Online.

2This feature requires SQL Server 2005 Enterprise Edition and is only available for 64-bit SQL Server 2005, and for 32-bit SQL Server when AWE is enabled.

3This feature is supported in all editions of SQL Server 2005, except that indexed view-to-query matching by the Query Optimizer is supported only in Enterprise Edition and Developer Edition. Indexed views can be created in all editions of SQL Server 2005, and queried by name using the NOEXPAND hint.

4 SQL Server Express or SQL Server Express with Advanced Services can exchange Service Broker messages with other editions of SQL Server. Messages between two instances of SQL Server Express or SQL Server Express with Advanced Services must be routed through another edition of SQL Server. For example, SQL Server Standard Edition or SQL Server Enterprise Edition.

5This feature is available in SQL Server 2005 SP2 and later versions.

 

 

 

 

 SQL Server 2005 Management Tools Features
The following abbreviations represent SQL Server 2005 editions:
  • EE = SQL Server 2005 Enterprise Edition
  • DE = SQL Server 2005 Developer Edition
  • SE = SQL Server 2005 Standard Edition
  • WG = SQL Server 2005 Workgroup Edition
  • SSE = SQL Server 2005 Express Edition
  • SSEA = SQL Server 2005 Express Edition with Advanced Services

 

Features/Management Tools Enhancements EE (32-bit) DE (32-bit) EE (64-bit) DE (64-bit) SE (32-bit) WG (32-bit) SE (64-bit) SSE (32-bit) SSEA (32-bit)
Programmability Features            
Design Tools Enhancements            
Query Designer Yes Yes Yes No No No
Query Editor Yes Yes Yes No No No
MDX Query Editor Yes Yes Yes No No No
MDX IntelliSense Yes Yes Yes No No No
Transact-SQL IntelliSense Yes Yes Yes No No No
Version Control Support Yes Yes Yes No No No
XML/A Yes Yes Yes No No No
Features/Management Tools Enhancements EE (32-bit)DE (32-bit)EE (64-bit)DE (64-bit) SE (32-bit) WG (32-bit) SE (64-bit) SSE (32-bit) SSEA (32-bit)
Management Programming            
SQL Server Management Objects (SMO) Yes Yes Yes No Yes Yes
WMI Configuration Provider Yes Yes Yes No No No
Manageability Features            
Configuration            
SQL Server Configuration Manager, MMC Snap-in Yes Yes Yes Yes Yes Yes
Features/Management Tools Enhancements EE (32-bit)DE (32-bit)EE (64-bit)DE (64-bit) SE (32-bit) WG (32-bit) SE (64-bit) SSE (32-bit) SSEA (32-bit)
Management            
Microsoft Visual Studio Integration Yes Yes Yes Yes Yes Yes
New Dialog Style (Fully Scriptable and Schedulable User Interface) Yes Yes Yes No No No
SQL Server Management Studio Yes Yes Yes Yes No SQL Server Management Studio Express (a subset of SQL Server Management Studio)
Master Server Yes (automatic) Yes (manual) No No No No
Target Server Yes Yes Yes No No No
Features/Management Tools Enhancements EE (32-bit)DE (32-bit)EE (64-bit)DE (64-bit) SE (32-bit) WG (32-bit) SE (64-bit) SSE (32-bit) SSEA (32-bit)
Operations            
Microsoft Operations Manager pack for SQL Server 2005 Yes Yes Yes Yes No No
SQLCMD Command Prompt Tools (formerly OLESQL) Yes Yes Yes Yes Yes Yes
SQL Server Agent Job Reuse Yes Yes Yes Yes No No
SQL Server Agent Performance Counters Yes Yes Yes Yes No No
SQL Server Agent Scheduling Yes Yes Yes Yes No No
SQL Server Agent Security Yes Yes Yes Yes No No
SQL Server Agent Subsystems for OLAP and SSIS Yes Yes Yes Yes No No
SQL Server Agent Yes Yes Yes Yes No No
Web Assistant Yes Yes Yes Yes No No
SQL Mail Yes on 32-bitNo on 64-bit Yes Yes No No No
Database Mail Yes on 32-bitNo on 64-bit Yes Yes No No No
Features/Management Tools Enhancements EE (32-bit)DE (32-bit)EE (64-bit)DE (64-bit) SE (32-bit) WG (32-bit) SE (64-bit) SSE (32-bit) SSEA (32-bit)
Scalability and Performance            
Database Engine Tuning Advisor Yes Yes Yes Yes No No
Performance Counter Correlation Yes Yes Yes Yes No No
SQL Server Profiler Enhancements Yes Yes Yes Yes No No
Profiling Analysis Services Yes Yes Yes Yes No No
Exportable Showplan and Deadlock Traces Yes Yes Yes Yes No No
Features/Management Tools Enhancements EE (32-bit)DE (32-bit)EE (64-bit)DE (64-bit) SE (32-bit) WG (32-bit) SE (64-bit) SSE (32-bit) SSEA (32-bit)
International Features (Collation, Character Support, and Global Deployment)            
Complex Script/ Bi-directional Support Yes Yes Yes Yes Yes Yes
GB18030 Support Yes Yes Yes Yes Yes Yes
Surrogate Support Yes Yes Yes Yes Yes Yes
 

 

 

 

 SQL Server 2005 Analysis Services Features
The following abbreviations represent SQL Server 2005 editions:
  • EE = SQL Server 2005 Enterprise Edition
  • DE = SQL Server 2005 Developer Edition
  • SE = SQL Server 2005 Standard Edition
  • WG = SQL Server 2005 Workgroup Edition
  • SSE = SQL Server 2005 Express Edition
  • SSEA = SQL Server 2005 Express Edition with Advanced Services

 

Features/Analysis Services Enhancements EE (32-bit) DE (32-bit) EE (64-bit) DE (64-bit) SE (32-bit) WG (32-bit) SSE (32-bit) SSEA (32-bit) SE (64-bit)
Programmability Features          
.NET Framework Yes Yes No No Yes
.NET Stored Procedures Yes Yes Yes Yes Yes
Cross Platform, Zero Footprint Client Access Yes Yes No No Yes
Native XML Web services Yes Yes No No Yes
HTTP access Yes Yes No No Yes
Binary and compressed XML transport Yes No No No No
Manageability Features          
Auto Referential Integrity Handling Yes Yes No No Yes
Capture and Replay Yes Yes No No Yes
Fine-Grain Administration Rights Yes Yes No No Yes
Profiler Yes Yes No No Yes
Trace Yes Yes No No Yes
Flight Recorder Yes Yes No No Yes
Features/Analysis Services Enhancements EE (32-bit)DE (32-bit)EE (64-bit)DE (64-bit) SE (32-bit) WG (32-bit) SSE (32-bit)SSEA (32-bit) SE (64-bit)
Management Programming Features          
Analysis Management Objects (AMO and DMO) Yes Yes No No Yes
Availability Features          
Enhanced Backup and Restore Yes Yes No No Yes
Failover Clustering Yes Yes No No Yes
Multi-Instances 50 16 No No 16
Server Synchs Yes Yes No No Yes
Features/Analysis Services Enhancements EE (32-bit)DE (32-bit)EE (64-bit)DE (64-bit) SE (32-bit) WG (32-bit) SSE (32-bit)SSEA (32-bit) SE (64-bit)
Business Intelligence Features          
Auto Packaging and Deployment Yes Yes No No Yes
Business Intelligence Wizard Yes Yes No No Yes
Data Source Views Management Yes Yes No No Yes
Integrated Business Intelligence Development Studio Yes Yes No No Yes
Autobuild Builder Technology Yes Yes No No Yes
MDX Debugger Yes Yes No No Yes
Multi-Partition Editor Yes Yes No No Yes
Source Control and Versioning Yes Yes No No Yes
Unified Calculation Editor Yes Yes No No Yes
Features/Analysis Services Enhancements EE (32-bit)DE (32-bit)EE (64-bit)DE (64-bit) SE (32-bit) WG (32-bit) SSE (32-bit)SSEA (32-bit) SE (64-bit)
Data Mining Features          
Enhanced Integrated OLAP and Data Mining Functionality (MDX Prediction function, DM dimensions) Yes Yes No No Yes
Parallelism for model processing Yes No No No No
Parallelism for model prediction Yes No No No No
Text-Mining Term Extraction Transformation (SSIS) Yes No No No No
Text-Mining Term Lookup Transform (SSIS) Yes No No No No
Data Mining Query Transformation (SSIS) Yes No No No No
Data Mining Processing Destination (SSIS) Yes No No No No
Algorithm Plug-in API Yes No No No No
Algorithm Viewers Yes Yes No No Yes
Data Mining Tools (including Wizards, Editors, Query Builders, Lift Chart) Yes Yes No No Yes
Standard Data Mining Algorithms Yes Yes No No Yes
Advanced configuration and tuning options for Data Mining algorithms Yes No No No No
Reporting Integration with DM Prediction Queries Yes Yes No No Yes
Features/Analysis Services Enhancements EE (32-bit)DE (32-bit)EE (64-bit)DE (64-bit) SE (32-bit) WG (32-bit) SSE (32-bit)SSEA (32-bit) SE (64-bit)
Advanced Analytic Features          
Account Intelligence Yes No No No No
Cross-database/cross-server linked measures and dimensions Yes No No No No
Metadata translations Yes No No No No
Perspectives Yes No No No No
Semi-additive Measures Yes No No No No
Writeback Dimensions Yes No No No No
Features/Analysis Services Enhancements EE (32-bit)DE (32-bit)EE (64-bit)DE (64-bit) SE (32-bit) WG (32-bit) SSE (32-bit)SSEA (32-bit) SE (64-bit)
Data Warehouse Building Features          
Create cubes without database Yes Yes No No No
Auto-generate Staging and Data Warehouse Schema Yes Yes No No No
Auto-generate DTS Packages for Updating Data Warehouse Data Yes Yes No No No
Extended Dimensional Modeling Features          
Measure expressions Yes No No No No
Scalability and Performance Features          
Proactive Caching Yes No No No No
Auto Parallel Partition Processing Yes No No No No
Partitioned Cubes Yes No No No No
Distributed Partitioned Cubes Yes No No No No
 

 

 

 

1In SQL Server Standard Edition, you can have a maximum of 5000 attributes in any single column in a data mining model. This limitation also applies to values in nested tables. For more information, see article ID 932609 in the Microsoft Knowledge Base.

 
 SQL Server 2005 Reporting Services Features
The following abbreviations represent SQL Server 2005 editions:
  • EE = SQL Server 2005 Enterprise Edition
  • DE = SQL Server 2005 Developer Edition
  • SE = SQL Server 2005 Standard Edition
  • WG = SQL Server 2005 Workgroup Edition
  • SSE = SQL Server 2005 Express Edition
  • SSEA = SQL Server 2005 Express Edition with Advanced Services

  
 SQL Server 2005 Reporting Services Features
The following abbreviations represent SQL Server 2005 editions:
  • EE = SQL Server 2005 Enterprise Edition
  • DE = SQL Server 2005 Developer Edition
  • SE = SQL Server 2005 Standard Edition
  • WG = SQL Server 2005 Workgroup Edition
  • SSE = SQL Server 2005 Express Edition
  • SSEA = SQL Server 2005 Express Edition with Advanced Services

 

 

Features/Reporting Services Enhancements EE (32-bit) DE (32-bit) EE (64-bit) DE (64-bit) SE (32-bit) SE (64-bit) WG (32-bit) SSE (32-bit) SSEA (32-bit)
Support for remote and nonrelational data sources Yes Yes No? No No?
DHTML, Excel, PDF, and Image rendering extensions Yes Yes Yes No Yes
MHTML, CSV, XML, and Null rendering extensions Yes Yes No No No
E-mail and file share delivery extensions Yes Yes No No No
Custom data processing, delivery, and rendering extensions Yes Yes No No No
Custom report items Yes Yes No No No
Custom authentication extensions Yes Yes Yes No No
Report caching Yes Yes No No No
Report history Yes Yes No No No
Scheduling Yes Yes No No No
Subscriptions Yes Yes No No No
Data-driven subscriptions Yes No No No No
User-defined role definitions Yes Yes No No No
Report model data sources Yes Yes Yes No No
Report model item security Yes Yes No No No
Support for infinite clickthrough in ad hoc reports Yes No No No No
Report Builder Yes Yes Yes No No
Report Manager Yes Yes Yes No Yes
SQL Server Management Studio Yes Yes Yes No No
Report Designer in Business Intelligence Development Studio Yes Yes Yes No Yes¹
Report server scale-out deployment Yes No No No No
 

 

 

 

For information on additional Reporting Services features available in SQL Server 2005, see “Reporting Services Features” in SQL Server 2005 Books Online.

¹ Business Intelligence Development Studio is installed by using SQL Server 2005 Express Edition Toolkit. For more information, see SQL Server Express Toolkit in SQL Server 2005 Express Edition Books Online.

? In the Workgroup Edition and Express Edition with Advanced Services, Reporting Services only supports relational databases on the local Database Engine instance.

 

 

 

 

 SQL Server 2005 Notification Services Features
The following abbreviations represent SQL Server 2005 editions:
  • EE = SQL Server 2005 Enterprise Edition
  • DE = SQL Server 2005 Developer Edition
  • SE = SQL Server 2005 Standard Edition
  • WG = SQL Server 2005 Workgroup Edition
  • SSE = SQL Server 2005 Express Edition
  • SSEA = SQL Server 2005 Express Edition with Advanced Services

 

Features/Notification Services Enhancements EE (32-bit) DE (32-bit) EE (64-bit) DE (64-bit) SE (32-bit) WG (32-bit) SSE (32-bit) SSEA (32-bit) SE (64-bit)
Notification Services SDK (API, NSControl, ICF/ADF) Yes Yes No No Yes
Notification Services Engine and Client components Yes Yes No No Yes
Scalability (Parallelism, Multicast, Distributed deployment) Yes No No No No
 

 

 

 

 SQL Server 2005 Integration Services Features
The following abbreviations represent SQL Server 2005 editions:
  • EE = SQL Server 2005 Enterprise Edition
  • DE = SQL Server 2005 Developer Edition
  • SE = SQL Server 2005 Standard Edition
  • WG = SQL Server 2005 Workgroup Edition
  • SSE = SQL Server 2005 Express Edition
  • SSEA = SQL Server 2005 Express Edition with Advanced Services

 

Features/Integration Services Enhancements EE (32-bit) DE (32-bit) EE (64-bit) DE (64-bit) SE (32-bit) WG (32-bit) SSE (32-bit) SSEA (32-bit) SE (64-bit)
SQL Server Import and Export Wizard and supporting connections, source and destination adapters, and tasks Yes Yes Yes No Yes
Execute SQL Task Yes Yes Yes No Yes
OLE DB Source and Destination Adapters Yes Yes Yes No Yes
SSIS command prompt tools Yes Yes Yes No Yes
SSIS Package Designer Yes Yes Yes No Yes
Legacy support for DTS packages Yes Yes Yes Yes Yes
SSIS Service Yes Yes No No Yes
All other source and destination adapters, tasks, and transformations, except for those listed below Yes Yes No No Yes
Data Mining Query Transformation Yes No No No No
Data Mining Model Training Destination Adapter Yes No No No No
Fuzzy Grouping Transformation Yes No No No No
Fuzzy Lookup Transformation Yes No No No No
Term Extraction Transformation Yes No No No No
Term Lookup Transformation Yes No No No No
Slowly Changing Dimension Transformation and Wizard Yes Yes No No No
Dimension Processing Destination Adapter Yes No No No No
Partition Processing Destination Adapter Yes No No No No
 

 

 

 

 SQL Server 2005 Replication Features
The following abbreviations represent SQL Server 2005 editions:
  • EE = SQL Server 2005 Enterprise Edition
  • DE = SQL Server 2005 Developer Edition
  • SE = SQL Server 2005 Standard Edition
  • WG = SQL Server 2005 Workgroup Edition
  • SSE = SQL Server 2005 Express Edition
  • SSEA = SQL Server 2005 Express Edition with Advanced Services

 

Features/Replication Enhancements EE (32-bit) DE (32-bit) EE (64-bit) DE (64-bit) 4 SE (32-bit) WG (32-bit) SSE (32-bit) SSEA (32-bit) SE (64-bit)
Merge replication Yes Yes Yes1 Subscriber only2 Yes
Merge replication over HTTPS (Web Synchronization) Yes Yes Yes1 Subscriber only2 Yes
Transactional replication Yes Yes Yes1 Subscriber only3 Yes
Snapshot replication Yes Yes Yes Subscriber only Yes
Non-SQL Server Subscribers Yes Yes No No Yes
Oracle publishing Yes No No No No
Peer-to-peer transactional replication Yes No No No No