Matt Nunn
Microsoft Corporation

May 2005
Updated June 2005

Applies to:
   Microsoft SQL Server 2005
   SQL Server database development
   Microsoft .NET Framework
   Microsoft Visual Studio 2005
   Microsoft ADO.NET 2.0

Summary: This document provides an overview of new features for database development in Microsoft SQL Server 2005. (13 printed pages)

Contents

A New Paradigm for Database Development
.NET Framework Integration
XML Technologies
New Application Frameworks
Language Enhancements
Conclusion

A New Paradigm for Database Development

With the release of Microsoft SQL Server 2005, the world of database development is changing. As a database developer, you now have the option to appropriately locate your code in relation to its functionality, to access data in native formats such as XML, and to build complex systems that are driven by the power of the database server. Database development is becoming more integrated than ever before, and all of the tools that you need are available right at your fingertips.

This paper discusses some of the new features of SQL Server 2005 that will enable you to build a new class of database application.

.NET Framework Integration

With the release of Microsoft SQL Server 2005, database programmers can now take full advantage of the Microsoft .NET Framework class library and modern programming languages to implement functionality within the server. Using common language runtime (CLR) integration, you can code your stored procedures, functions, and triggers in the .NET Framework language of your choice. Microsoft Visual Basic .NET and the C# programming language both offer object-oriented constructs, structured exception handling, arrays, namespaces, and classes. In addition, the .NET Framework provides thousands of classes and methods that have extensive built-in capabilities that you can make use of on the server-side. Many tasks that were awkward or difficult to perform in Transact-SQL can be better accomplished by using managed code; additionally, two new types of database objects, aggregates and user-defined types, are available. You can now better use the knowledge and skills that you have already acquired to write in-process code. In short, SQL Server 2005 enables you to extend the database server to more easily perform appropriate computation and operations on the back end.

This integration between SQL Server and the CLR provides several major benefits:

• Enhanced programming model: Programming languages that are compatible with the .NET Framework are in many respects richer than Transact-SQL, offering constructs and capabilities that were previously not available to SQL developers.
• Enhanced safety and security: Managed code runs in a CLR environment, which is hosted by the database engine. This allows .NET Framework database objects to be safer and more secure than the extended stored procedures available in earlier versions of SQL Server.
• User defined types and aggregates: Two new database objects that expand the storage and querying capabilities of SQL Server are enabled by hosting the CLR.
• Common development environment: Database development is integrated into the Microsoft Visual Studio 2005 development environment. You can use the same tools for developing and debugging database objects and scripts that you use to write middle-tier or client-tier .NET Framework components and services.
• Performance and scalability: Because managed code is optimized for complex procedural and scientific processing and compiles to native code prior to execution, the use of managed code can achieve significant performance increases in some scenarios.

You can capitalize on the CLR integration to write code that has more complex logic and is more suited for computation tasks, by using languages such as Visual Basic .NET and C#. In addition, Visual Basic .NET and C# offer object-oriented capabilities such as encapsulation, inheritance, and polymorphism. You can now easily organize related code into classes and namespaces, which means you can more easily organize and maintain your code investments when you are working with large amounts of code. This ability to logically and physically organize code into assemblies and namespaces is a huge benefit, and will allow you to better find and relate different pieces of code in a large database implementation.

Managed code is more efficient than Transact-SQL at processing numbers and managing complicated execution logic, and provides extensive support for string handling, regular expressions, and so on. Also, with the functionality that is available in the .NET Framework class library, you now have full access to thousands of pre-built classes and routines that you can access easily from any stored procedure, trigger, or user-defined function. Everything from improved string handling functions, math functions, date operations, access to system resources, advanced encryption algorithms, file access, image processing, and XML data manipulation is easily accessible from managed stored procedures, functions, triggers, and aggregates.

One of the major benefits of managed code is type safety. Before managed code is executed, the CLR performs several checks, through a process known as verification, to ensure that the code is safe to run. For example, the code is checked to ensure that memory is not read that has not been written to.

