Answers from Microsoft - 26 Jul 2000

Editor's Note:Send your SQL Server questions to Richard Waymire, program manager for SQL Server development at Microsoft, at [email protected].

Why do queries that I run in text mode perform slower than queries in grid mode?

In text mode, SQL Server fetches the results and returns them line-by-line to the text windows. When you choose to return results in grid mode, SQL Server fetches the results and places them in storage on the client. Then, SQL Server renders the results from the client. Because the results are graphical, this process is significantly faster. In SQL Server 2000, we've changed the default output format in Query Analyzer to grid mode because it's much faster than text mode.

How can I determine how much memory my SQL Server system is using? I've heard that the SQL Server Performance Monitor counter, target server counter, and total server memory counter don't always give accurate information.

Look at the Microsoft Windows NT Server 4.0 Resource Kit utilities to view how much memory the process is consuming and compare that figure with the amount that the Performance Monitor reports. If you're using NT 4.0, the server records how much memory it allocated for its devices, which doesn't include how much memory the server used to load SQL Server. SQL Server 7.0 (and any 32-bit application) can see a maximum of only 2GB of RAM. If you're using NT Server Enterprise Edition with SQL Server 7.0 Enterprise Edition, you can modify your boot.ini and set the /3GB switch, which will enable your application to use 3GB of RAM.

Here's an example of how your boot.ini file might look with these settings:

[boot loader]timeout=30default=multi(0)disk(0)rdisk(0)partition(2)WINNT[operating systems]multi(0)disk(0)rdisk(0)partition(2)WINNT="Windows NT Server Version4.00" /3GBmulti(0)disk(0)rdisk(0)partition(2)WINNT="Windows NT Server Version4.00 [VGA mode]" /basevideo /sos

I want to transfer all the logins from my SQL Server 6.5 system to my SQL Server 7.0 system and retain the same passwords for SQL Server 7.0 that users had for 6.5. I tried using Data Transformation Services (DTS), but DTS lets you transfer logins only between two SQL Server 7.0 servers. I tried using SQL Server 6.5 Transfer Manager, but when Transfer Manager scripts out the logins, it sets the password to NULL. I know that I can use the SQL Server 7.0 Upgrade Wizard to transfer the logins, but I don't want to upgrade entire databases. How can I transfer these logins?

You can use the version of sp_addlogin that comes with SQL Server 7.0 to transfer the logins.

sp_addlogin [@loginame =] 'login'     [,[@passwd =] 'password']  [,[@defdb =] 'database']  [,[@deflanguage =] 'language']  [,[@sid =] 'sid']  [,[@encryptopt =] 'encryption_option']

Notice that the last option lets you specify (with the option skip_encryption_old) that the SQL Server 6.x system has already encrypted the password you're passing in. The next problem is transferring the login and encrypted password. You can transfer the login and password by writing a remote stored procedure to query syslogins in SQL Server 6.5 and calling it from 7.0 after you set up the 6.5 system as a remote server. Then, dynamically construct the call to sp_addlogin for each fetched login from 6.5 and add it to 7.0. As an alternative to this method, you can use bulk copy program (bcp) to move the data out, then use bcp to put the data into a temp table and loop through the table, thus dynamically constructing calls to sp_addlogin.

I want to install active/active failover support for SQL Server 7.0 Enterprise Edition. A Microsoft diagram shows that you need to install two external disk arrays for this configuration, whereas in active/passive mode, two Windows NT servers access only one external disk array. Is an active/active configuration with only one external disk array possible?

No, that configuration isn't possible. Each virtual server depends on one or more disk resources, and only one computer can own that disk resource at any given time. An active/passive setup has only one SQL Server running, so you need only one disk (which only one machine owns at any given time). Because active/active configuration requires two SQL Server systems running on two separate machines, you need at least two disks.

I have a table with a decimal data type in a field called COD. If I submit a query with a filter that passes a number, as in

SELECT * FROM table1 WHERE COD = 123

then SQL Server applies an index scan and returns the results very slowly. If I use a decimal point after the number, as in

SELECT * FROM table1 WHERE COD = 123.

then SQL Server applies an index seek and returns the results much faster. Why does using the decimal point make a difference in the way SQL Server handles the query?

If you don't use a decimal point at the end of the number, then SQL Server sees the number as an integer value and needs to convert the data to compare it to an integer. If you use the decimal point, SQL Server correctly sees the data type as numeric (which is what the decimal data type is) and can use an index seek, which is a faster method than using an index scan in this case.