Questions, Answers, and Tips About SQL Server - 01 Oct 1998

Use this command-line utility wherever accounts reside

Microsoft expects to ship SQL Server 7.0 this year (we received our copies of beta 3 midsummer). We're not going to focus exclusively on SQL Server 7.0 from now on (some of our best friends maintain sites that are running SQL Server 4.x), but we want to point out some new features we're jazzed about. (For more information about SQL Server 7.0, see Michael Otey, "What's New in SQL Server 7.0?" September 1998.)

This fall, Duke Communications is planning to launch a new publication, SQL Server Magazine, devoted exclusively to the needs of the SQL Server community. Get a peek at what's in store by visiting the new magazine's Web site at http://www.sqlmag.com.

Q: Will SQL Server 7.0 simplify obtaining schema data?

Current versions of SQL Server don't provide some simple commands (how many of you have yearned for a LIST TABLES command?). With Enterprise Manager (EM), you can drill down through the hierarchy (i.e., server group to server to database to tables), but the point-and-click approach tends to drive database administrators (DBAs) crazy. Microsoft has heard your plea and in SQL Server 7.0 provides new information schema views.

You're probably familiar with system tables, which contain metadata (i.e., data about data). All relational databases store information about themselves in a collection of nonstandard system tables. IBM's DB2 and Informix's Universal Server Universal Data Option servers call these tables system catalogs and Oracle calls them data dictionaries.

DBAs query these system tables to find details about a database's schema, configuration, and permissions. For example, SQL Server databases all have a sysobjects table containing a row of information about every object in the database. To obtain a list of all user ('U') tables in the current database, type

SELECT * FROM sysobjects
WHERE type = 'U'

Although system tables are handy, Microsoft has always discouraged users from writing queries directly against them because their structure is subject to change. However, lots of DBAs (and independent software vendors--ISVs) have ignored the warning, because they can glean so much useful information from the internal system tables.

SQL Server 7.0 will make giving up the system tables easier, thanks to the new information schema views that conform to the ANSI SQL-92 definition for the INFORMATION_SCHEMA. In other words, Microsoft has exposed an ANSI-standard map of the internal system tables. Instead of querying system tables directly, you'll query the appropriate information schema view to get information. We suspect nuggets of information will reside only in the system tables, but these new views will be adequate for most needs. Rather than selecting data directly from sysobjects to get a list of all tables in the database, you can run the following query:

SELECT * FROM information_schema.tables

Table 1, page 206, shows output from the SELECT* query when pubs is the active database. You can narrow your request (e.g., ask for only views or column names). You can find out more about the information schema views in Books Online (BOL); Figure 1 lists the schema catalogs you can request.

Finally, if you want to see how the competition handles system tables, take advantage of Platinum Technology's database tips (http://www.platinum.com/dbtips). You can download, view online, or order free posters of DB2's V4 or V5 mainframe system catalog or Oracle8's Data Dictionary. Platinum promises to post the SQL Server system tables soon. In the meantime, Steve Wynkoop has posted SQL Server 6.5 and SQL Server 7.0 system tables at http://www.swynk.com/friends/hotek/articles/ss65_schema.asp and http://www.swynk.com/friends/hotek/articles/ss70_schema.asp, respectively.

Q: Does SQL Server 7.0 finally give users an ALTER TABLE command that can change column definitions?

Yes, the old ALTER TABLE command didn't have an ALTER COLUMN option, a function that users expected to find. As you can see in the syntax from SQL Server 7.0 beta 3 Books Online (BOL), which Listing 1 shows, you can add or delete columns and their constraints. You can even disable triggers. Very cool. This functionality ranks right up there with true row-level locking on both data and index pages.

Q: Do I have to install Internet Explorer (IE) 4.01 or later to manage SQL Server 7.0?

Yes, you need IE 4.01 to even install SQL Server 7.0, not to mention manage it. With the release of SQL Server 7.0, Enterprise Manager (EM) is a snap-in to the new Microsoft Management Console. MMC is the new Web-Based Enterprise Management (WBEM)-compliant user console for all BackOffice products. MMC provides both administrators and independent software vendors (ISVs) a common interface for all BackOffice servers.