Choosing Between Transact-SQL and Managed Code

When you write stored procedures, triggers, and user-defined functions, one decision you will now have to make is whether to use traditional Transact-SQL or a programming language that is compatible with the .NET Framework, such as Visual Basic .NET or C#. The answer to this question will depend on the particular situation that is involved; in some situations you'll want to use Transact-SQL, while in other situations you will want to use managed code.

Transact-SQL is best for situations in which the code will primarily perform data access with little or no procedural logic. Programming languages that are compatible with the .NET Framework are best suited for computationally intensive functions and procedures that feature complex logic, or for situations in which you want to take advantage of the .NET Framework class library.

Code placement is also important. Both Transact-SQL and managed code run on the server. This places functionality and data close together, and allows you to take full advantage of a server's processing power. This is beneficial because it decreases traffic between the data and middle-tier. By simply performing the computation next to the data, a system that is I/O-bound may find this method significantly beneficial. CLR functions also can take advantage of the SQL Server query processor, which can parallelize and optimize execution. On the other hand, you may wish to avoid placing processor intensive tasks on your database server. Most client computers today are very powerful, and you may wish to take advantage of this processing power by placing as much code as possible on the client. There is no "one size fits all" answer.

Web Services

In SQL Server 2005, you can develop XML Web services in the database tier, making SQL Server an HTTP listener. This provides a new type of data access capability for applications that are centralized around Web services. When SQL Server 2005 is used on Microsoft Windows Server 2003 or Microsoft Windows XP SP2, you can use HTTP to access SQL Server directly by utilizing the lightweight Web server, HTTPSYS that is now in the operating system, without using a middle-tier listener such as Microsoft Internet Information Services (IIS). SQL Server exposes a Web service interface, to allow execution of SQL statements and invocation of functions and procedures. Query results are returned in XML format and can take advantage of the Web services infrastructure of Visual Studio.

ADO.NET

There is a lot that's new in the next version of ADO.NET. From new support for query change notifications, multiple active result sets (MARS), and native type support in Visual Studio 2005, ADO.NET evolves dataset access and manipulation to achieve greater scalability and flexibility.

ADO.NET Notification Support

SQL Server 2005 introduces notification support for SQL Server queries. You can use this support to send a command to SQL Server, and to request that a notification be generated if executing the same command again produces different results from those initially obtained. You accomplish this by using a dependency object that detects when the underlying data is changed. Commands that are sent to the server through any of the client APIs such as ADO.NET, OLE DB, Open Database Connectivity (ODBC), Microsoft ActiveX Data Objects (ADO), or SOAP may include a tag that requires a notification. For each statement that is executed as part of the request, the server creates a Notification Subscription that fires once for each statement that is included in the request. Notifications are delivered through an asynchronous SQL Service Broker queue. Query notifications are useful for enabling the caching of results in applications such as database-driven Web sites. If ASP.NET 2.0 is being used, then support for SQL Server Query Notifications is built directly into the product and can be turned on simply by choosing the notification option.

Multiple Active Result Sets

Multiple active result sets (MARS) provides the ability to have more than one pending request per connection, in particular to have more than one default result set open per connection. Default result sets are forward-only, read-only result sets. For default result sets, the client drivers transparently retrieve the data in large chunks (Tabular Data Stream buffer-sized chunks) so that the application requests are satisfied without a roundtrip to the server (as in the case of server cursors). The application can use a simple row-at-a-time programming model without compromising performance.

The multiple active result sets feature removes the current restriction in which an open default result set blocks the driver from sending requests to the server until the entire result set is consumed.

Native Data Type Support

Not only does ADO.NET 2.0 allow many new data access options but it also brings support for all of the new SQL Server data types to managed code languages. Visual Studio developers can now take advantage of all the new SQL Server 2005 data types such as SQLXML, user-defined types, and varchar(max), directly from within the Visual Studio environment.

