Table Indexing

Table Indexing

Post by Rena » Sat, 23 Dec 2000 00:11:42



I have a table created by my predecessor.
 He has the primary key set up as non-clustered and then an index set up on the primary key field and set as clustered. Would it not be better just to set the primary key up as clustered?

This scenario is the same in a number of tables, will it affect performance?

* Sent via Developersdex.com http://www.developersdex.com *
The Web Developers Index

 
 
 

Table Indexing

Post by Tibor Karasz » Sat, 23 Dec 2000 00:27:45


Rena,

You are correct in your assumptions.
Having two the same index on a table is redundant and can hurt performance for
modification operations.

Then whether you want to "waste" the one and only clustered index on the primary key,
is another issue...
--
Tibor Karaszi, SQL Server MVP
Neil's FAQ at: http://www.sqlserverfaq.com
Please reply to the newsgroup only, not by email.


Quote:> I have a table created by my predecessor.
>  He has the primary key set up as non-clustered and then an index set up on the

primary key field and set as clustered. Would it not be better just to set the primary
key up as clustered?
Quote:

> This scenario is the same in a number of tables, will it affect performance?

> * Sent via Developersdex.com http://www.developersdex.com *
> The Web Developers Index


 
 
 

Table Indexing

Post by Dan Guzma » Sat, 23 Dec 2000 00:38:40


There are situations where you will get a performance improvement with a
redundant clustered and nonclustered indexes but it is entirely dependent on
your queries.

A non-clustered index on the PK will cover queries such as WHERE EXISTS and
those that join on the PK but do not retrieve data from that table.  This
will be very fast because of the narrow index.  The clustered index on the
PK may help queries which join and retrieve data from the table.

However, you need to balance the SELECT performance improvements with the
INSERT performance degradation.

Hope this helps.

-----------------------
SQL FAQ links (courtesy  Neil Pike):

 http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
 (faqxxx.zip in lib 7)
 or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
 or www.sqlserverfaq.com
 or www.mssqlserver.com/faq
-----------------------


Quote:> I have a table created by my predecessor.
>  He has the primary key set up as non-clustered and then an index set up

on the primary key field and set as clustered. Would it not be better just
to set the primary key up as clustered?
Quote:

> This scenario is the same in a number of tables, will it affect
performance?

> * Sent via Developersdex.com http://www.developersdex.com *
> The Web Developers Index

 
 
 

Table Indexing

Post by Mike Kruchte » Sat, 23 Dec 2000 00:37:42


You're correct about only needing one of these indexes. You'll have to
determine, for each table, whether you should keep the clustered or
non-clustered.


Quote:> I have a table created by my predecessor.
>  He has the primary key set up as non-clustered and then an index set up

on the primary key field and set as clustered. Would it not be better just
to set the primary key up as clustered?
Quote:

> This scenario is the same in a number of tables, will it affect
performance?

> * Sent via Developersdex.com http://www.developersdex.com *
> The Web Developers Index

 
 
 

1. Urgent Help:Corrupt Table/Index Header in a Paradox Table

        Help!!

        I need urgent help with a Paradox table. When i tried open this
table the DataBase Desktop send me a message : "Corrupt Table/Index
Header."

        I tried with an program utility call fixit.exe (i find this in The
Delphi SuperPage), but i see the same message.

        Please if you know about this problem send me some tips. The size
of table is 8 Mg.

Thanks a lot.
--
Patricio Vera S.
Ingeniero en Computacin e Informtica

2. Table/index size and number of extents

3. Table/index source code needed.

4. SQL*Plus Saving Environment?

5. Quickest way to find table indexes in 6.5?

6. Creating Setup files?

7. My SQL Table Index is Missing!

8. Info: Watcom SQL for DOS ?

9. Attached table index names

10. Fact Table index design

11. how to improve table indexes for performance?

12. Execution Plan and using Table Index

13. Query to retrieve Table indexes