Questions, Answers, and Tips About SQL Server

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 INT
SELECT @Counter=5
SELECT TOP @Counter * FROM
   <mytable>

the procedure returns an error. But the simple line

SELECT TOP 5 * FROM <mytable>

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 INT
DECLARE @sql VARCHAR(255)
SET @Counter=5
SELECT @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.
AS
BEGIN
   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.