Snapshot Isolation Support

SQL Server 2005 introduces a new snapshot isolation level. Snapshot isolation is a row versioning mechanism in which versions of data are stored for data readers. This new isolation level provides the following benefits:

• Increased data availability for read-only applications. Nonblocking read operations are allowed in an OLTP environment.
• Automatic mandatory conflict detection for write transactions.
• Simplified migration of applications from Oracle to SQL Server.

For example, locking can cause blocks between applications that are reading and writing the same data simultaneously. If a transaction changes a row, another transaction cannot read the row until the write commits. With snapshot isolation, the reader can access the previous committed value of the row.

The snapshot isolation level is supported and exposed through ADO, OLE DB, SQLOLEDB, Shape Provider, SQLODBC, the OLE DB Managed Provider, and the SQL Managed Provider.

SQL Management Objects

The SQL Management Objects (SMO) model is the management object model for SQL Server 2005. SMO represents significant design and architectural improvements for the SQL Server management object model. It is a simple to use, but rich object model that is based on .NET Framework managed code. SMO is the primary tool for developing database management applications using .NET Framework or for automating SQL Server 2005 management operations. SMO is used by every dialog box in SQL Server Management Studio, and every administrative action that you can perform in SQL Server Management Studio you can also accomplish by using SMO.

The new SMO object model and the Microsoft Windows Management Instrumentation (WMI) APIs replace SQL-DMO. Where possible, SMO incorporates similar objects as SQL-DMO for ease of use. You can still use SQL Server 2005 with SQL-DMO, but SQL-DMO will not be updated to manage features that are specific to SQL Server 2005.

SMO and SQL-DMO

The SMO object model is a logical continuation of the work done in SQL-DMO. SMO is feature-compatible with SQL-DMO, containing many of the same objects. Where possible, the original SQL-DMO design is followed, but SMO has a number of additional features beyond SQL-DMO. To achieve maximum data definition language (DDL) and administrative coverage for SQL Server 2005, SMO adds more than 150 new classes.

The primary advantages of SMO are in its performance and scalability. SMO has a cached object model, which allows you to change several properties of an object before effecting the changes to SQL Server. As a result, SMO makes fewer round trips to the server, and makes its objects more flexible. SMO also has optimized instantiation, meaning that you can partially or fully instantiate objects. You can load many objects quickly by not instantiating all the properties of the objects.

Unlike SQL-DMO, which has a single application root directory that keeps references to all created server objects, SMO lets you establish multiple roots for servers without establishing a new connection. SMO implements advanced multiple-phase scripting, in addition to supporting SQL-DMO style scripting. You can also switch an object into capture mode and capture any DDL that would be emitted for that object, without actually applying changes to the server.

SQL-DMO also has a managed computer object that simplifies the interface to WMI, in order to support WMI monitoring and server configuration through the SMO object interface.

XML Technologies

XML has become a common format for storing and transferring data, and is a popular choice for marked-up, structured, or semi-structured information. Examples of these kinds of data include text (marked up to identify document structure and highlighting), nested objects (structured), and heterogeneous data that may change its structure from one instance to another (semi-structured). XML is also an important and widely accepted standard for disseminating data between different applications over local networks and the Internet.

Microsoft SQL Server 2000 supported the use of XML through Microsoft SQLXML, which allows you to convert relational data to an XML format and store XML data in relational tables. Microsoft SQL Server 2005 builds on this functionality by supporting XML as a first-class data type, as well as by providing a new query language for XML documents and allowing you to perform in-place modifications.

To better support the needs of users who are working with XML data, a new XML data type has been introduced. This data type has methods; —query(), exist(), value(), nodes(), and modify() — which implement an important set of the XML Query (XQuery) specification that is currently in last call. In fact, this specification has been extended in SQL Server 2005 with the addition of XML data modification constructs. In order to support XML typing, keywords have been added for registering and managing XML schemas. There are also changes to FOR XML and OPENXML, two features that were introduced in SQL Server 2000 for generating XML from relational data and vice versa. These are now enhanced with support for the XML data type.

