SQL Server 7: Is It for You?


Embedded, desktop, and server versions make SQL Server 7.0
the logical choice for scalable VB database apps.

by Ken Spencer

Reprinted with permission from Visual Basic Programmer's Journal, 5/98, Volume 8, Issue 6, Copyright 1998, Fawcette Technical Publications, Palo Alto, CA, USA. To subscribe, call 1-800-848-5523, 650-833-7100, visit www.vbpj.com, or visit The Development Exchange at www.devx.com

In the midst of agony at Informix, excuses from Oracle, and downsizing by Sybase, Microsoft's SQL Server 6.x sales just keep on growing. So what's the incentive for Microsoft to completely overhaul its budget-priced, client/server, database back-end? Like Windows, the objective is "SQL Server Everywhere." Microsoft has made SQL Server 7, the database formerly known as Sphinx, the first client/server RDBMS to span the entire gamut of Windows platforms, ranging from Win95 laptops to clustered NT Enterprise servers with dozens of Pentium II processors running multiterabyte data warehouses. Independent software vendors get an embeddable SQL Server 7, sans management tools and documentation, to compete with Pervasive Software's venerable Btrieve engine. But the biggest news is that the single-user variant of SQL Server 7 will become an "alternative engine for Access," offering millions of Office Professional users the dubious opportunity to substitute SQL Server 7 for the latest and greatest version of Jet. (For a quick look at SQL Server 7, see the sidebar, "SQL Server Pros and Cons.")

SQL Server Pros and Cons
Migrating to SQL Server 7.0 brings you great new features, but you'll find a few gotchas and warts lurking under the covers. Here's my take on what's hot and not-so-hot in Microsoft's newly minted RDBMS.

