Questions, Answers, and Tips About SQL Server - 08 Mar 2000

I installed a new instance of SQL Server 2000, and all my SQL Server 7.0 tools have disappeared. What happened to them?

Although you can run multiple instances of the server engine on one machine, you can't keep multiple instances of the client and administrator tools on the same machine. When you install multiple instances of SQL Server, you'll use the new SQL Server 2000 versions of SQL Server tools (e.g., Query Analyzer, SQL Enterprise Manager—SEM, Microsoft Data Access Components—MDAC) to connect to the SQL Server 7.0 instance. Don't install a new instance if you must keep the old tools.

You might be able to modify executables, DLLs, and Registry entries to hack around this limitation, but you can easily trash a system when you experiment with tricky areas such as the Registry. Until we learn how to work around this limitation, exercise extreme caution.

I want to store column definitions associated with a column in a table, and I want the definitions to reveal the business meaning of the column. For example, if I have a column called OrderDate in my Order table, I want to store a definition that indicates whether the column contains the date the customer placed the order or the date the company fulfilled the order. How do I use the Microsoft Repository to configure this setup?

SQL Server 2000 lets you store extended properties with many database object types. Extended properties are user defined and store a value of type SQL_VARIANT. Visual Basic (VB) programmers are familiar with the new variant data type. Like VB's variant data type, SQL_VARIANT lets you store different data types' data values in one column, parameter, or variable. Each instance of a SQL_VARIANT column records two items: the data value and the meta data that describes the value (i.e., the value's base data type, maximum size, scale, precision, and collation). You can use the SQL_VARIANT_PROPERTY function to get the meta data information for any SQL_VARIANT instance.

For example, if you want to store a description of the au_id column in the authors table in the pubs database, right-click the column name in the new Object Browser that the Query Analyzer interface provides, then select Extended Properties. We added a new property called WhatAmI with a value of I am the author id column!!! Alternatively, you can use the sp_addextendedproperty procedure to accomplish the same task.

sp_addextendedproperty   'WhatAmI2', 'This is a new   property value', 'user', dbo,   'table', authors, 'column',   au_id

You can then use a standard SELECT statement with a new function called fn_listextendedproperty to retrieve the information, as the following example shows:

SELECT   * FROM   ::fn_listextendedproperty   (NULL, 'user', 'dbo',   'table', 'authors', 'column',   default)objtype objname   name valueCOLUMN au_id WhatAmI I am the    author id column!!!COLUMN au_id WhatAmI2   This is a new property valueSELECT   *FROM     ::fn_listextendedproperty   (NULL, 'user', 'dbo',   'table', 'authors', 'column',   default)

I'd like to start playing with SQL Server 2000 beta 2, but I don't want to trash the current working version of SQL Server 7.0 that I've installed on my personal developer workstations. Can I simultaneously run SQL Server 2000 and SQL Server 7.0?

You can simultaneously run SQL Server 2000 and SQL Server 7.0 on the same machine. Previous upgrade processes let you install two SQL Server versions on the same machine, but you couldn't run both versions at the same time. To solve this problem, SQL Server 2000 supports multiple instance functionality.

To simultaneously run SQL Server 2000 and SQL Server 7.0, run the SQL Server 2000 setup program on a system running SQL Server 7.0. You'll move quickly through the Welcome Screen, and the program will ask you to choose between a local or remote installation. Next, the Installation Selection dialog box, which Screen 1 shows, will present you with several installation options. Select Create a new installation of SQL Server. Don't select Upgrade, remove, or add components to an existing installation of SQL Server; upgrading your existing installation will convert your existing databases to the new SQL Server 2000 format.

Brian's former SQL Server 7.0 network name was NITTANY, and the name of his new SQL Server 2000 instance is NITTANY_SQL2000. Brian can connect to the default instance NITTANY that uses a SQL Server 7.0 engine or connect to NITTANYNITTANY_SQL2000 to access the SQL Server 2000 instance. Before you decide to install multiple instances of SQL Server, be aware that SQL Server 2000 tools will replace the old SQL Server 7.0 administrative tools. SQL Server Books Online (BOL) provides detailed information about multiple instances.

I'm an MCSE working to become a Microsoft Certified Database Administrator (MCDBA). I know that SQL Server is a complicated product and that deploying SQL Server applications requires more knowledge than I can get from books. However, can you recommend any beginner resources? I already have the Microsoft SQL Server 7.0 System Administration Training Kit (Microsoft Press, 1999) and William Robert Stanek's Microsoft SQL Server 7.0 Administrator's Pocket Consultant (Microsoft Press, 1999).

The best way to learn is to develop an application or prototype that solves a problem. Invent an application for your use, or create a database application for a local organization.

To gain additional experience, import raw statistical data into a new SQL Server database or OLAP cube and pretend you're an end user who wants to analyze the data. Explore OLAP and the Food Mart sample application. Upsize an Access application for someone you know. Play with the Microsoft Developer Network (MSDN) Duwamish application—install it, investigate it, tear it apart, and rebuild it. You can find more information and practice applications on the Microsoft Web site (http://msdn.microsoft.com/voices/sampleapp.asp and http://msdn.microsoft.com/library/techart/d35vbaroot.htm). You can also join the Duwamish newsgroup at news://msnews.microsoft.com/microsoft.public.msdn.duwamish. You can review the new Fitch and Mather Stocks (FMStocks) sample application at http://msdn.microsoft.com/library/techart/fm2kintro.htm.

I have trouble writing stored procedures that use the TOP statement with a local variable instead of a fixed number. For example, when I write

DECLARE @Counter INTSELECT @Counter=5SELECT TOP @Counter * FROM   

the procedure returns an error. But the simple line

SELECT TOP 5 * FROM 

works. How can I make my version work?

According to SQL Server Books Online (BOL), you can use n in the TOP clause to limit the number of rows that a SELECT statement returns. But n must be an integer. In SQL Server 7.0, Transact SQL (T-SQL) doesn't let you use a local variable in the TOP n clause, even if the local variable returns an integer value. You can identify local and global variables by their prefixes: @ is the prefix for a local variable, and @@ prefaces a global variable. You can use the SET statement to assign a value to a local variable, and you can define local variables by referencing them in the select list of a SELECT statement. The following syntax might meet your needs:

DECLARE @counter INTDECLARE @sql VARCHAR(255)SET @Counter=5SELECT @sql = "SELECT TOP " + str(@counter) + " * FROM authors"EXEC (@sql)

This T-SQL batch dynamically builds and executes a T-SQL string that locates the TOP n rows. The dynamic T-SQL statement lets you create T-SQL commands that are impossible to represent when you use other methods.

How can I use user-defined functions (UDFs) with SQL Server 2000?

UDFs let you encapsulate more logic within a stored procedure. At one point in SQL Server 2000's development, Microsoft planned to include support for language-independent UDFs (e.g., a UDF written in VBScript). Unfortunately, the software limits you to writing UDFs in Transact SQL (T-SQL). The following example from SQL Server 2000 Books Online (BOL) shows how to use basic T-SQL-based UDFs:

CREATE FUNCTION CubicVolume-- Input dimensions in centimeters.   (@CubeLength decimal(4,1),   @CubeWidth decimal(4,1),   @CubeHeight decimal(4,1) )RETURNS decimal(12,3) -- Cubic centimeters.ASBEGIN   RETURN ( @CubeLength *      @CubeWidth * @CubeHeight )END

This script creates a server-side func-tion object that you can call anywhere SQL Server permits a DECIMAL expression.