XML Data Type

XML can model complex data rather than being limited to the scalar types that are supported by SQL Server. As such, a string-based, built-in data type, such as char or varchar, does not suffice to make full and effective use of the power and the numerous advantages of XML. For example, if XML is stored as a string, you can insert or select an entire document, or even retrieve contiguous bytes from it, but you cannot query into the contents of the document itself. By providing the XML data type, SQL Server 2005 allows you to query portions of an XML document, validate that the document conforms to an XML schema, and even modify the contents of the XML document in place. It also integrates traditional, relational data with data in unstructured or semi-structured XML documents in ways that are not possible with SQL Server 2000. In SQL Server 2005, XML data is stored as binary large objects (BLOBs) in an internal representation that allows efficient reparsing, querying, and some compression.

To increase performance when working with XML data in SQL Server 2005, you can also apply indexes to the XML data type down to the node level. SQL Server 2005 supports both primary and secondary indexes on XML data and makes use of the power of the existing optimizer engine to utilize the indexes where appropriate when querying XML data.

A collection of XML schemas can be associated with a column of type XML. This provides validation for constraints, inserts, and updates, and typing of values inside stored XML data, as well as optimizations for storage and query processing. SQL Server 2005 also provides several DDL statements for managing schemas on the server.

Retrieving and Writing XML

SQL Server 2005 includes several enhancements to the FOR XML and OPENXML functionality that were first introduced in SQL Server 2000.

FOR XML

The FOR XML clause in SQL Server 2000 does not provide a way to consume the XML results on the server. You cannot store the XML results in a table (without, of course, first returning them to the client) or assign them to a variable. SQL Server 2005 enhances FOR XML by adding support for the XML data type and allowing XML consumption on the server. It does this by adding a TYPE directive in FOR XML. For example, the results of the SELECT...FOR XML TYPE statement generate an XML data type instance that can be assigned to a local, XML variable, or that can be used in a subsequent INSERT statement to populate an XML data type column. The PATH mode specifies the path in the XML tree where a column's value should appear. The TYPE option and the PATH options that are included in FOR XML simplify the generation of complex XML, and are more convenient to use than FOR XML EXPLICIT queries. FOR XML also works over XML data type columns in SQL Server 2005.

OPENXML

SQL Server 2000 essentially treated the FOR XML clause and the OPENXML rowset function as inverse companions. That is, with FOR XML you can retrieve relational data as XML; with OPENXML you can turn XML into relational data, against which you can set up SQL joins or execute queries. SQL Server 2005 enhances the functionality of OPENXML. In addition to the XML data type, support for several new data types is provided, such as user-defined types. You can use these in the OPENXML WITH clause, and you can also pass an XML data type instance to sp_preparedocument.

XQuery Support

The XML Query Language, or XQuery, is an intelligent and robust language that is optimized for querying all types of XML data. With XQuery you can run queries against variables and columns of the XML data type using the latter's associated methods. As with many of the XML standards, the World Wide Web Consortium (W3C) oversees the development of XQuery. XQuery evolved from a query language called Quilt, which was itself based on a variety of other query languages such as the XML Path Language (XPath) version 1.0, XQL, and SQL. It also contains XPath 2.0 as a subset. Therefore, if you have experience using XPath 1.0, you can capitalize on your skills and do not have to learn an entirely new query language. There are, however, significant enhancements that go beyond XPath 1.0, such as typing, special functions, and support for better iteration, sorting of results, and construction.

SQL Server 2005 ships with deep XQuery capabilities that allow for XML object manipulation in the data tier. It supports a statically typed subset of the XQuery 1.0 Working Draft of November 15, 2003.

DML Extensions

