Features of Yukon (SQL Server 2005)

.NET Framework Integration <o:p>

<o:p> 

The most significant new feature in the SQL Server 2005 release is the integration of the Microsoft .NET Framework. The integration of the CLR with SQL Server extends the capability of SQL Server in several ways.

While T-SQL, the existing data access and manipulation language is well suited for set-oriented data access operation, It also has limitations. T-SQL is a procedural language, not an object-oriented language. The integration of  the .NET CLR with SQL Server 2005 enables the development of stored procedures, user defined functions, triggers, aggregates and user defined types using any of the modern object oriented .NET languages like VB.NET and C#. <o:p>

<o:p> 

While these languages do not have the same strong set-oriented nature as T-SQL, but these .NET languages support complex logic, have better computation capabilities, provide easier access to external resources and facilitate code reuse. The SQL Server 2005 data base engine hosts the CLR in-process. Using a set of APIs, the SQL Server engine performs all of the memory management for hosted CLR programs. <o:p>

<o:p> 

<o:p>  

Native XML Data Type

XML(eXtensible Markup Language) is the well known buzzword. XML is a widely used language for data transfer. Microsoft .NET Framework consumes XML as .NET <st1:place>Meta language and it is an integrated part of architecture of many .NET technologies like <st1:city><st1:place>ADO .NET, web services, remoting etc. XML works to enable transport for inter-application remote procedure calls(RPC) via SOAP (Simple Object Access Protocol).

In the release of SQL Server 2000, Microsoft first added XML support by introducing the For XML clause to be a part of SELECT statement and also Open XML function. XML support with SQL Server 2000 leaded towards integration of XML documents with relational data of SQL Server 2000, and this integration had some limitations. If some XML data is stored in SQL Server 2000 which is using either text or image data type, then developers had limited functionality to deal with this type of data. SQL Server 2000 was unable to execute native queries against XML document’s hierarchical data, and in order to do that SQL Server developer was supposed to write complex T-SQL code.<o:p>

<o:p> 

Microsoft SQL Server 2005 database engine has well integrated XML support. This support offers a new level of unified storage for XML and relational data. New XML data type provides support for native XML queries and strong data typing which associates XML data type to an XSD(eXtensible Schema Definition). The integrated bi-directional mapping between XML and relational data provides support for triggers on XML, replication and bulk load of XML data, and data access support via SOAP.<o:p>

<o:p> 

DDL Triggers <o:p>

<o:p> 

Trigger is a stored procedure which executes automatically when there is a DML (Data Manipulation language) operation such as Insert, update and delete. Microsoft SQL Server 2000 and earlier versions were confined to this functionality of trigger I.e. execution/association of trigger with DML statements.<o:p>

<o:p> 

SQL Server 2005 allows developers to write triggers for DDL (Data Definition Language) operations. Hence, DDL triggers can be associated with creating, altering and dropping database objects such as tables, views, stored procedures and logins.<o:p>

<o:p> 

This new DDL Trigger support of SQL Server 2005 allows database developers and administrators to control the use of Create, Alter and Drop statements over database objects.<o:p>

<o:p> 

Index Enhancements<o:p>

<o:p> 

In SQL Server 2000, rebuilding a clustered index, was forcing all of the related non-clustered indexes also to be rebuild. SQL Server 2005 doesn’t support this feature anymore I.e. rebuilding clustered index no longer force non-clustered indexes to be rebuilt. <o:p>

<o:p> 

SQL Server 2005 also allows adding a Non-Key column to an index. As previous versions of SQL Server and almost all databases allow only key columns to be a part of index and so there is limited query support which can take advantage of indexes. This new feature allows more queries to get executed by taking full advantage of indexes.<o:p>

<o:p> 

The maximum size of an index is 900 bytes as previous versions of SQL Server, which only allows key columns to be part of an index under the size limit of 900 bytes. SQL Server 2005 allows non-key columns to be indexed, without becoming part of the key and so these columns don’t consume space in an index like key columns as maximum size of an index is still 900 bytes.<o:p>

<o:p> 

Another new feature of SQL Server 2005 is ability to disable an index. Disabling an index stops that index to be being maintained and used by SQL Server engine. When an index is disabled, SQL Server 2005 database engine de-allocates the storage space used by an index but maintains the meta-data of that index. Alter Index statement is used to rebuild an index and only after that, disabled index can be enabled again.<o:p>

<o:p> 

Previous versions of SQL Server didn’t allow any access to an index while that index was rebuilt. Developers needed to wait until the rebuilt process completed and so database objects can get updated. SQL Server 2005 also allows online index operations, which enables applications to access the index and perform database operations like insert, update and delete on a table while the index is rebuilding.