Datasheet

are inefficient as far as memory and CPU consumption are concerned. They do this because it is the easi-

est and quickest way to finish the code. I agree that there are times when a quick solution is the best, but

future performance must always be taken into account. This book tries to show you the best way to write

T-SQL so that you can avoid writing code that will bring your server to its knees, begging for mercy.

What’s New in SQL Server 2005

Several books and hundreds of web sites have already been published that are devoted to the topic of

“What’s New in SQL Server 2005,” so I won’t spend a great deal of time describing all the changes that

come with this new release. Instead, throughout the book I will identify those changes that are applica-

ble to the subject being described. However, in this introductory chapter I want to spend a little time dis-

cussing one of the most significant changes and how it will impact the SQL programmer. This change is

the incorporation of the .NET Framework with SQL Server.

T-SQL and the .NET Framework

The integration of SQL Server with Microsoft’s .NET Framework is an awesome leap forward in

database programming possibilities. It is also a significant source of misunderstanding and trepidation,

especially by traditional infrastructure database administrators.

This new feature, among other things, allows developers to use programming languages to write stored

procedures and functions that access and manipulate data with object-oriented code, rather than SQL

statements.

Kiss T-SQL Goodbye?

Any reports of T-SQL’s demise are premature and highly exaggerated. The ability to create database pro-

gramming objects in managed code instead of SQL does not mean that T-SQL is in danger of becoming

extinct. A marketing-minded executive at one of Microsoft’s partner companies came up with a cool

tagline about SQL Server 2005 and the .NET Framework that said “SQL Server 2005 and .NET; Kiss SQL

Good-bye.” He was quickly dissuaded by his team when presented with the facts. However, the execu-

tive wasn’t completely wrong. What his catchy tagline could say and be accurate is “SQL Server 2005

and .NET; Kiss SQL Cursors Good-bye.” It could also have said the same thing about complex T-SQL

aggregations or a number of T-SQL solutions presently used that will quickly become obsolete with the

release of SQL Server 2005.

Transact-SQL cursors are covered in detail in Chapter 10, so for the time being, suffice it to say that they

are generally a bad thing and should be avoided. Cursors are all about recursive operations with single

or row values. They consume a disproportionate amount of memory and CPU resources compared to set

operations.

With the integration of the .NET Framework and SQL Server, expensive cursor operations can be replaced

by efficient, compiled assemblies, but that is just the beginning. A whole book could be written about

the possibilities created with SQL Server’s direct access to the .NET Framework. Complex data types,

custom aggregations, powerful functions, and even managed code triggers can be added to a database to

exponentially increase the flexibility and power of the database application. Among other things, one of

the chief advantages of the .NET Framework’s integration is the ability of T-SQL developers to have

complete access to the entire .NET object model and operating system application programming interface

(API) library without the use of custom extended stored procedures. Extended stored procedures and

Introducing Transact-SQL and Data Management Systems

04_57955x ch01.qxd 9/19/05 12:43 PM Page 3