The XQuery specification currently contains syntax and semantics for querying, but not for modifying XML documents. The XML Data Modification Language (DML) is an extension to the XQuery features for data modification. SQL Server 2005 adds three keywords: insert, update, and delete. Each of these are used within the modify() method of the XML data type.

New Application Frameworks

SQL Server introduces a number of new or enhanced application frameworks that allow developers to significantly extend their application capabilities through judicious use of database services.

SQL Service Broker

Over the last 10 years, the proliferation of e-commerce applications has created a need for increased workflow management across database applications. When an online customer places an order for a book, this order needs to commit transactions into the inventory, shipping, and credit card systems, and also needs to send an order confirmation using another Web application. Waiting for each of these processes to happen in order doesn't scale well. SQL Server 2005 provides a new scalable architecture for building asynchronous message routing.

SQL Server 2005 introduces a new SQL Server application framework, Service Broker. Service Broker is a distributed application framework that provides reliable asynchronous messaging at the database-to-database level. The Service Broker technology allows internal or external processes to send and receive streams of reliable, asynchronous messages by using extensions to normal Transact-SQL data manipulation language. Messages are sent to a queue in the same database as the sender, to another database in the same instance of SQL Server, or to another instance of SQL Server either on the same server or on a remote server.

Reporting Services

With the release of SQL Server 2005, Microsoft extends a major new component of its integrated business intelligence (BI) platform. SQL Server Reporting Services expands the Microsoft business intelligence vision by making it easy to get the right information to the right people, in any business environment.

Reporting Services is a complete, server-based platform for creating, managing, and delivering traditional and interactive reports. It includes everything you need "out of the box" to create, distribute, and manage reports. At the same time, the modular design and the extensive application programming interfaces (APIs) of Reporting Services enable software developers, data providers, and enterprises to integrate reporting with legacy systems or third party applications.

Reporting Services ships with SQL Server 2005 and includes:

• A complete set of tools for creating, managing, and viewing reports.
• An engine for hosting and processing reports.
• An extensible architecture and open interfaces for embedding reports or integrating the solution in diverse IT environments.
• Client controls to bring the power of reporting to both Smart Client and Web applications.
• Report Builder, a tool that allows users to create reports based on subsets of data and easily deploy them to the enterprise server.

Notification Services

Microsoft SQL Server Notification Services is a platform for developing and deploying applications that generate and send notifications to users. Notifications are personalized, timely messages that can be sent to a wide variety of devices.

Notifications reflect the preferences of the subscriber. The subscriber enters a subscription to express an interest in information. For example, "notify me when the stock price of Adventure Works reaches $70.00," or "notify me when the strategy document my team is writing is updated."

A notification can be generated and sent to the user as soon as a triggering event occurs, or can be generated and sent on a predetermined schedule that the user specifies. The user's subscription specifies when the notification should be generated and sent.

Notifications can be sent to a wide variety of devices. For example, a notification can be sent to a user's mobile phone, personal digital assistant (PDA), Microsoft Windows Messenger, or e-mail account. Because these devices often accompany the user, notifications are ideal for sending high-priority information.

SQL Server Mobile Edition

SQL Server 2000 shipped with SQL Server 2000 Windows CE Edition, which is now SQL Server Mobile Edition. There are a number of new key features in SQL Server Mobile Edition that relate to developers:

• You can create a SQL Server Mobile Edition database on the desktop or on the device, directly from SQL Server Management Studio. You can also manipulate the schema of the SQL Server Mobile Edition database directly from Management Studio, regardless of whether the database resides on the mobile device or on the desktop. You can use SQL Server Management Studio to run queries that target a SQL Server Mobile Edition database on the device or on the desktop. You can also take advantage of new SQL Server Mobile Edition features, which include an XML showplan that is rendered in a GUI format just like native SQL Server, and the ability to use query hints to override the query optimizer in SQL Server Mobile Edition. For the first time, you can control the optimization plan on a device.
• You can now code against Data Transformation Services (DTS) objects to exchange data.
• The new SqlCeResult set is derived from the SQLResult set that is in SQL Server 2005. This allows SQL Server Mobile Edition to have a true scrollable, updateable cursor. It also allows binding to data objects that are on devices.
• You can code an application to synchronize data while leaving the main application open and you can have two separate applications access the same database on the device at the same time.
• You can get notifications that you can code into status bars that will give the synchronization status. Previously, there was no way to know how far synchronization status was, to notify users that a device had not stopped responding.
• You can maintain the small size of the database through a much more aggressive page reclamation policy.
• You can share parameterized query code with SQL Server syntax.

