DEV311

What's New in SQL Server

Ted Roche
Blackstone Incorporated

In this session, we look at SQL Server 7.0 and 2000, with a focus on the new features introduced in each version. We will look at what new applications are possible with SQL Server, and how you can start learning SQL Server.

My, how time flies when you're having fun! Since Visual Studio shipped with SQL Server 6.5 (in the Enterprise Edition), Microsoft has shipped another version of SQL Server (7.0) and has another one on the way, any day now (SQL Server 2000). According to Microsoft documents, SQL Server 2000 is due to ship "mid-year" and so it is possible that it will actually be on the streets by the time the Miami DevCon starts. In early summer, as I write this, I am basing my experiences on SQL Server 2000 Beta 2.

DISCLAIMER: Much of what is written here about SQL Server 2000 is based on beta software, and is subject to change. While it is unlikely that significant features will change at this late stage, it is a good career move to verify such features exist and work properly before staking your reputation on them.

What's New – SQL Server 7.0

SQL Server 7.0 was a radical departure for Microsoft. Microsoft originally developed SQL Server as part of a joint venture with Sybase and Ashton-Tate. At the 6.0 version number, Sybase and Microsoft were maintaining separate code bases, but it was with the 7.0 product that Microsoft made major revisions, or perhaps a full re-write, of large portions of the code.

The result is a fast and more reliable product.

Row-level locking

8k pages (vs 2k)

Better query handling ("intraquery parallelism" and bitmap indexing, multiple indexes)

Better replication

OLAP Services

Automated administrative functions (Auto-grow & auto Shrink)

Self-tuning memory usage

Wizards

OLAP Services

Performance, Performance, Performance

Microsoft Data Engine (MSDE) – see Jim Duffy's session

Version	Runs On	Licensing / Requirements
Desktop	Windows NT 4.0 Workstation, Server, Windows 2000 Professional or Server, or Windows 9x	Doesn't support Search Service, OLAP Services, parallel queries or transaction replication. Comes with Standard or Enterprise.
Standard	Windows NT 4.0 Server, Windows 2000 Server	Up to 4 CPUs, 2 Gb RAM
Enterprise	Windows NT 4.0 Server and Windows 2000 Enterprise Editions only	Supports Cluster Server, 32 CPUs, over 2 Gb RAM
Developer		Included with Visual Studio. Limited in connections.
Microsoft Developer (MSDE)	Just about Windows everything	Included with Microsoft Office 2000, versions of SQL Server. Lacks management tools by itself. Royalty-free distribution – see your license for details.
Small Business Server	Windows NT 4.0 Server, Windows 2000 Server

What's New – SQL Server 2000

SQL Server 2000 is expected to ship somewhere near the mid-year 2000 mark. Here's a sample of what we can expect to see in that product.

XML Support. While it seems like everything under the sun is getting XML support, the changes in SQL Server 2000 to support XML are very exciting, and are not just pasted on the outside of the engine. XML can be stored natively within the database. XML can be used (using IIS, via an included ISAPI add-on, and ADO) to transfer data in and out of a SQL Server over the web.

Full-text searches, even in large text fields.

Data Mining and Analysis

SQL Debugging

User-Defined Functions

What new apps are possible?

OLAP Services, introduced in SQL Server 7.0, set SQL Server as a serious contender in the Data Warehousing arena. In addition, since the tools are COM-compliant, they may be interfaced with, and interact with, other familiar tools such as Visual Studio or Microsoft Office.

Multi-tier distributed database applications. With a number of different ways to replicate and synchronize data up and down a chain of machines from Pocket PCs in the field to MSDE within small workgroups up through departmental and enterprise servers, Microsoft may have a solid solution here.

How to learn SQL Server

There are a slew of books, magazines and online forums to help you learn SQL Server. Check out some of the online forums for reviews of books, or use one of Whil Hentzen's favorite techniques: think of a question about SQL Server, preferably one you know the answer to, and then see how easy it is to find the answer in a book you're considering. If you can't find a fact you already know, consider how hard it might be to learn new ones!

SQL Server can be installed on a single machine, running any operating system after Windows 95. Different versions can be installed on the different machines, and with some significant limitations, but not a real concern when you are installing a machine to learn from. Microsoft

STRONGLY recommend that a framework be used to ease the learning curve. There are a fair number of intricacies involved in proper handing of tables, cursors, buffers and transactions.

Issues and Problems

No software is without its issues, and SQL Server is no exception. Here we focus on some know issues of working with SQL Server and Visual FoxPro.

Q234070 - PRB: DataType Property for Field is Invalid When Accessing Remote Views of SQL Server 7.0 Tables describes one of several issues that effects VFP when working with Unicode fields. In this case, it is a problem with the Unicode fields greater than 255 characters in length. VFP misinterprets them to be a character field of length 255, and an error ("DataType Property for Field <fieldname> is Invalid") occurs because VFP character fields are limited to 254 characters. The work around is to use the CAST() function as part of your SQL call, to transform the datatype to text. Text fields are properly interpreted as memo fields.

VFP has a separate issue with nChar fields where the last character is cut off and a CHR(0) appears in its place. The workaround to this one that I have gotten to work is to expand the field, using field mapping within a view, to one more character than the field length. Obviously, you then need to program around this excess, insuring that you are not accepting input you can't save.

Finally, a problem with datetime fields appeared in the VFP 7.0 product, as described in Microsoft KnowledgeBase article Q253886 - PRB: Updating DateTime Data in Microsoft SQL Server 7.0. This problem is not limited to VFP, but in fact occurs in Jet, VB and DAO as well (Q225334 - PRB: Jet/VBA May not Work Correctly w/ Datetime Data Containing Higher Precision for Fractional Seconds). The problem is that the SQL Server datetime precision has been extended to milliseconds, so that update statements that update the value need to reference an original value with that precision. VFP does not display millisecond precision, so an UPDATE fails because the original value cannot be found. Note that this is only a problem if another process, such as SQL Server, with that higher accuracy, has inserted values with millisecond precision into the database. If all processing goes through VFP, the error will not occur. There are several work-around besides those suggested in the article. My favorite is to convert the datetime value to a string for manipulation within VFP, and then return a string to the updateable view for processing into SQL Server.

Q245714 - PRB: Inserting > than 250K of Data in a SQL 7 Image Field Fails documents a problem, and a work-around, when inserting large amounts of information into an image field.

DEV311

What's New in SQL Server

What new apps are possible?

How to learn SQL Server

Issues and Problems

See Also: