Questions, Answers, and Tips About SQL Server - 14 Sep 1999

When I run sp_helpindex on a SQL Server 7.0 table on my system, the output lists many index names, such as _WA_Sys_my_field1_160F4887. No one on my team created these indexes, and the mystery indexes increase my database size from 700MB to 1.2GB. How can I tell SQL Server 7.0 not to create these indexes?

Query tuning is complex, and we can only scratch the surface of this topic. For more information about query tuning, see Books Online (BOL).

The mystery indexes that sp_helpindex discovers aren't really indexes—they're statistical sampling information that lets the optimizer make better decisions about how to process your query. SQL Server can't determine the optimal query-processing plan without an accurate idea of how many rows from every table the query involves. SQL Server creates these fake indexes so that it can track the statistical distribution of values in columns that the optimizer wants more information about. In many cases, the fact that SQL Server is maintaining statistics for nonindexed columns is a hint to you that real indexes might speed query performance. Run the Index Tuning Wizard for recommendations about how to improve indexing.

SQL Server turns on this fake index functionality by default, but you can use the AUTO CREATE STATISTICS command within sp_dboption to disable this feature. Although disabling this option might reduce the disk space necessary to store your database, we don't recommend you disable this feature until you've thoroughly examined the performance implications.

SQL Server 6.5 includes a feature that lets you back up and restore one table, but Microsoft dropped this feature from SQL Server 7.0. How can I work around this shortcoming?

SQL Server 7.0 doesn't directly support the ability to back up and restore one table, but this version lets you back up and restore individual file groups. Thus, to mimic SQL Server 6.5's single-table backup and restore functionality, use the CREATE TABLE statement to place a table in a separate file group.

My SQL Server system has a performance problem that seems to be related to the clustered index on a big, frequently updated table. How can I improve performance?

SQL Server 7.0's new DROP_EXISTING clause can dramatically speed index maintenance times when SQL Server is rebuilding clustered indexes. SQL Server 6.5 nonclustered indexes embed page and row ID information in the leaf level of a nonclustered index. This behavior leads to unstable indexes that require a lot of internal maintenance during large INSERT, UPDATE, and DELETE operations. SQL Server 7.0's nonclustered indexes are more stable than SQL Server 6.5's nonclustered indexes because SQL Server 7.0 doesn't maintain page and row ID information at the leaf level if the table also contains a clustered index. In this case, SQL Server 7.0 maintains the clustering key at the leaf level of the nonclustered index.

If you need to rebuild a clustered index on an existing table, you can recreate the index using the CREATE INDEX statement and specifying DROP_EXISTING. This command tells SQL Server that you're recreating an index of the same name and that you haven't changed any of the underlying data. Assuming the clustering key hasn't changed, SQL Server will know that it doesn't need to recreate the nonclustered indexes. However, if you drop and recreate the clustered index without using the DROP_EXISTING clause, SQL Server has to rebuild all the nonclustered indexes to repopulate the cluster key information maintained at the leaf level.

SQL Server 7.0 provides many options for moving databases between servers, including Data Transformation Services (DTS), backup/restore, and sp_detachdb and sp_attachdb. What are the pros and cons of each method, and which method is best?

Without more information, we don't know which method is best for you. Here's a summary of each method's advantages and disadvantages:

How can we create entity-relationship (ER) diagrams on our primary development SQL Server 7.0 system, then move the diagrams to another server?

SQL Server 7.0 stores the information associated with Enterprise Manager's ER diagrams in the dtproperties table in each database. To move a diagram from one server to another, you simply move the associated rows in the dtproperties table. For example, the following command discovers which diagrams a database contains:

SELECT value, objectid FROM   dtproperties WHERE property =   "DtgSchemaNAME"

Running this query results in the following output:

value         objectidTestDiagram   8TestDiagram   215

The value column contains a diagram's descriptive name, and the objectid column contains the internal ID number that SQL Server uses to track the diagram. You can then use a command similar to the following example to move the diagrams from one server to another:

SET IDENTITY_INSERT dtproperties   ONINSERT   dtproperties([id],objectid,property,value,   lvalue,version)SELECT   [id],objectid,property,value,   lvalue,versionFROM   pubs..dtpropertiesWHERE   pubs..dtproperties.objectid = 8SET IDENTITY_INSERT dtproperties   OFF

You must use the SET IDENTITY_INSERT command because the dtproperties.id column uses the identity property. The WHERE clause lets you move only the TestDiagram diagram. To move all the diagrams, you can replace the WHERE clause with a command similar to the following command:

WHERE   pubs..dtproperties.objectid in   (   SELECT objectid FROM   pubs..dtproperties WHERE   property = "DtgSchemaNAME"

Be careful if you use this solution because neither Books Online (BOL) nor Microsoft TechNet document it and Microsoft doesn't officially support it. However, mssql7installinstnwnd.sql shows that Microsoft uses this technique to build the RELATIONSHIP diagram that SQL Server installs as part of the Northwind database.

I tried to use the ALTER DATABASE REMOVE FILE option to remove a file from a SQL Server 7.0 database. However, I get the following error message when I run the command:

Server: Msg 5042, Level 16,   State 1, Line 1The file 'test_file' cannot be removed because it is not empty

Can you help me?

Books Online (BOL) documents the REMOVE FILE option of ALTER DATABASE, but BOL doesn't tell you that you first need to run SHRINKFILE('file_name', EMPTYFILE). This command shrinks the file to 0MB of space used and ensures that you've removed all the objects from the file.

After you use this command, you can get rid of that old file.

Here's a tip for future reference: If you double-click an error message in Query Analyzer, the cursor jumps to the offending line. Thanks go to Mike Yocca, head of the Pittsburgh SQL Server Users Group, for sharing this serendipitous discovery with us.