Editor's Note: Send your SQL Server questions and comments to SQL Server MVP Brian Moran at [email protected]
As a DBA for internal development projects at my company, I often help troubleshoot slow applications, and I'm amazed at how often I find tables that don't have any indexes. How can I quickly determine which tables in a database don't have an index?
The following query returns a result set with the names of tables that don't have an index and uses the rows column in sysindexes to return an estimate of the number of rows in those tables:
SELECT sysobjects.name TableName, (SELECT rows FROM sysindexes WHERE id = sysobjects.id AND indid = 0) Rows FROM sysobjects WHERE type = 'U' AND OBJECTPROPERTY(sysobjects.id , 'TableHasIndex' ) = 0
Note that the only way to find the exact number of rows in a table is to execute a SELECT COUNT (*) against the table.
Queries that use the OBJECTPROPERTY column can provide a variety of helpful information about your SQL Server databases. For example, most tables should have a carefully chosen clustered index. In the above example, you can replace the TableHasIndex property check with TableHasClusteredIndex to quickly generate a list of tables that don't have a clustered index.
Our developers prefixed certain classes of indexes with the pound (#) character. SQL Server Books Online (BOL) lists # as a valid character with which to start an index, and we created the indexes without a problem. But now we can't drop the indexes prefixed by #. Trying to do so generates the following error message:
Server: Msg 159, Level 15, State 1, Line 1 For DROP INDEX, you must give both the table and the index name, in the form tablename.indexname.
This problem is an obscure SQL Server bug. In SQL Server 2000 and SQL Server 7.0, you can't use the DROP INDEX command to drop indexes prefixed with the pound character. A simple workaround is to use sp_rename to change the index name, then issue the DROP INDEX command. The Microsoft article "BUG: Index Cannot be Dropped if the Name Begins with '#' Character," (http://support.microsoft.com/support/kb/articles/q254/5/98.asp), discusses the problem and workaround.
One of our databases intermingles char and nchar data types for a few primary and foreign keys used in joins on large tables. The keys are properly indexed, but SQL Server doesn't always use the indexes to join columns when one column is char and the other is nchar. How does SQL Server use indexes when joining tables on columns of different data types?
Your question raises the important topic of data type precedence when SQL Server implicitly converts data types to execute a query. Under the "Data Type Precedence" topic, SQL Server Books Online (BOL) says that when you use an operator to combine two expressions of different data types, SQL Server implicitly converts the data type with lower precedence to the data type with higher precedence. Figure 1 lists SQL Server's data types in order of highest to lowest precedence. From this information, you can see that SQL Server implicitly converts a char data type to nchar, which has a higher precedence, before making any comparison.
An example might help you better understand data precedence and why SQL Server doesn't always use your indexes to join tables on columns that have different data types. Listing 1 shows code that creates a subset of Northwind Customers and Orders data and creates redundant instances for the CustomerId column so that you can experiment with joining on columns with different data types (char and nchar). Query Analyzer shows that SQL Server performs a table scan on the Orders table. SQL Server implicitly converts the orders.charCustomerId column to the nchar data type by essentially executing a
statement. SQL Server sometimes ignores your indexes because you typically can't use indexes on columns that the CONVERT function affects. Generally, ensuring that join columns are of the same data type is the safest and best course of action.
I downloaded the SQL Server 2000 Developer Edition from the Microsoft Developer Network (MSDN) Web site and wonder what makes this edition different from the other editions. Unfortunately, the comparison of SQL Server 2000 editions at http://www.microsoft.com/sql/productinfo/sqlcompdata.htm doesn't include the Developer Edition. Where can I find information about this edition's benefits and limitations?
The SQL Server 2000 Developer Edition includes all the features you'll find in the Enterprise Edition, but you can use the Developer Edition only for testing and development. The End User License Agreement (EULA) for the Developer Edition says that "...one individual is granted a nonexclusive, personal license to use the Product solely for the purposes of designing, developing and testing your software products according to the terms of this EULA." You can find a complete description of the different SQL Server 2000 editions in SQL Server 2000 Books Online (BOL) under "Editions of SQL Server 2000."