SQL Server 2000 and the Application Developer | Ado Examples and Best Practices

Team-Fly

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.

Scalability and Performance

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.

MSDE Changes

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.

Note

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.

Multiple Instancing

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.

Enhanced TSQL

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:

Table variables: These permit you to create in-memory variables that can be treated as you would any table. It also permits you to pass in-memory tables to and from user-defined functions or to and from other stored procedures. Typically, these are used in lieu of TEMPDB-based #Temp tables.
User-defined functions: To catch up with the Access SQL extensions, and make the transition from Access easier, SQL Server 2000 now supports user-defined functions.
Partitioned view enhancements: View functionality has been expanded to support view partitioning. This can be used to implement partitioned data across multiple tables in the same or different servers. The SQL Server 2000 query processor eliminates member tables not necessary for a given query or update.
INSTEAD OF Triggers: These triggers can be set up to fire before the ordinary Update, Insert, and Delete triggers, and the new AFTER triggers implemented in SQL Server 7.0. Triggers can also be created on a view or a table. You can also order the execution of triggers so you can determine which fires first and which fires next.
New data types:
- sql_variant can store any of the base SQL Server types (except BLOB types). These are useful for sparse-table or object annotations.
- bigint is an 8-byte integer used to store much larger integer values. This is supported by a COUNT_BIG() function, which is used to return bigint row counts. When used in an Identity column, it means you can have even larger tables.
Database/column level collations: In SQL Server 7.0, the entire server had a single collation sequence: code page and sort order. In SQL Server 2000, each database can have a separate, default collation. In addition, each column in a table can have a separate collation. SQL Server 2000 collation encompasses code-page plus sort order. This means SQL Server 2000 can have a default collation sequence as well as different operational sequences based on how you code the query.
Miscellaneous enhancements: SQL Server 2000 includes a number of new functions to support many oft-needed operations, including the following:
- GetUTCDate(): Current UTC time. This lets you program against "Zulu" or Greenwich Mean Time instead of local time.
- SCOPE_IDENTITY(): Last identity value in current scope (no triggers). This is a very cool new function that lets you trap the new identity value generated by the server, based on the last insert—the identity value returned does not get altered by triggers that fire after the local insert.
- IDENT_CURRENT('tblname'): Last identity value generated for table. This is yet another way to trap the identity generated—also very cool.
- CONTEXT_INFO(): A per-session value that can be set with SET CONTEXT_INFO. This is interesting for Web-based designs trying to track session-based context information.

XML Integration

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)^[2] 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:

 http://IISServer/biblio?sql=SELECT+Author,Au_Lname+FROM+Authors+FOR+XML+AUTO

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.

^[2]The March 2000 issue of MSDN magazine has an in-depth discussion of SQL Server 2000, SOAP, and how these emerging technologies play together.

Team-Fly