While it's too early to build the list of things that'll change, work better, or simply break when you install SS2K (and thus ADO 2.6), it's not too early to build a list of cool things to fold into existing designs. You might be in the process of inventing the same technology implemented in the newest versions of ADO or in SQL Server itself. The following list by no means includes all of the new features. It does, however, list features that'll directly impact your existing and future development work.
When the SQL Server group announces to the world that their latest offspring runs faster and does more cool stuff, I'm perhaps not as excited as they are. It's kinda like when Ford announces that their new Explorer can carry more gravel or gators and burn less gas. While I've been known to buy a new truck now and again, I'm partial to the one I have. But when I get the new model, these features will be there whether I need to carry gravel (or gators) or not. What I'll be looking for is how the features I use every day work. I'll appreciate better gas mileage, but will I have to give up my tape collection because they dropped support for cassette tapes in favor of CDs? Will I have to relearn how to program the seats, the radio, or the door locks? In other words, while those new features are cool and will help me do what I do with the truck, unless the interfaces are about the same, I'm in for a number of hours of wasted time trying to figure out how to get the headlights to turn off.
Yes, SS2K is faster and there's nothing wrong with that. Anything that lets your clients and me spend less time staring at an hourglass cursor is good. However, a faster engine won't improve my development time as much as fast, and functional (bug free) data access interfaces. If ADO is designed to take advantage of these features, well, okay. But unless the Visual Studio tools get a lot smarter (and a lot faster) we won't be able to leverage or even access these new features with the existing tools for quite some time—perhaps not until after my birthday in the year 2001 when Visual Studio 7.0 arrives.
SS2K also includes (in all SKUs) a version of MSDE designed to run on workstation (or Professional) NT boxes, as well as on selected Windows 9x systems. Remember that the Microsoft "Desktop" Database Engine (MSDE) is SQL Server. However, I was somewhat startled to learn that SS2K no longer supports MSDE on Windows 95 as did its SQL Server 7.0 predecessor. While this is somewhat understandable, I suspect that this will force many of you to take another look at MSDE and perhaps hesitate to move to it. On the other hand, the alternatives (Jet and who knows what else) are not nearly as attractive as moving your clients to Windows 98.
MSDE inherits most of the performance enhancements you get with SS2K. However, it's still governed at five simultaneous operations. No matter how many connections you open, once MSDE has five operations running, SQL Server slows down all connections to discourage you from using MSDE as a full-blown server.
MSDE can also be installed multiple times—just as Jet can. For a better understanding of what that means, keep reading. However, the database format has changed. This means if you use sp_detach_db and sp_attach_db to install your customer database, you'll find SQL Server 2000 reformats the database (automatically) when attached to the most current version. Once you detach from SS2K, the database files cannot be reattached to a SQL Server 7.0 database engine.
While the marketing materials don't say so, the database structures have changed again. Not a single session I attended at the latest SQL Server 2000 conference discussed this issue, but it can be very important for those using sp_detach_db to deploy their databases.
One of the more serious SQL Server limitations has been its inability to run multiple versions at the same time. There are a number of viable scenarios in which this is a fact of life. For example, if you use MSDE as your client database (as suggested) and you install the application on a system that already has another MSDE application, or simply on a system running SQL Server, the application cannot install. In addition, because the application often sets the MSDE SA password, it won't be able to access the existing MSDE (or other version of SQL Server).
This all changes with SS2K, because it permits you to install as many copies of SQL Server as you need. Each is loaded in a separate process and can be individually named so they can run independently. This means you can run multiple copies of MSDE, or you can run MSDE alongside SQL Server 7.0 or SQL Server 2000.
Again, this is not a book about SQL Server, but when working with ADO and SQL Server there are a number of techniques that you may use ADO to solve that are now best solved by the backend server—at least if you're using SQL Server. SS2K now supports a number of new TSQL primitives and other enhancements:
We've touched on XML over and over as we marched through the chapters in this book. Based on the number of articles, books, and conference sessions alone (not to mention the internal Microsoft hype), XML is the new golden boy at Microsoft. When coupled with HTML, it's being reborn (while it's still in its infancy) as Simple Object Access Protocol (SOAP). Simply put, SOAP is becoming a replacement for DCOM and CORBA as a new standard way to communicate between systems. This by itself is the topic for another book. But consider that Microsoft is tightly integrating XML directly into SQL Server 2000 in an effort to help developers build the next generation of Web and Enterprise applications. Their Web site (http://msdn.microsoft.com/workshop/xml/articles/xmlsql/sqlxml_prev.asp) sports a way to try out this innovation on SQL Server 7.0. This new technology provides a more-or-less direct URL access to SQL Server. This means you can author queries to be sent directly to SQL Server 7.0 via a URL, with the results being returned as XML-formatted documents. SQL Server 2000 is fully XML-enabled and includes a superset of the features available in the technology preview for SQL Server 7.0.
This technology is implemented as an IIS ISAPI extension that provides HTTP access to SQL Server and XML data formatting and updating capabilities. With the appropriate configuration, SS2K (and the technology preview) allows URL queries similar to this:
In this case, we're simply pointing the browser (not ADO) at a database (Biblio) on our SQL Server/IIS Server and asking SQL Server to execute the query and return XML. The layout of the XML returned could be specified in a variety of ways (including a useful Auto mode) and there is the option to include schema information either in document type definition (DTD) or XML-Data formats. This means you can specify an XSL style sheet along with the SQL query to make sure the XML returned is formatted correctly for the client. One special point to note: the virtual root name you choose (for example, Biblio) can be used only for accessing your SQL data. Any other Web files (such as .htm or .asp files) are not accessible from this virtual root. This space is reserved for so-called template files that contain "canned" queries.
All of this means that it's not necessary to write a single line of ADO, ODBC, or OLE DB code in your application. Now that's an important change.
The March 2000 issue of MSDN magazine has an in-depth discussion of SQL Server 2000, SOAP, and how these emerging technologies play together.