What's Hot

  • Unix-style devices give way to conventional operating system files for data and log segments. Data and log files expand and contract automatically; you can forget having to expand DAT files periodically. This feature alone justifies the upgrade from earlier versions.
  • Enterprise, multiuser, single-user, and embedded incarnations of SQL Server 7 share the same source code base. All SQL Server 7 variants are 100 percent compatible and offer almost identical sets of server tools.
  • Automatic memory management and database tuning eliminate the need for DBA skills for single-user and small workgroup installations. Use sp_configure if you want to override SQL Server 7's autotune settings.
  • Performance improvements abound. 8K pages and 64K extents speed up file I/O operations. Dbcc runs remarkably faster than in prior versions. Live backup operations now have a minimal effect on concurrent database operations.
  • The query planner takes the least-cost approach to INSERT and UPDATE locking, both of which are supported at the row level. Depending on the number of locks required, the planner escalates locking from the row level to the page or table level.
  • OLE DB lets SQL Server's query engine deal with heterogeneous joins that include tables from any record source having an OLE DB provider. SQL Server users now get features that Access developers have had for several years.
  • Data Transformation Services (DTS) adds an OLE DB front end to BCP, dramatically speeding up imports of a wide range of relational and other tabular data. You can also quickly export SQL Server data to other RDBMSs and common tabular file types.
  • A multitude of wizards aid new users in performing operations such as creating new databases, adding tables and indexes, and importing/exporting data. Experienced DBAs, however, will miss SQL Server 6.5 Enterprise Manager's more direct, menu-driven approach for many routine management chores.
  • Replication operations are streamlined by improved multisite update replication capabilities, less contention on replication tables, and optional replication of stored procedure execution. An anonymous subscriber account lets large numbers of disconnected users update tables over the Internet.
  • Switching between SQL Server 6.5 and 7 installed on a single server is simple and fast. Choose Microsoft SQL Server - Switch (Common) from the Start menu's Programs choice, then choose Microsoft SQL Server to toggle the version and its corresponding toolset. The entire process takes less than 15 seconds.
  • Data warehousing types get terabyte databases, automatic load-dependent memory management, parallel I/O and read-ahead, and secondary index optimization. Plato enhances online analytical processing (OLAP) operations.

    What's Not

  • SQL Server 7's new database and log file system isn't compatible with the device (DAT) files of prior versions. Upgrading requires a complete rebuild of existing databases, so add a few 9 GB ultrawide SCSI-3 drives to your upgrade budget. Fortunately, the cost of high-performance disk space is continuing to decline, and you always can use more disk space. Dumping to and restoring from tape is a much more laborious process.
  • SQL Server 7's MMC-based Enterprise Manager can't connect to SQL Server 6.x databases because of SQL-DMO incompatibilities. You're stuck with running two distinctly different sets of database tools until conversion is complete. Microsoft's Casey Kiernan says it's "not a problem." Au contraire, Casey, it's a major pain.
  • Transact-SQL doesn't support the SQL-92 CASCADE reserved word needed to provide cascading updates and deletions with declarative referential integrity (DRI) statements. Users of Jet's cascading update and deletion features will need to write SQL Server triggers. Even with SQL Server's new trigger features, this isn't an easy chore.
  • Access 97's Upsizing Wizard doesn't work with SQL Server 7.0. After you specify the tables to upsize and choose your options, clicking on Finish results in a bogus "Upsizing Complete!" message. A quick check of the Upsizing Report or your destination database confirms that nothing at all happened. Your only choice is to upsize to SQL Server 6.x, then convert the database to SQL Server 7 format.
  • Win9x users must start SQL Server 7 manually. The usual net-start mssqlserver command line doesn't work in Win9x. Make sure all your single-user apps include an ADODB.Connection.Open loop to remind users to start SQL Server 7 (see Listing 1).
  • Microsoft's Internet mania causes inappropriate HTML and gratuitous graphics to appear in Enterprise Manager's right pane. In the left pane, double-click on one of the Database objects, such as Pubs, for an egregious example. It won't be long until some enterprising hacker discovers how to send X-rated site links directly into Enterprise Manager.
  • SQL Server 7.0, like most other current Microsoft products, requires Internet Explorer 4.0 to function. IE4 installs a great deal of superfluous code on your server. It's lots of fun watching IE4 establish customized user settings for channels, configure Outlook Express and the like, then pop up welcome screens when you log on as a different user. I don't want to "Subscribe to channels" or "Explore to win" from a server (or, for that matter, from a client). Will NT 5.0 Server add Broadcast Architecture so DBAs can watch TV in the server closet?

    R.J.


  • I've used every version of Microsoft SQL Server since the late 1980s, when it was known as "The Sybase Database Server for PC Networks" and ran under Microsoft's very bland flavor of OS/2. I've been a technical beta tester for every Windows NT version of SQL Server, beginning with 4.21a on NT 3.1 Advanced Server. In my view, the transition from SQL Server 6.x to 7.0 involves more fundamental changes to the product than all previous upgrades combined. Many of the new features were missing documentation in Beta 2, so I spent two days at Microsoft's SQL Server 7 Technical Reviewer's Conference to get Microsoft's slant on the new SQL Server architecture. (I also got the release from my beta NDA needed to write this article.)

    Paul Flessner, Microsoft's general manager of SQL Server development, opened the conference by saying, "SQL Server 7 is the largest non-1.0 release in the industry." Members of the SQL Server 7 development team went on to project hundreds of PowerPoint slides, interspersed with a few demos, to prove Flessner's statement. But SQL Server 7 also is one of the most delayed non-1.0 releases; back in May 1996, the product was scheduled to reach the market in the second quarter of 1997. The current schedule calls for Beta 3 of SQL Server 7 to be dropped on more than 30,000 MSDN Universal subscribers in the second quarter of 1998 and released to manufacturing (RTM) in the second half. Microsoft promises a "major [SQL Server 7] push at Tech·Ed in June," and I won't be surprised if Tech·Ed attendees also receive a copy of Beta 3. This article's illustrations and code examples use an "almost feature-complete" Beta 2 of SQL Server 7 running under Win95, Win98 Beta 3, WinNT 4 SP3 and Mini-Service Pack (on the Sphinx Beta CD-ROM), and WinNT 5 Beta 1.

    One Size Fits All (Almost)
    Scaling SQL Server down to the desktop is a greater challenge than scaling it up in the enterprise. Microsoft wisely decided to use the same source code base for the three SQL Server 7 variations: single-user/embedded, conventional server, and enterprise. Each build generates Win9x and WinNT binaries, both of which use the same data/log file structure and management toolset. (Some features of the SQL Server 7 Enterprise Manager, however, don't work in the Win9x variant.) Every Access power user who decides to use SQL Server instead of Jet gains Microsoft-only client/server experience by osmosis. Migrating database apps from the desktop to the server involves little more than changing the Location= entry of the ADODB.Connection string and buying SQL Server client licenses. SQL Server's major low-end competitors, Oracle Lite and Sybase SQL Anywhere, don't share a common source code bond with their server-side counterparts; in fact, they differ quite markedly. Score a hit for cross-platform compatibility, assuming all your platforms are Windows.

    SQL Server traditionally has been relatively easy to set up and administer; that's always been one of its major selling points. But involving desktop users with chores such as specifying the size of Master.dat and dealing with fixed-size database and log devices requires at least a modicum of DBA skills. Fixed-size device files are a holdover from the 15-year Unix heritage of the Sybase database engine. To eliminate the need to train desktop and laptop users as DBAs, as well as to take full advantage of NT security and File System (NTFS) for servers, Microsoft eliminated DeviceName.dat files. Databases now begin as a pair of conventional files, DatabaseName_Data.mdf and DatabaseName_Log.ldf, with default sizes of 4 MB and 2 MB, respectively. The minimum MDF file size is 3 MB in order to accommodate tables copied from the Model database. The MDF and LDF files grow automatically as you add data. More interesting, they shrink automatically as data and log space is freed, eliminating Jet's need for compacting databases periodically to recover space occupied by deleted records. File groups let you add space to MDF and LDF files from other drives or partitions. Unlike DAT files, which can store more than one database, the MDF/LDF pair is dedicated to a single database.

    Memory management is a feature that's especially critical on the desktop and laptop. Microsoft says, "the 1998 typical laptop is increasingly 233 MHz Pentium MMX, over 3 GB disk, and 64 MB memory." This may be Microsoft's 1998 laptop spec, but most desktop and laptop users I know are running at 150 MHz to 200 MHz with a 1 GB or 2 GB drive, and 32 MB of RAM. The engine development team's original goal was to match Jet 3.5's memory footprint, about 3.5 MB. Microsoft's Ron Soukup says that single-user SQL Server 7 "isn't as small as Jet, but it's in the same range," with a working set under 4 MB. One of the keys to improved memory management is a single, unified buffer pool. SQL Server 7 autoconfigures memory usage on startup, expands the pool to accommodate more database users, and responds to operating system pressure by contracting the pool when necessary. DBAs will appreciate that it's no longer necessary to specify a maximum number of simultaneous connections. Microsoft claims to have eliminated 22 manual tuning parameters with the new autotuning approach.

    Upgrade Drudgery
    SQL Server 7 substitutes 8K for 2K pages, and 64K for 16K extents, making it possible for a single record to contain about 8000 bytes. The change in page size and the file structure modification means that conversion from earlier versions requires a complete makeover of all database files, not just an in-place DAT file update. Microsoft gives you row locking for both INSERTs and UPDATEs as partial compensation for the upgrade nuisance. (Most OLTP DBAs won't accept 8K page locks without a full row-locking option.) The new query planner performs an analysis to determine the least-cost locking scheme. If an OLTP app modifies only a few rows at a time, row locking is fine. However, if an "update from hell" needs to increase 20,000 unit prices by 10.5 percent, requiring tens of thousands of row locks, the query planner escalates locking to the page or table level.

    Pipelining and offlining are the two basic paths to SQL Server 7 upgrades. Pipelining uses a named-pipe connection between the two RDBMSs and requires sufficient free disk space to install SQL Server 7 (about 130 MB) and duplicates of your existing databases. Offlining means dumping your 6.x databases to tape, dropping the databases and their devices, then restoring from tape to SQL Server 7 databases. Offlining sounded like too much trouble to me, so I freed up as much space as possible on the OAKLEAF0 NT 4.0 main server to pipeline a conversion of my relatively small nwind and OLTP databases, derived from the Northwind database of Access 97 and Access 2, respectively.

    First, I set up a new account in the Administrator's group and logged on as SQLS70, not Administrator, to enable dual-version operation. Installation of an NT mini-service pack and SQL Server 7 on OAKLEAF0 took about 20 minutes, about 15 minutes of which was unattended. On completion, my Microsoft SQL Server 6.5 Start menu choice changed to SQL Server - Switch (Common) with two subchoices, Microsoft SQL Server 6.5 and Version Upgrade Wizard. After specifying upgrade options and the destination location in a few dialogs, the Wizard began the upgrade process. Watching the progress of the exceedingly complex upgrade operation made it clear that wizardless manual upgrading isn't in the cards. The Version Upgrade Wizard created nwind_Data.MDF, nwind_Log.LDF, oltp_Data.MDF, and oltp_Log.LDF in six and a half minutes. All indexes, triggers, stored procedures, and other database objects migrated perfectly. Pipelined upgrades of large databases reportedly run at 1 GB to 4 GB per hour on fast, multiprocessor servers (OAKLEAF0 has one 133 MHz Pentium). Microsoft claims the new database file system is "a solid foundation for the next 10 to 20 years," so future upgrades are likely to be much simpler.

    Off to See the Wizard(s)
    A Microsoft Management Console (MMC) snap-in replaces SQL Server 6.x's Enterprise Manager, which should surprise no one. I like the idea of a uniform UI for all management tools, but so far I'm underwhelmed by the Beta 2 implementation of Enterprise Manager (see Figure 1). The worst defect is that the MMC plug-in won't handle both 6.x and 7.0 servers because of SQL-DMO incompatibilities. (You can register a 6.x server, but not manage it.) The Manage ObjectType menu choices of version 6.x succumb to a collection of 18 wizards that you access from the Help menu, although some operations also are accessible from the Action menu and its Tasks submenu. Wizards are fine for multistep operations, but I'd like more direct access to single-step actions, such as creating or modifying indexes.

    The Data Transformation Service (DTS) Import Wizard eases the pain of traditional Bulk Copy Process (BCP) operations. You can select from a wide range of input formats, including Jet, xBase, Excel, Paradox, text, Oracle, or any ODBC-accessible table. I gave the Import Wizard a quick test by importing 26,000 rows of a test mailing list from a Jet 3.5 database. The import process completed in less than 30 seconds, and the Create Index Wizard generated a unique clustered index on the telephone number field in a similar period. Using Access 97 to export the same table to SQL Server 7 through ODBC took more than four minutes. OLE DB-based DTS is just one of the major performance improvements in SQL Server 7.

    A new Query Analyzer replaces SQL Server's aging, text-based ISQL_w. Query Analyzer color-keys SQL reserved words and delivers resultsets in a resizable grid for easier viewing. Microsoft is justifiably proud of its new query optimizer and OLE DB-based query engine that offers multi-index operations, merge and hash joins, and hash aggregations. Query Analyzer offers a graphical ShowPlan window that displays the decision tree for complex queries (see Figure 2). Passing the mouse over an icon displays a Properties sheet for the operation, which calculates a Total Subtree Cost in a mysterious currency known only to the SQL Server Relational Engine team. The ShowPlan window is read-only; you don't get to second-guess ShowPlan with your own optimization scheme.

    The Create View Wizard helps you write simple CREATE VIEW Transact-SQL statements, but right-clicking on SQL Server Views and choosing New SQL Server View opens a four-pane graphical window for generating VIEWs (see Figure 3). The da Vinci-based Visual Modeling Tools are updates to those included with today's VB5 Enterprise Edition and Visual InterDev. The panes display table relationships, table.column selections and criteria, Transact-SQL for the view, and a query result grid. Microsoft says that the next version of Access also will include the da Vinci tools to support the single-user SQL Server 7 as the "alternative engine." You can drag tables from a database selected in the MMC primary view to the upper pane, but I found it easier to type the table names into the T-SQL statement's FROM clause, then click on the upper pane to display the table/column boxes. You drag a primary key column over a corresponding foreign key column to establish table relationships and enable SQL-92 INNER JOIN syntax. Column name aliases and WHERE clause criteria go in the second pane; each action in the top two panes adds the appropriate T-SQL clauses to the third pane. When you're ready to test your VIEW, click on the Run button to execute the query and display the resultset in the bottom pane. Clicking on the Save button lets you name your VIEW and sends the complete CREATE VIEW Name AS SELECT ... statement to the server.

    I can't find a ready reason for Microsoft's provision of two dramatically different approaches to designing VIEWs. On the whole, I prefer the single-window graphical approach to the Wizard's stepwise method. The multipane window appears to me to be a cross between Access's Query Design window and the ODBC-based Microsoft Query tool. Access users will miss the drag-and-drop features of the Query Design window, the graphic elegance of the Relationships window, and the ability to specify cascading updates and deletions as JOIN properties. Version 7 of T-SQL still doesn't support SQL-92's ON DELETE CASCADE and ON UPDATE CASCADE predicates. SQL Server 7 supports multiple, recursive triggers for INSERT, DELETE, and UPDATE operations, but I couldn't find a Create Trigger Wizard to aid in writing complex referential integrity triggers.

    Publishing limitations prevent a full discussion of other important wizards for setting up replication, establishing database maintenance plans, creating SQL Agent (formerly SQL Executive) jobs, and generating alerts. I found all the wizards to be useful, especially for new features that weren't fully documented. Fortunately, the Microsoft Total-Cost-of-Ownership Police haven't required the SQL Server team to include popup animated "Database Assistants" in this version.

    Serving OLE DB and ADO
    SQL Server 7 and its related services depend heavily on OLE DB. In 1997, Microsoft promised that the SQL Server 7 query engine would be a totally independent OLE DB service provider (see my article, "Choose the Right Data Access Tool," in the July 1997 issue of VBPJ). Microsoft's Peter Spiro, product unit manager and architect, now says that it's "not a clean OLE DB separation; there are still some API calls to the engine." Regardless of the cleanliness of the subsystem partitioning, OLE DB lets SQL Server 7 offer a variety of new features, including heterogeneous joins to any source of tabular data, for which an OLE DB data provider is available, such as Access MDB files with the Jolt provider.

    Setting up the Sphinx beta on Win9x or WinNT also installs the latest update to ADO 2.0 (the file name, Msado15.dll, is the same for both ADO 1.5 and 2.0), a collection of native OLE DB data providers, and ODBC 3.6, which doesn't differ markedly from prior 3.x versions. To check out the new OLE DB data provider for SQL Server, I wrote a simple VB5 app that creates an ADODB.Connection with the SQLOLEDB provider. The app takes three different approaches to fill an unbound MSFlexGrid control with data from the Orders table of the nwind database that I had upgraded earlier to SQL Server 7. To experiment with parameterized stored procedures and the ADODB.Command object, I used the Create Stored Procedure Wizard to add sp_orders to the nwind database:

    CREATE  PROCEDURE sp_orders
    @StartDate datetime,
    @EndDate datetime
    AS SELECT * FROM Orders
    WHERE OrderDate >= @StartDate 
    AND OrderDate &#lt;= @EndDate

    Both ODBCDirect's QueryDefs and RDO's rdoQueries use { call sp_name ?, ?} ODBC escape syntax to execute stored procedures with input parameters. The ADODB.Command object takes a much more straightforward approach (see Listing 1). Specify adCmdStoredProc as the CommandType property, set the CommandText property to the name of the stored procedure, then specify the Size, Direction, and Value properties of Parameters(1) to Parameters(n). Parameters(0) is reserved for a return value, regardless of whether your stored procedure returns a value. I came up with some interesting speed differences displaying ADODB.Recordsets from stored procedures, Command objects, and Connection objects. Unfortunately, I can't spill the performance beans here because the early version I tested, according to Microsoft, hasn't yet been tuned for performance. But you can—and should—try it yourself. Download the Sphinx app from http://www.windx.com, and give Beta 3 a test run with ADO 2.0. Plan on migrating your VB front ends to ADO to get the full benefit of SQL Server 7's performance improvements, but be sure to run a full set of speed checks before settling on a particular object approach.

    Listing 1
    VB5
    
    Option Explicit
    Private cnnSphinx As New ADODB.Connection
    Private comSphinx As New ADODB.Command
    Private strSQL As String
    Private dblTime As Double
    
    'Modify the constant values to suit your SQL Server 7.0 
    'installation
    Private Const strConnect = _
       "User ID=sa;Data Source=nwind;" & _ 
       "Location=OAKLEAF2"
    
    Private Sub Form_Load()
       'Defaults for Access 8.0 Northwind Orders table
       txtBegDate.Text = "1/1/1995"
       txtEndDate.Text = "3/31/1995"
    End Sub
    
    Private Sub Form_Activate()
       Dim strMsg As String
       
       DoEvents
       On Error Resume Next
       
       Me.MousePointer = vbHourglass
       'Establish the connection
       cnnSphinx.ConnectionTimeout = 15
       cnnSphinx.Provider = _
          "SQLOLEDB" 'Native SQL Server Provider
       Do While True
          Err.Clear
          cnnSphinx.Open strConnect
          If Err.Number Then
             'SQL Server probably isn't running
             strMsg = Err.Description & vbCrLf & vbCrLf & _
                "Try again?"
             strMsg = strMsg & vbCrLf & vbCrLf & _
                "(Make sure SQL Server is started.)"
             If MsgBox(strMsg, vbYesNo + vbCritical, _
                "Can't Connect to SQL Server") = vbNo Then
                End
             End If
          Else
             Exit Do
          End If
       Loop
       cmdStoredProc.Enabled = True
       cmdCommand.Enabled = True
       cmdConnection.Enabled = True
       Me.MousePointer = vbDefault
    End Sub
    
    Private Sub cmdStoredProc_Click()
       'Calls a simple SQL Server stored procedure on the
       'Orders table with two datetime params, @BegDate and 
       '@EndDate
       Dim rstSphinx As New ADODB.Recordset
       
       dblTime = Timer
       With comSphinx
          .ActiveConnection = cnnSphinx
          .CommandType = adCmdStoredProc
          .CommandText = "sp_orders"
          'Parameters(0) is pre-defined as return value
          .Parameters(1).Type = adChar
          .Parameters(1).Size = Len(txtBegDate.Text)
          .Parameters(1).Direction = adParamInput
          .Parameters(1).Value = txtBegDate.Text
          .Parameters(2).Type = adChar
          .Parameters(2).Size = Len(txtEndDate.Text)
          .Parameters(2).Direction = adParamInput
          .Parameters(2).Value = txtEndDate.Text
          Set rstSphinx = .Execute
          'Recordset is ForwardOnly
       End With
       'Report execution time
       txtExecTime.Text = Format(Timer - dblTime, "#0.000")
       If rstSphinx.RecordCount Then
          Call FillFlexGrid(rstSphinx)
       Else
          MsgBox "Your input parameters returned no " & _
             "records.", vbOKOnly + vbInformation, _
             "ADO Stored Procedure"
       End If
       Set rstSphinx = Nothing
    End Sub
    
    Private Sub cmdCommand_Click()
       'Open a Recordset on a Command object
       Dim rstSphinx As New ADODB.Recordset
       
       dblTime = Timer
       strSQL = "SELECT * FROM Orders WHERE OrderDate >= '" _
          & txtBegDate.Text & "' AND OrderDate <= '" & _
          txtEndDate.Text & "'"
       
       With rstSphinx
          .CursorType = adOpenForwardOnly
          .LockType = adLockReadOnly
       End With
       With comSphinx
          .ActiveConnection = cnnSphinx
          .CommandText = strSQL
          .CommandType = adCmdText
          Set rstSphinx = .Execute
       End With
       'Report execution time
       txtExecTime.Text = Format(Timer - dblTime, "#0.000")
       DoEvents
       If rstSphinx.RecordCount Then
          Call FillFlexGrid(rstSphinx)
       Else
          MsgBox "Your input parameters returned no " & _
             "records.", vbOKOnly + vbInformation, _
             "ADO Command Recordset"
       End If
       Set rstSphinx = Nothing
    End Sub
    
    Private Sub cmdConnection_Click()
       'Open a Recordset on a Connection object
       Dim rstSphinx As New ADODB.Recordset
       
       dblTime = Timer
       strSQL = "SELECT * FROM Orders WHERE OrderDate >= '" _
          & txtBegDate.Text & "' AND OrderDate <= '" & _
          txtEndDate.Text & "'"
     
       With rstSphinx
          .ActiveConnection = cnnSphinx
          .CursorType = adOpenForwardOnly
          .LockType = adLockReadOnly
          .Open strSQL
       End With
       'Report execution time
       txtExecTime.Text = Format(Timer - dblTime, "#0.000")
       DoEvents
       If rstSphinx.RecordCount Then
          Call FillFlexGrid(rstSphinx)
       Else
          MsgBox "Your input parameters returned no " & _
             "records.", vbOKOnly + vbInformation, _
             "ADO Connection Recordset"
       End If
       Set rstSphinx = Nothing
    End Sub
    
    Private Sub FillFlexGrid(rstSphinx As ADODB.Recordset)
       'Generic procedure for filling MSFlexGrid
       Dim intCol As Integer
       Dim strItem As String
       
       dblTime = Timer
       With flxGrid
          .ScrollBars = flexScrollBarNone
          'Prevents flashing during load
          .FixedCols = 0
          .FixedRows = 1
          .Rows = 1 'Eliminates an empty top row
          .Cols = rstSphinx.Fields.Count
          .Row = 0
          For intCol = 0 To .Cols - 1
             .Col = intCol
             .Text = rstSphinx.Fields(intCol).Name
          Next intCol
          'AddItem method is required because RecordCount is 
          'unknown
          Do Until rstSphinx.EOF
             strItem = ""
             For intCol = 0 To .Cols - 1
                strItem = strItem & _
                   rstSphinx.Fields(intCol).Value _
                   & vbTab
             Next intCol
             .AddItem strItem
             rstSphinx.MoveNext
          Loop
          .ScrollBars = flexScrollBarBoth
       End With
       'Report grid fill time
       txtDispTime.Text = Format(Timer - dblTime, "#0.000")
    End Sub
    
    Listing 1 Time It Yourself. NDA restrictions and common courtesy preclude supplying performance data on beta versions of SQL Server, but you can use the Sphinx.vbp VB5 app to run your own speed tests on Sphinx Beta 3. Activating frmSphinx attempts to open an ADODB.Connection to an nwind database, upsized from Access 8 to SQL Server 6.5 and then upgraded to SQL Server 7. Clicking on cmdStoredProc executes a parameterized stored procedure to return Orders for a given date range. Notice that the stored procedure call syntax for ADODB.Command objects differs markedly from that of ODBCDirect QueryDef and rdoQuery objects, both of which use ODBC escape syntax. cmdCommand and cmdConnection execute queries directly on Command and Connection objects, respectively. Forward-only recordsets don't return usable RecordCount values, so filling an MSFlexGrid requires applying the AddItem method for each row.
    Something for Everyone?
    If you're planning a new data warehouse or mart, SQL Server 7.0 clearly is the way to go. Much of the SQL Server development team's efforts have gone into making Microsoft's overhauled RDBMS a real contender for hosting very large databases (VLDBs) and speeding execution of complex queries against million-row tables. The Plato add-on to SQL Server, based on OLE DB for OLAP, promises expedited slicing-and-dicing operations on data cubes. SQL Server 7 delivers greatly improved replication services, including transaction consistency with latency (usually in the three- to 10-second range), better Internet support with anonymous pull subscriptions, and scheduled merge replication for increased site autonomy. The decision to upgrade earlier-version OLTP production servers to SQL Server 7 also appears to be a no-brainer to me. The only question is when to upgrade; most DBAs wait until the first service pack or two before committing to an upgrade as dramatic as that from SQL Server 6.x to 7.

    At the low end, rumor has it that Jet—today's king of desktop databases—will be dethroned by the single-user version of SQL Server 7. (For an opinion on why you should stick with Jet, see the sidebar, "Jet: It's a Proven Winner.") Like alternative automobile engines, alternative database engines are by no means a sure bet. (Remember the Wankel rotary engine?) Tens of millions of Access and VB users currently run Jet in both single-user and multiuser mode, and are likely to continue to do so well into the next millennium. So far, I see robust replication and embedded databases as the primary applications for desktop SQL Server 7. Regardless of the short-term acceptance of SQL Server 7 as a Jet replacement, count on Microsoft's marketing machine to achieve the goal of "SQL Server Everywhere."

    Jet: It's a Proven Winner
    After seeing all the great new features and performance enhancements in SQL Server 7.0, you might wonder if you should still bother using the Microsoft Jet database engine in your desktop database Visual Basic applications. Without violating my NDA with Microsoft, the next version of the Jet database engine, Jet 4.0, has more performance and speed improvements and more new developer features than every prior version of the engine combined. Jet 4.0 is much faster and more robust than its predecessor, Jet 3.5. Jet 4.0 will still be an excellent tool for your desktop database solutions.

    SQL Server 7.0 has not yet proven itself to me as a desktop database engine, and it has at least a 50 percent larger memory footprint than Jet. Being such a robust server RDBMS, it might take a cycle or two for SQL Server on the desktop to really prove itself. SQL Server 7.0 has many core changes in such important areas as storage and OLE DB; it really is like a version 1.0. When SQL Server runs in "embedded" mode on Windows 95, all query processing will have to run locally, like Jet. It has proven difficult scaling SQL Server downward.

    Should you start developing all your desktop and file server database systems in SQL Server? Should you migrate all your old Jet apps to SQL Server 7.0? The answer is quite simple: all "desktop" and file server database systems that you develop should continue to use the Jet engine. Jet is a proven File Server database with an excellent track record, and many tools exist for Jet development. Jet has optimizations for smaller types of database systems that SQL Server just does not have. Aggregates are one example of this. Until SQL Server "desktop" proves itself and evolves, until 128 MB of RAM and Pentium IIs are standard, and until we get a clear sign from Microsoft that Jet is dead, we should continue to develop with Jet on the desktop. SQL Server 7.0 running in Windows 95 embedded mode will have a memory footprint of around 7 MB. Jet's average is less than 3 MB.

    Currently, I suggest you use the desktop SQL Server only when doing fast prototyping of a client/server system. Because it is the same engine, you can use Microsoft Access to rapidly build a prototype. (Microsoft Access will not support all the administration functions of SQL Server, but it will enable you to create an excellent prototype.) When you're ready to move the database to a server, simply change the location property in your code. Using SQL Server for your desktop applications is overkill. The Jet engine is just as fast, easier to program, and has a much smaller footprint. Stick with a proven winner.

    S.F.





    Roger Jennings, a principal of OakLeaf Systems, is a contributing editor for VBPJ. His books include Special Edition Using Windows NT Server 4 Second Edition, Special Edition Using Access 97 Second Edition, Platinum Edition Using Access 97, and Unveiling Windows 95 for Que; and Access 2 Developer's Guide and Database Developer's Guide with Visual Basic 4 for Sams. Roger also cowrote Sams' Database Developer's Guide with Visual C++ 4 and is series editor for Sams' new Roger Jennings' Database Workshop series. Reach him at 70233.2161@compuserve.com.