Questions, Answers, and Tips - 22 Feb 2001

Editor's Note: Send your SQL Server questions and comments to SQL Server MVP Brian Moran at [email protected]

Can you debug triggers and stored procedures in SQL Server 2000 without using a third-party product?

SQL Server has supported trigger and stored procedure debugging for some time through Microsoft Visual Studio (VS), but SQL Server 2000 makes debugging easier than ever. SQL Server 2000's debugging capability is stable and ships as part of Enterprise Manager, making installation and use a breeze.

I don't have room to explain all of SQL Server 2000's debugging features in this column, but the sample script in Listing 1 shows one approach to debugging triggers. After running the script, click Open Brower from the Query Analyzer Tools menu (SQL Server 2000 only), and navigate to the new UpdateProc procedure that the script created in tempdb. Right-click the procedure, and select the Debug option to access the Debug Procedure dialog box, which Figure 1 shows. Enter an input value, such as 100, for UpdateProc, then click Execute. The procedure updates a row in TriggerTest, which fires the UPDATE TRIGGER. This trigger in turn sets the newly updated value of Col1 in TriggerTest to n * -10, where n is the input value you supplied for UpdateProc.

Note that by default, the Auto roll back option is enabled so that when your debugging session ends, SQL Server will roll back any changes you make to the database. Disabling this option causes SQL Server to save to the database any changes you make during the debugging session.

Can you use Visual Basic (VB) to create Data Transformation Services (DTS) packages?

I think that the easiest way to learn how to create your own DTS-based programs is to create a DTS package that performs the functions you want, then script the package in VB. SQL Server 7.0 has the limited capability to turn a DTS package into a VB program, but the SQL Server 7.0 DTS package designer interface doesn't have integrated support for this functionality. If you're using SQL Server 7.0, you need to use scriptpkg.exe to turn a DTS package into VB code. You can find this utility in the compressed dtsdemo.exe file in the DevToolsSamplesDTS folder on the SQL Server 7.0 installation CD-ROM. SQL Server 2000 users have it much easier. The SQL Server 2000 version of DTS lets you save a DTS package directly as a VB file. You can then use the VB code to learn how the DTS object model works and to experiment with how to manipulate DTS COM objects through code.

SQL Server Books Online (BOL) says that the keycnt column in the sysindexes system table shows the number of keys in an index, but I thought that keycnt showed the number of columns specified in the CREATE INDEX statement. What exactly does this column show?

Information in sysindexes.keycnt can be confusing if you don't understand how SQL Server physically stores information differently under different circumstances. The keycnt column does contain the number of keys in the index. But the fact that SQL Server is counting the number of keys in the physical representation of the index, rather than the number of columns you logically specified in the CREATE INDEX statement, isn't obvious. Let's look at how the value of this column might vary based on the type of index you're using. For this example, let's consider four index types: unique clustered, nonunique clustered, nonclustered when the table also has a clustered index, and nonclustered when the table doesn't also have a clustered index.

Listing 2 shows a script that creates a unique clustered index, then queries sysindexes.keycnt for the new index. As you can see in the query result, keycnt shows a value of 1, as you would expect after reading BOL. Listing 3, however, shows a script that creates a nonunique clustered index, then queries sysindexes.keycnt for the new index. This time, keycnt shows a value of 2, which you might not expect. You need to understand the nonclustered index architecture to see why keycnt returns a value of 2 for the second query instead of a value of 1. SQL Server manages nonclustered indexes differently based on whether the table also has a clustered index. In its entry for "Nonclustered Index," BOL explains how SQL Server manages row locators for both nonclustered and clustered indexes:

All clustered indexes in SQL Server must be unique. If you create a nonunique clustered index, SQL Server will add a hidden column to the index to make it unique. Because the previous clustered index had one column but that column wasn't unique, SQL Server added a column and returned a final keycnt value of 2.

Now, let's look at how sysindexes.keycnt behaves for nonclustered indexes depending on whether or not the table also has a clustered index. The script in Listing 4 shows what happens when you add a nonclustered index to either the IndexTest1_uniqueCindex table or the IndexTest2_nonuniqueCindex table. IndexTest1_uniqueCindex has a unique clustered index, whereas IndexTest2_nonuniqueCindex has a nonunique clustered index. As you can see in Listing 4, sysindexes returns different keycnt values for the two tables. In both cases, the keycnt value for the nonclustered index is the keycnt value for the clustered index plus the number of columns in the nonclustered index. You get these keycnt values because SQL Server stores the clustered index key as a row locator in the nonclustered index. The keycnt value for IndexTest1_uniqueCindex's NonclusteredIndex2 is 3, or 1 more than the keycnt value for IndexTest2_nonuniqueCindex's NonclusteredIndex. SQL Server built NonclusteredIndex2 on a table with a nonunique clustered index, which caused SQL Server to add a uniqueidentifier value to the duplicate key.

The script that Listing 5 shows demonstrates the final case: what happens when you add a nonclustered index to a heap table—a table that doesn't have a clustered index. Notice that the keycnt value in this case is also the number of columns in the nonclustered index plus 1. The keycnt value in this example (2) is the same as the keycnt value for IndexTest2_nonuniqueCindex—but for a different reason. When you add a nonclustered index to a heap table, you aren't storing the clustered index key as a row locator, but you still need to store a row locator. So, SQL Server creates a row ID as a row locator by using the value of the file ID, the page number, and number of the row on the page.

I recently found out that the person who installed SQL Server 7.0 for us installed the Desktop Edition instead of the Standard Edition. We're using SQL Server on a 450MHz dual-Pentium II processor Compaq ProLiant system that's running Windows NT Server 4.0. We're using SQL Server to run an accounting and distribution application, which supports about 25 users who perform accounting, order entry, inventory management, and other tasks. We have three main databases, the largest being almost 4GB. Is running the Desktop Edition instead of the Standard Edition affecting our system's performance?

Yes, you're likely seeing suboptimal performance. Microsoft designed SQL Server 7.0 Desktop Edition primarily for individual use. This edition doesn't include certain advanced performance enhancements such as parallel queries, fiber-mode scheduling, read-ahead scans, and hash and merge joins. Chances are good that these features would improve your application's performance.

Performance considerations aside, you need to install the Standard Edition anyway because your current configuration is illegal. The End User License Agreement (EULA) for SQL Server 7.0 says that Microsoft doesn't license the Desktop Edition for the multiuser environment that you've described. Note, however, that you can easily install SQL Server 7.0 Desktop Edition by mistake. The problem occurs when you double-click the install SQL Server components option on the browser-based interface. The first click takes you to the next screen, in which the installation procedure automatically applies the second click to the option the cursor is pointing to. The option is the desktop installation, not the standard installation.