<o:p>Features of <st1:state><st1:place>Yukon (SQL Server 2005)
Applies to:
Microsoft® SQL Server 2005™<o:p>
While reading this article you may feel that that the contents of this article are copeid from some Microsoft site. But actually I have designed the structure of this article like MSDN articles, because I really like the flow of MSDN articles.
No information is taken from Microsoft or any other website. Whatever is written in this article is my effort to share my understanding about Yukon with other .NET aspirants.
Summary: This article provides an overview of new feature support built into Microsoft SQL Server 2005. Just for a quick byte, SQL Server 2005’s most exciting feature is .NET Framework integration, which will be detailed in later section of this article. For some features, this article assumes that the reader is familiar with SQL Server 2000 features and services.
Contents<o:p>
Introduction<o:p>
Enhanced Hardware Support<o:p>
Native 64-bit Support<o:p>
Hyper-Threading
.NET Framework Integration<o:p>
Native XML Data Type<o:p>
DDL Triggers<o:p>
Index Enhancements <o:p>
<o:p>
Introduction
<o:p>
<st1:state><st1:place>Yukon is the code name of new revolutionary version of Microsoft SQL Server; SQL Server 2005. SQL Server 2005 provides vast range of new features and a lot of improvements, which SQL Server 2000 aspirants were always looking for.
<o:p>
The features supported by SQL Server 2005 serves three grounds of any enterprise environment:
<o:p>
ü Database Administration<o:p>
ü Database Development<o:p>
ü Business Intelligence<o:p>
<o:p>
Enhanced Hardware Support<o:p>
Any running enterprise database system gets affected by the type of hardware and running operating system platform. Enhancements made in SQL Server 2005 enable it to take advantage of new generations of hardware and processors. Building on Windows Server 2003 platform, SQL Server 2005 provides 64-bit support for both the Intel Itanium-64 architecture and AMD x64 architecture. <o:p>
<o:p>
Native 64-bit Support<o:p>
<o:p>
When running on Windows Server 2003 for 64-bit Extended Systems, SQL Server 2005 supports AMD’s 64-bit Opteron and Athlon 64 processors as well as Intel’s Xeon with Intel Extended Memory 64 Technology (EMT64).<o:p>
<o:p>
SQL Server 2005 fully supports both 32-bit and 64-bit hardware platforms for all of its major services, including the SQL Server Engine, Analysis Services, SQL Agent and Reporting Services.
<o:p>
Benefits achieved in moving a database to the 64-bit platform isn’t faster processing, rather the real advantage is in the vastly increased addressable memory<o:p>
The native 32-bit architecture is limited to a maximum of 4GB addressable memory, which is divided in two pieces, 2GB is reserved for Windows operating system and the remaining 2GB for applications. Using the Advances/Address Windowing Extensions (AWE) support found in the 32-bit version of Windows can address a maximum 32GB of RAM. The native 64-bit implementation eliminates this memory constraint by raising the maximum addressable memory to 32TB.<o:p>
<o:p>
Hyper-Threading<o:p>
<o:p>
SQL Server 2005 can take advantage of hyper-threading. Hyper-threading is a CPU technology by Intel in which each physical processor in system creates two logical processors. Each logical processor is capable of simultaneously executing separate threads. The goal of hyper-threading is to provide better resource consumption for multithreaded applications running on a single machine. <o:p>
.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).
<o:p>
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.
