Get started Bring yourself up to speed with our introductory content.

SQL Server 2005 beta testers' FAQ

Didn't get to test a SQL Server 2005 CTP? Not to fret! MVP Adam Machanic offers this collection of frequently asked questions posed by beta testers, along with his own expert answers, to help you better prepare for Microsoft's newest DBMS.

Whether or not you tested a pre-release version of SQL Server 2005, MVP Adam Machanic has compiled the most frequently asked questions posed by beta testers, along with his own expert answers, to help you better prepare for Microsoft's newest DBMS. The following is the first of a two-part series of SQL Server 2005 FAQs.

Frequently Asked Questions:

SQL Server 2005 beta testers

  1. Which tools will help me prepare for SQL Server 2005 migration?
  2. Will SQL Server 2005 offer benefits with no code changes?
  3. Will my SQL Server 2000 DTS packages work in SQL Server 2005?
  4. How do CLR routines perform compared to T-SQL routines?
  5. Is there a string concatenation or list aggregate in SQL Server 2005?
  6. Should I store my bus. objects as instances of CLR user-defined types?
  7. Can I write 2005 stored procedures without knowing T-SQL?
  8. Does SQL Server 2005 have any new index types?
  9. What is "row versioning" and how is it used in SQL Server 2005?
  10. How can I store XML data in SQL Server 2005?

1. Which tools will help me prepare for SQL Server 2005 migration?

Microsoft has provided the SQL Server 2005 Upgrade Advisor to help database administrators determine what will have to be done in order to upgrade to SQL Server 2005. This tool evaluates a variety of factors, including deprecated features and necessary changes to system configuration, in order to paint a clear picture of the overall readiness of the database system for SQL Server 2005.

The tool presents warnings in several categories, including Analysis Services, Data Transformation Services, Full-Text Search, Notification Services, Reporting Services, and of course the core Database Engine. DBAs hoping to upgrade their systems should take a look at this tool.

Return to beta testers' FAQs

2. Will SQL Server 2005 offer benefits with no code changes?

An emphatic yes! Of the many benefits of upgrading to SQL Server 2005, there are many that database administrators will get for "free" -- that is, without any change to code and with minimal change to system configuration. These benefits include every DBA's favorite type of enhancement: performance optimizations. The SQL Server Engine team describes some of the optimizations made to tempdb in this blog.

Many other enhancements also come with nothing more than a simple upgrade, including many features that will help in the areas of security and manageability. Don Vilen, SQL Server Program manager for Microsoft, details some "transparent" upgrade benefits, in this archived TechNet webcast.

Return to beta testers' FAQs

3. Will my SQL Server 2000 DTS packages work in SQL Server 2005?

DTS is on its way out in SQL Server 2005 and will be replaced by a brand-new Extract, Transform and Load (ETL) engine called SQL Server Integration Services (SSIS). SSIS provides a more powerful, flexible and better performing foundation for building ETL solutions than DTS, but this may leave many database administrators wondering what to do with their DTS packages.

The good news is, although they are no longer editable in SQL Server 2005, DTS packages created in SQL Server 2000 can still be run. To ease the transition, SQL Server 2005 provides an upgrade wizard for helping DBAs transition packages into the SSIS framework. However, not all components can be upgraded. ActiveX transforms, for instance, present a challenge for the upgrade wizard, and may not be able to be migrated.

While this means that DBAs will eventually have to re-write these packages using SSIS, doing so will probably be much easier than using DTS, thanks to the functionality that SSIS provides.

Return to beta testers' FAQs

4. How do CLR routines perform compared to T-SQL routines?

Here is the general performance rule when comparing T-SQL routines to equivalent CLR routines: Test both configurations with your data on your servers and figure out which one is better.

That said, many people have run performance tests and the general consensus is that T-SQL will always perform better for standard CRUD (Create, Read, Update, Delete) operations, whereas CLR code will perform better for complex math, string manipulation and other tasks that go beyond data access.

SQL Server MVP Gustavo Larriera has compiled the following list of useful links with more information on this topic:

Return to beta testers' FAQs

5. Is there a string concatenation or list aggregate in SQL Server 2005?

A very common question in Microsoft SQL Server forums is whether SQL Server 2005 has any kind of aggregate that functions similarly to SUM, but works over sets of strings. For instance, assume that a database has the following table and data:

  String VARCHAR(20) 


Such an aggregate might be used on this table in order to produce a list of strings:

FROM Strings


'A, B, C'

Although this aggregate is not built into SQL Server 2005, the new system introduces a way of easily achieving this functionality. The most obvious way might be to use the new CLR user-defined aggregates (UDAs). Unfortunately, UDAs have an 8000-byte limit, which severely limits their use when aggregating large sets.

Another method for achieving this in SQL Server 2005 is a byproduct of the new FOR XML PATH functionality. By specifying an empty path, it's possible to produce a string aggregation-like functionality:

SELECT String + ', ' AS [text()] 
FROM Strings

More information on this technique can be found on Aaron Bertrand's ASP FAQ site.

Return to beta testers' FAQs

6. Should I store my business objects as instances of CLR user-defined types?

Implementing a SQL CLR user-defined type (UDT) is as easy as adding a few additional pieces to a .NET class or structure. These include an attribute (SqlUserDefinedTypeAttribute), an interface (INullable) and a few additional methods of (Null() and Parse()). As a result of this simplicity, a skilled developer can convert a business object into a SQLCLR UDT in less than five minutes.

