What are the Differences Between SQL Server 2000 and SQL Server 2005?
In part I of this series I looked at the administrative differences and in
this part I'll cover some of the development differences
between the
versions. I'm looking to make a concise, short list of
things you can tell a
developer who is interested, but not necessarily
knowledgeable about SQL
Server, to help them decide which version might be best
suited to meet their
needs.
And hopefully help you do decide if an upgrade is worth your
time and effort
One short note here. As I was working on this, it seemed
that there are a
great many features that I might put in the BI or security
space instead of
administrator or development. This may not be comprehensive,
but I'm looking
to try and show things from the main database developer
perspective.
The Development Differences
Developing against SQL Server 2005 is in many ways similar
to SQL Server
2000. Most all of the T-SQL that you've built against SQL
Server 2000 will
work in SQL Server 2005, it just doesn't take advantage of
the newer
features. And there are a great many new extensions to T-SQL
to make many
tasks easier as well as changes in other areas.
One of the biggest changes is the addition of programming
with .NET
languages and taking advantage of the CLR being embedded in
the database
engine. This means that you can write complex regular
expressions, string
manipulation, and most anything you can think of that can be
done in C#, VB
NET, or whatever your language of choice may be. There's
still some debate
over how much this should be used and to what extent this
impacts
performance of your database engine, but there's not denying
this is an
extremely powerful capability.
The closest thing to this in SQL Server 2000 was the ability
to write
extended stored procedures and install them on the server.
However this was
using C++ with all the dangers of programming in a low level
language.
However there are many new extensions to T-SQL that might
mean you never
need to build a CLR stored procedure, trigger, or other
structure. The main
extension for database developers, in my mind, is the
addition of the
TRY/CATCH construct and better error information. Error
handling has been
one of the weakest parts of T-SQL for years. This alone
allows developers to
build much more robust applications.
There are also many other T-SQL additions, PIVOT, APPLY, and
other ranking
and windowing functions. You might not use these very often,
but they come
in handy. The same applies to Common Table Expressions
(CTEs), which make
some particular problems very easy to solve. The classic
recursion of
working through employees and their managers, or menu
systems, have been
complex in the past, but with CTEs, they are very easy to
return in a query.
One of the other big T-SQL additions is the OUTPUT clause.
This allows you
to return values from an INSERT, UPDATE, or DELETE (DML)
statement to the
calling statements. In an OUTPUT statement, just like in a
trigger in SQL
Server 2000, you can access the data in the inserted or
deleted tables.
One of the programming structures that many developers have
gotten more and
more exposure to over the last decade is XML. More and more
applications
make use of XML, it's used in web services, data transfers,
etc. XML is
something I see developers excited about and with SQL Server
2005 there is
now a native XML data type, support for schemas, XPATH and
XQUERY and many
other XML functions. For database developers, there is no
longer the need to
decompose and rebuilt XML documents to get it in and out of
SQL Server.
Whether you should is another story, but the capabilities
are there.
There are a couple other enhancements that developers will
appreciate. The
new large datatypes, like varchar(max) allow you to store
large amounts of
data in a column without jumping through the hoops of
working with the TEXT
datatype.
Auditing is much easier with DDL triggers and event
notifications. Event
notifications in particular, allowing you to respond to
almost anything that
can happen in SQL Server 2005, can allow you to build some
amazing new
applications.
The last enhancement in T-SQL that I think developers will
greatly
appreciate is ROW_NUMBER(). I can't tell you how many times
I've seen forum
posts asking how to get the row number in a result set, but
this feature is
probably greatly appreciated by developers.
There are a number of other areas that developers will find
useful. Service
Broker, providing an asynchronous messaging system can make
SOA applications
a much easier to develop. Until now, this is a system that
appears easy to
build, but allows unlimited opportunities for mistakes.
Native web services
are also a welcome addition to allow you to extend your data
to a variety of
applications without requiring complex security infrastructures.
Reporting Services has grown tremendously, allowing more
flexibility in how
you deploy reports to end users. Integration Services is
probably the
feature that most requires development skills as this ETL
tool now really is
more of a developer than a DBA system. However with the
added complexity, it
has grown into an extremely rich and tremendously capable
tool.
There are other changes with SQL Server, ADO.NET has been
enhanced, Visual
Studio has been tightly integrated with it's extensions for
various features
as well as its influence on the Business Intelligence Design
Studio, and the
Team System for DB Pros. The Full-Text Search capabilities
have been
expanded and they work better, allowing integration with
third party
word-breakers and stemmers as well as working with noise
words.
Why Upgrade?
This is an interesting question. As with part I of this
series, I'm not
completely sure of how to recommend this. If your server is
running well as
an administrator, there's no reason to upgrade. As a developer,
however, it
s a bit more complicated.
Developers, almost by definition, are looking to change
things on a regular
basis. For developers, they are fixing things, enhancing
them, or rebuilding
them. In the first or even second case, it may not make much
sense to
upgrade if your application is working well. In the latter
case, I'd really
think hard about upgrading because a rebuild, or
re-architecture, takes a
lot of time and resources. If you're investing in a new
application, or a
new version of an application, then SQL Server 2005 might
make sense to take
advantage of the features of SQL Server 2005.
I'm guessing that many of these features will be around
through at least the
next two versions of SQL Server. While I can see there being
a radical
rewrite after Katmai (SQL Server 2008), I can't imagine that
many things won
t still be around in the version after that. They may get
deprecated after
that, but they should be there for that version, which
should see support
through 2018 or 2019.
If you are struggling with ETL, trying to implement
messaging, or web
services, then it also might make sense to upgrade your
database server to
SQL Server 2005.
A quick summary of the differences:
FeatureSQL Server 2000SQL Server 2005
Server Programming ExtensionsLimited to extended stored
procedures, which
are difficult to write and can impact the server
stability.The incorporation
of the CLR into the relational engine allows managed code
written in .NET
languages to run. Different levels of security can protect the
server from
poorly written code.
T-SQL Error HandlingLimited to checking @@error, no much
flexibility
Addition of TRY/CATCH allows more mature error handling.
More error_xx
functions can gather additional information about errors.
T-SQL LanguageSQL Language enhanced from previous versions
providing strong
data manipulation capabilities.All the power of SQL Server
2000 with the
addition of CTEs for complex, recursive problems, enhanced
TOP capabilities,
PIVOT/APPLY/Ranking functions, and ROW_NUMBER
AuditingLimited support using triggers to audit
changes.Robust event
handling with EVENT NOTIFICATIONS, the OUTPUT clauses, and
DDL triggers.
Large Data TypesLimited to 8k for normal data without moving
to TEXT
datatypes. TEXT is hard to work with in programming
environments.Includes
the new varchar(max) types that can store up to 2GB of data
in a single
column/row.
XMLLimited to transforming relational data into XML with
SELECT statements,
and some simple query work with transformed documents.Native
XML datatype,
support for schemas and full XPATH/XQUERY querying of data.
ADO.NETv1.1 of ADO.NET included enhancements for client
development.v2 has
more features, including automatic failover for database
mirroring, support
for multiple active result sets (MARS), tracing of calls,
statistics, new
isolation levels and more.
MessagingNo messaging built into SQL Server.Includes Service
Broker, a
full-featured asynchronous messaging system that has evolved
from Microsoft
Message Queue (MSMQ), which is integrated into Windows.
Reporting ServicesAn extremely powerful reporting
environment, but a 1.0
product.Numerous enhancements, run-time sorting, direct
printing, viewer
controls and an enhanced developer experience.
ETLDTS is a very easy to use and intuitive tool. Limited capabilities
for
sources and transformations. Some constructs, such as loops,
were very
difficult to implement.Integration Services is a true
programming
environment allowing almost any source of data to be used
and many more
types of transformations to occur. Very complex environment
that is
difficult for non-DBAs to use. Requires programming skills.
Full-Text SearchWorkable solution, but limited in its
capabilities.
Cumbersome to work with in many situations.More open
architecture, allowing
integration and plug-ins of third party extensions. Much
more flexible in
search capabilities.
Conclusion
These are the highlights that I see as a developer and that
are of interest.
There are other features in the security area, scalability,
etc. that might
be of interest, but I think these are the main ones.
I welcome your comments and thoughts on this as well.
Perhaps there are some
features I've missed in my short summary that you might
point out and let me
know if you think it makes sense to discuss some of the security
changes. As
far as BI stuff, hopefully one of you will send me some
differences in an
article of your own.
0 comments:
Post a Comment