Language Enhancements

Transact-SQL Enhancements

Transact-SQL has long been the basis for all programmability of SQL Server. SQL Server 2005 provides many new language capabilities for developing scalable database applications. These enhancements include error handling, new recursive query capabilities, and support for new SQL Server Database Engine capabilities. The Transact-SQL enhancements in SQL Server 2005 increase your expressive powers in query writing, allowing you to improve the performance of your code and extend your error management capabilities. The continuous effort that is being put into enhancing Transact-SQL shows a firm belief in its significant role in SQL Server.

Recursive Queries and Common Table Expressions

A common table expression (CTE) is a temporary, named result set that can be referred to by a defining statement. In its simple form, you can think of a CTE as an improved version of a derived table that more closely resembles a non-persistent type of views. You refer to a CTE in the FROM clause of a query, similar to the way you refer to derived tables and views. You define the CTE only once, and you can refer to it several times in your query. In the definition of the CTE, you can refer to variables that are defined in the same batch. You can even use CTEs in INSERT, UPDATE, DELETE, and CREATE VIEW statements, similar to the way you use views. The real power of CTEs is in their recursive capabilities, however, where CTEs contain references to themselves. You use derived tables when you want to refer to a query result as if it were a table, but when you do not want to create a persistent view in the database. Derived tables, however, have a limitation that is relaxed by CTEs: you cannot define a derived table once in your query and use it several times; instead, you must define several derived tables with the same query. Alternatively, you can define a CTE once and use it several times in a query without persisting it in the database.

Non-recursive CTEs increase your expressive power. For each piece of code that uses non-recursive CTEs, however, you can usually write longer code that achieves the same results by using other Transact-SQL constructs, such as derived tables. The case is different with recursive CTEs. When a CTE refers to itself, it is considered recursive. Recursive CTEs are constructed from at least two query parts (or members, in recursive query parlance). One part is a non-recursive query part, also referred to as the anchor member (AM). The other part is the recursive query part, also referred to as the recursive member (RM). The query parts are joined by a UNION ALL operator into a single CTE.

PIVOT and UNPIVOT Operators

SQL Server 2005 offers two new relational operators, PIVOT and UNPIVOT, which you 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.

The PIVOT operator is useful for handling open-schema scenarios and for generating cross tab reports. In an open-schema scenario, you maintain entities with sets of attributes that are either not known ahead of time or are different for each entity type. Your application's users define the attributes dynamically. Instead of predefining many columns and storing many NULLs in your tables, you split the attributes into different rows and store only the relevant attributes for each entity instance. PIVOT allows you to generate cross tab reports for open-schema and other scenarios in which you rotate rows into columns, possibly calculating aggregations along the way, and presenting the data in a useful form.

The UNPIVOT operator performs the opposite operation to PIVOT, rotating columns into rows. It narrows the input table expression based on a pivot column. The UNPIVOT operator allows you to normalize data that has previously been pivoted.

APPLY Operator

With the APPLY operator, SQL Server 2005 allows you to refer to a table-valued function in a correlated sub query. The APPLY relational operator allows you to invoke a specified table-valued function once for each row of an outer table expression. You specify APPLY in the FROM clause of a query, similar to the way you use the JOIN relational operator. APPLY comes in two forms; CROSS APPLY and OUTER APPLY.