SQL Server 2005 was not designed to be used as an object-oriented database management system. It is still a standard SQL database management system DBMS, and the UDT capabilities should be thought of as a type of system extensions rather than objects. Developers should carefully weigh their options when deciding whether to use an existing business object as a CLR UDT.

Every time a method or property is accessed on an instance of a type, the instance must be deserialized before the method can be accessed. Because of this, it's best to rely on types that can be compared based on their serialized bytes. Developers should try to only use UDTs that can be used atomically to answer questions. For instance, the following C# class would probably not be a good candidate for a UDT:

class Product
   public string Name;
   public string Description;
   public decimal price;

If a query were written against a column of this type, each row would have to be de-serialized in order to answer a question such as, "What products cost $10.00?" This is because we can't assume that all $10.00 products have the same binary representation. Deserializing every row of a large table (i.e. a table with millions of products) could become a serious performance challenge.

Aside from the performance challenge there are normalization concerns. Given this type, for instance, how would a company store two descriptions for the same product, but ensure that the product only had a single valid price?

It is best to stick to types that can be used to answer questions without incurring the overhead of deserialization.

Return to beta testers' FAQs

7. Can I write 2005 stored procedures without knowing T-SQL?

As a result of Microsoft's ambitious marketing of SQL Server 2005's .NET integration over the last few years, many developers believe that T-SQL will no longer be necessary to create SQL Server stored procedures. Unfortunately (or not, depending on your point of view), this is only partially true. While it is technically possible to create a stored procedure without using T-SQL, it is not possible to do any data access without T-SQL.

Data access within CLR stored procedures is done using the standard ADO.NET classes. Developers will find that much of the same data access code usable in application tiers will be easily portable into SQLCLR routines. As these ADO.NET classes in the middle tier require T-SQL for data access, so do the same classes used within the context of the hosted CLR provider.

I noted that it is technically possible to write a T-SQL-less stored procedure. So is there any reason to do so? One case for this is a CLR stored procedure written to retrieve data from a flat file or Web service and format it into a rowset. That would be an operation that would not require T-SQL – but it's not a good comparison with the abilities of T-SQL stored procedures.

Return to beta testers' FAQs

8. Does SQL Server 2005 have any new index types?

SQL Server 2005 does not introduce new index types for relational tables. The basic -- clustered and non-clustered indexes implemented as B-trees -- still apply. However, SQL Server 2005 does include indexing enhancements both for Full Text Indexing and for XML data, as well as enhancements that ease some problems associated with relational indexing.

SQL Server 2005's Full Text Indexing feature is completely new and rewritten. For information on this feature, watch Nimish Khanolkar's archived MSDN webcast, Introducing Full-Text Search in SQL Server 2005.

XML has also undergone a drastic transformation in the way it is treated in SQL Server 2005. There is now a first-class XML data type available to developers. The type supports the XQuery query language, and columns using the type can be indexed using a special form of XML indexes. More information on the XML type can be found in this MSDN article.

A variety of enhancements have been made to the T-SQL relational indexing commands. Perhaps the most interesting of these is the new "online" indexing mode, which allows database administrators to perform index maintenance tasks without locking users out of tables. This hopefully marks the beginning of the end of database administrators having to wait for the 3:00 a.m. maintenance window to fix database issues! More information on this feature can be found in this SQL Server Worldwide Users Group article.

Return to beta testers' FAQs

9. What is "row versioning" and how is it used in SQL Server 2005?

"Row versioning" refers to the process of storing previous versions of rows in the tempdb database. The stored versions can be read from transactions that began before the row was updated. This is the basis for SQL Server 2005's new SNAPSHOT isolation level, which provides a highly concurrent, non-blocking behavior but guarantees that readers will see only consistent and committed data.

Many SQL Server database administrators are used to using "dirty" reads in the form of NOLOCK hints or the READ UNCOMMITTED isolation level, in order to achieve high concurrency in busy database systems. Using "dirty reads" exposes queries to the possibility of reading data that is not committed -- and cannot be trusted.

The SNAPSHOT isolation level does away with these issues by providing readers with a previous copy of the row being read if the row is not committed at read time. At the same time, no writers are blocked from updating the row and the reader gets a consistent view of only committed data. In turn writers do not block and are not blocked by other processes. For a thorough discussion of this feature refer to Kimberly Tripp's TechNet white paper on the topic.

Return to beta testers' FAQs

10. How can I store XML data in SQL Server 2005?

Much like CLR UDTs ( link to UDT FAQ ), XML is now a new first-class datatype in SQL Server 2005. Developers may be tempted to use this datatype in order to avoid having to write code to "shred" XML data into tables (i.e. instead of using OPENXML to populate tables with the data.) Unfortunately, using the XML datatype like this has many of the same problems as representing data in user-defined types. Developers should keep performance in mind, as querying a node of an XML column will require the engine to evaluate a separate XML query for each row of the table. Normalization issues are also present, just as when using CLR UDTs. Ensuring data integrity will be extremely difficult in databases that make too much use of the XML datatype.

Return to beta testers' FAQs

Adam Machanic is a database-focused software engineer, writer and speaker based in Boston, Mass. He has implemented SQL Server for a variety of high-availability OLTP and large-scale data warehouse applications, and also specializes in .NET data access layer performance optimization. He is a Microsoft Most Valuable Professional (MVP) for SQL Server and a Microsoft Certified Professional. Machanic is co-author of Pro SQL Server 2005, published by Apress.

Didn't find what you were looking for?

We invite you to pose a question to Adam or any one of our SQL Server experts.

Dig Deeper on Microsoft SQL Server 2005

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.