Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
General Enhancements
While SQL Server v7.0 was a great product, Microsoft realized there were some general improvements they could make to the RDBMS, allowing for better integration, customization, or just usability.
XML
SQL Server 2000 has native support for XML, allowing SQL statements to receive an XML stream as input, and to parse the values into temporary tables (for example) so that the data can be accessed as you would any other large result set. XML can also be returned directly from SQL statements, allowing XML to be passed directly to an application. By applying an XSL transformation with XSLT, your data can be displayed in a nice, clean format, without all of those horrible recordsets and parsing of data.
Data Transformation Services (DTS)
Data Transformation Services (DTS) is a powerful scripting toolkit that allows developers to create workflows for processing of data and business rules. Because this is part of SQL Server 2000, developers can easily have DTS packages (small executable snippets of code, etc.) as a scheduled task.
DTS is commonly used to import/export and transform data. Since you can create a workflow (with a nice pretty designer), it's a simple case of defining the packages, tasks, and steps—and what they do—and then establishing decisions based on the results of the package.
Enhancements to DTS with SQL Server 2000 include the following:
Create logging capabilities. Allows you to write logs so that a complete task can be re-created. These logs can be written to files, as well as to tables within SQL Server.
Save to Visual Basic files. Allows the code to be portable across installations of SQL Server. DTS packages are commonly written in VBScript, although JScript is supported.
Global variables. Allows one task (within a package) to read and interpret the variables that another task has used, without the developer having to pass variables around. These are persisted in memory, so some overhead is required (although pretty minimal on modern hardware platforms).
Enhanced custom tasks. Allows you to send data to and from FTP sites, run more than one package at once, and include messaging between packages.
DTS offers a lot of extra flexibility to an installation of SQL Server and is a fantastic way of processing information that comes in a wide variety of formats from many sources. Of course, developers can write custom COM objects and then make calls to the objects from within a DTS package, allowing for a very flexible solution for extracting data from multiple sources.
Individual Database Collation Settings
With SQL Server v7.0 and earlier, once the installation was complete, the collation setting (how data is stored, compared, and presented) was tied down for the complete installation of SQL Server. With SQL Server 2000, you have a lot more flexibility.
For example, suppose you have a regular data feed from a Spanish company (Spaniards Inc.). To make life simple, they send you a complete SQL Server backup. You can restore the Spaniards Inc. database into your instance of SQL Server without any data loss or problems converting characters. You also won't impact your production applications, and best of all you can incorporate Spaniards Inc.'s data into your applications at your leisure.
User-Defined Functions (UDF)
This feature has to be one of my personal favorites. With SQL Server 2000, you can build your own functions (known as user-defined functions, shortened to UDF) and call these from within T-SQL statements as though they were built-in functions. A nice example is a date formatting function. In the past, you had to either rewrite the SQL statement for this function over and over, or leave it up to your presentation layer to reformat dates and deal with the inherent problems of different server configurations.
With UDFs, however, you can build the SQL statement once, and call it again and again within other SQL statements to format the date. I've included the date function from my book below, so that you can see how this done.
NOTE
This function takes two parameters: the date you want to format, and the separator you want to use to separate the formatted date (for example, '12/11/02', '-').
CREATE FUNCTION DateFormatter (@Date DATETIME, @DateSeparator CHAR(1))
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @ReturnDate VARCHAR(20)
SET @ReturnDate = CONVERT(VARCHAR(2), DAY(@Date)) + @DateSeparator +
DATENAME(MONTH, @Date) + @DateSeparator +
CONVERT(VARCHAR(4), YEAR(@Date))
RETURN(@ReturnDate)
END
To use the function, you simply call it from within a SELECT statement.
SELECT dbo.DateFormatter(myDate, "-") FROM mytable
Simple, huh?
Now that we've explored all the good stuff about SQL Server 2000, let's take a moment to consider the biggest problems with the RDBMS.