By Tim Bradley

Microsoft SQL Server 2000 has introduced many new features, including additional datatypes, user-defined counters, and recovery options. Here’s an overview of these features and a look at how you can use them.

New datatypes in v2K
SQL Server 2000 offers these new datatypes:

  • bigint
  • sql_variant
  • table

The bigint datatype is just what its name implies. It’s an 8-byte integer giving the range of -2^63 to 2^63-1, or -9,223,372,036,854,775,808 through 9,223,372,036,854,775,807. This is in contrast to the largest int datatype in v7.0, where int is -2^31 to 2^31-1, or -2,147,483,648 through 2,147,483,647. The v7.0 type consumes only four bytes for storage.

The sql_variant datatype is designed to hold a value from any other datatype except text, ntext, image, timestamp, and another sql_variant. This is useful if you need to store values of differing datatypes in the same column.

The most notable new datatype is table, which is used to store results of a function, as well as being defined as a local variable. Since this type store results from a user-defined function, it can also be the return type of the function. This is one of its most significant aspects. Note, however, that you can’t use this datatype in the definition of a column within a table or as a parameter to a stored procedure or function.

User-defined counters in v2K
System and database administrators use the Windows NT/2000 native performance monitor to keep watch over their systems and SQL Server processes. However, it’s also possible to define a SQL Server counter that can monitor application level items. Use the following system stored procedures to update up to 10 user-defined counters that can be displayed in Performance Monitor:

Consider the following scenario. You have a table that tracks inbound customer service telephone calls. By using the following code in an insert trigger on the InboundCalls table, you can display the current number of calls received:
SELECT @num = COUNT(*) FROM InboundCalls
EXEC sp_user_counter1 @num

The counter sp_user_counter1 pertains to the MSSQL: User Settable performance object that can be displayed in the Performance Monitor application.

Note:These counters can relay only integer information, and you should avoid intensive queries for performance reasons.

Recovery models in v2K
You can use three types of recovery models to back up and restore your critical databases:

  • FULL

You set this from the Recovery Option tab in the SQL Enterprise Manager for each database.

The FULL model is the safest method to use in the event of data file corruption. Use this model if you want to recover to a specific point in time, up to the last transaction log backup, or up to the last transaction. Of course, success is dependent upon whether your transaction log is still valid and whether you’ve been making regular database and transaction log backups. This model also retains BCP, BULK INSERT, and CREATE INDEX operations. The downside to this model is that your transaction log backups can be time- and space-intensive.

The BULK_LOGGED model allows for full recovery of a database, as well as BCP, BULK INSERT, CREATE INDEX, WRITE/UPDATE TEXT, and SELECT INTO operations. The difference between FULL mode and BULK_LOGGED mode is that BULK_LOGGED logs that one of these operations has taken place and logs the extent of changes that were affected.

This model only allows for full and differential database backups. Since SQL Server truncates the transaction log at regular intervals, the log is not needed during the recovery operation.