INDEX( index, index, ... )

INDEX( index, index, ... )

Post by R. Ian Le » Mon, 08 Feb 1999 04:00:00

I am trying to use the INDEX option in a SELECT statement to specify a
particular index that the query should use.  There is almost no
documentation on this in the SQL Server 7.0 Online "Help".  Can someone help
me out here?  Also, I am using this on a Linked Server.  Are there any
issues that I should be aware of?


Ian Lee

R. Ian Lee
Sr. Project Lead
Four-D Software, Inc.


1. Composite Indexes vs. Multiple Single-column indexes

What are the pros and cons of having indexes with multiple columns in them vs. multiple indexes with 1 columns?  For example, consider the table

id            int primary key,
name        varchar(50),
address    varchar(255),
age        int

Assuming we want non-clustered indexes, is it better to have 1 index for (id, name) or two indexes, 1 for (id), 1 for (name)?  Also, is there a difference if the composite index is defined (id, name) vs. (name, id)?  Does it affect the structure of the index tree and how the nodes are assembled?

My opinion is that single-column indexes are better for selecting and querying because the indexes are smaller and the query analyzer can optimize better when working with two smaller indexes than 1 very large index.  However, they are probably slower for transactional environments because there are multiple indexes to maintain with each transaction.

Your thoughts?

Receive e-mail alerts of California power grid emergencies

2. Import 6.5 cp 850 data to SQL7 Unicode

3. Drop Index, BCP Import and Create Index (Ver 6.5)

4. Best practices for large export and import

5. drop and recreate index vs large insert with index

6. Reference needed: Syntax diff. between major SQL's

7. Indexing SQL Server tables with MS Index Server

8. Opening External files

9. Ghost Indexes: Index state unknown

10. Ghost Indexes. Index state unknown

11. Clusterd index vs. index, and

12. Listing indexes and index columns in SQL