Q: In SQL Server 7.0, why is the sp_configure memory set to 0?

One of SQL Server 7.0's major improvements is its ability to dynamically configure key resources. In the past, you explicitly assigned a certain amount of memory to SQL Server and needed to reboot the server if you wanted to change that amount. SQL Server 7.0 defaults to setting the memory parameter to 0, which lets SQL Server dynamically increase or shrink its memory utilization according to the amount of free memory available in the server. You can always override the dynamic memory allocation and go back to setting memory configuration manually as you did in SQL Server 6.5, but we can't imagine many times you'd want to.

Q: What is intraquery parallelism, and what makes it a major enhancement in SQL Server 7.0?

Since SQL Server 6.0 introduced multithreading support, SQL Server has been able to take advantage of parallelism, which symmetric multiprocessing (SMP) offers. SQL Server 6.x can run more than one query at a time (based on the number of processors), but it runs each query on only one thread. Intraquery parallelism, however, lets SQL Server break up queries, such as complex joins or large sorts, into multiple components that SQL Server can run concurrently in separate threads. Breaking the queries into separate threads has overhead, so this type of processing doesn't always make sense (e.g., for straightforward queries or on uniprocessor boxes), but the optimizer knows when intraquery parallelism is likely to yield dramatically better query performance. For more information about how SQL Server makes this choice, look up Degree of Parallelism in SQL Server Books Online (BOL).

Q: Is the new Data Transformation Services (DTS) in SQL Server 7.0 a simple and intuitive replacement for bulk copy program (bcp)?

We consider DTS one of the best technologies to come from Microsoft in a long time. Not only can you use it to import and export data, but you can use DTS with SQL Server Agents to schedule data transfers.

This functionality is particularly useful for data warehouses and online analytical processing (OLAP) applications. However, as cool as DTS is (it even comes with its own wizard), bcp isn't dead and provides better performance in some cases.

Q: I was recently promoted to database administrator (DBA), when one of our administrators quit. Unfortunately, he didn't tell anyone the administrator password for a crucial SQL Server 6.5 system. What can we do?

The easiest solution to losing the administrator password is to take advantage of the fact that all local NT administrators can access SQL Server via a trusted connection. You can request a trusted connection from Enterprise Manager (EM) when you register the server. By registering the server when you're logged on as NT administrator, you'll become the SQL Server systems administrator.

Q: Can you run SQL Server on an AS/400?

Our colleague Mike Otey answered your question. You can write Visual Basic (VB) applications that access AS/400s, but not natively. You need to use a hardware add-on, IBM's Integrated PC Server (IPCS), a PC on a card that you can plug into the AS/400. This card has a Pentium Pro CPU, monitor, keyboard, and mouse ports. It shares tape and CD-Rom storage with the AS/400. The card can run standard Windows NT Server, and then you can load other BackOffice products on the NT server as though it were a regular PC server. Although this system works, bear in mind that Microsoft doesn't officially support running BackOffice products on IPCS.

Q: What is Starfighter?

Starfighter is Microsoft's code name for SQL Server's tools group--the teams responsible for Query Analyzer, Enterprise Manager (EM), and other SQL Server tools. Ironically, Oracle Enterprise Manager 1.2, which shipped with Oracle 7.3, was also code-named StarFighter.

Q: I inherited a SQL Server dump file, but I haven't been able to load it. I've tried treating it as removable media (sp_dbinstall), using LOAD DATABASE, restoring by backing up a blank database, and even changing filenames. What else can I try?

Check default configuration settings such as sort order. Your master might be set up as a binary sort and the backup from a database with a different sort. To correct the problem, rebuild the master and change the sort order.

Q: I'm trying to use xp_cmdshell to bulk copy program (bcp) data, and even though I have both systems administrator and Windows NT domain administrator rights, I recently got this message: Login failed- User: NT$AUTHORITY_ANONYMOUS$LOGON Reason: Not defined as a valid user of a trusted SQL Server connection. Why couldn't I log in?

Make sure the MSSQL Server service login is a user with the same NT administrator and systems administrator permissions as the SQL Executive.