CROSS APPLY invokes a table-valued function for each row in an outer table expression. You can refer to columns of the outer table as arguments to the table-valued function. CROSS APPLY returns a unified results set, compiled out of all of the results that are returned by the individual invocations of the table-valued function. If the table-valued function returns an empty set for a given outer row, that outer row is not returned in the result.

OUTER APPLY is very similar to CROSS APPLY, with the addition that it also returns rows from the outer table for which the table-valued function returned an empty set. NULLs are returned as the column values that correspond to the columns of the table-valued function.

Exception Handling for Transactions

Earlier versions of SQL Server require you to include error-handling code after every statement that was suspected of error, similar to Microsoft Visual Basic 6.0. To centralize error-checking code, you had to use labels and GOTO statements. Furthermore, errors such as data type conversion errors caused your batch to terminate, so you couldn't trap those with Transact-SQL. SQL Server 2005 addresses many of these issues by introducing a simple but powerful exception handling mechanism in the form of a TRY/CATCH Transact-SQL construct, similar to that in Visual Basic .NET and C#. Errors that previously caused a statement, level, batch, or transaction to terminate can now be caught and handled, provided that those errors are not severe enough to cause severance of the connection.

To implement error handling, simply write the code that you want to execute within a BEGIN TRY/END TRY block, and follow with the error-handling code in a BEGIN CATCH /END CATCH block. Note that a TRY block must have a corresponding CATCH block; otherwise, you will get a syntax error.

DDL Event Notifications

SQL Server 2005 allows you to capture DDL and system events and send an event notification to a Service Broker service. As opposed to triggers that are processed synchronously, an event notification is an event mechanism that allows asynchronous consumption. An event notification sends XML data to a specified Service Broker service, and event consumers consume it asynchronously. An event consumer can wait for new data to arrive using extensions to the WAITFOR clause of the Service Broker Receive statement.

Full-Text Search Enhancements

SQL Server 2005 includes support for rich, full-text applications. Cataloging capabilities have been enhanced to provide greater flexibility over what is cataloged. Query performance and scalability have been improved dramatically, and new management tools provide greater insight into the full-text implementation.

Security Enhancements

SQL Server 2005 benefits from the Trustworthy Computing initiative, a Microsoft initiative to improve the experience of customers in the areas of security, privacy, reliability, and business integrity. As part of this initiative, which was introduced company-wide in January 2002, Microsoft now follows development processes that help to ensure that our products and product deployments are secure by design, secure by default, and secure in deployment. The Microsoft SQL Server development team incorporated those processes in the development of SQL Server 2005. After deployment, Microsoft supports ongoing customer and partner communications about security issues. The result is that SQL Server 2005 will incorporate the most extensive security features of any release of SQL Server to date.

Generally, these features and improvements fall into the following three areas:

• Restricting user access to the SQL server: Greater control on access to SQL Server, and investments in mechanisms that enable an administrator to control access to SQL Server through policies.
• Disabling services and restricting service configuration: Providing the ability for administrators to restrict access to resources within SQL Server, at an administrator's designated scope and at a fine degree of granularity, and to ensure that they have an easily manageable system without violating the principle of least privileges. Because certain services are disabled by default for new server installations, customers are now more actively involved in deciding which specific additional services they want to enable.
• Reducing the surface area of attack for new features: Starting with the installation and setup of SQL Server, the surface area of attack is minimized. Throughout the development cycle of the product, new features are reviewed and tested for security to help reduce the surface area of attack.
• In addition to all of these key server security enhancements, SQL Server 2005 also adds native data encryption. Using the certificate and key management system built into SQL Server and by utilizing some new system functions you are now able to encrypt and decrypt data on the server.

Conclusion

Microsoft SQL Server 2005 provides the tools that developers need to build new classes of database applications. By removing the barriers to code execution and storage location, and by integrating standards such as XML, SQL Server 2005 opens up a world of possibilities to the database developer. This paper is only an introduction to what you will be able to do with SQL Server 2005.