Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 417
  • Last Modified:

SQL 2005 - clustered indexes

1. Unless the underlying field(s) is > 90% unique, a table scan will take place when SQL performs a WHERE.  Is that statement a correct generality?  If so, I will eliminate many indexes I have that are coming nowhere near > 90% unique.  That is, a lot of repetition in the data means no index is helpful.

2. Is there any net plus for a clustered index?  I assume the clustered index requires a lot of shuffling of the data if that is required getting the rows in their correct clustered order.  If this is the case, the clustered index does create a lot of overhead.  That would indicate the clustered index should be fields that are commonly used in the WHERE statements to justify the overhead?
0
dastaub
Asked:
dastaub
3 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
>Is that statement a correct generality?  
Not always, you have to check the Execution plan and make sure that it is not in use

> Is there any net plus for a clustered index?
Clustered index actually stores the data. your assumption is correct, it does needs some shuffling if not properly created. for example if you make an identity column or sequentialid column the clustered index, it wont require any shuffling.
>clustered index should be fields that are commonly used in the WHERE statements to justify the overhead
it again depends, another index or covering index will be sufficient
0
 
mrjoltcolaCommented:
(1) is not a true blanket statement. If the order by of the query is in the same order as the clustered index then a range scan can be used. An "order by" can effect which index is chosen.

And in some cases if the field list in the select are all in an index, the db engine can use the index to satisfy the full query without even accessing the table (assuming this was not the clustered index on the table).
0
 
Kobe_LenjouCommented:
You can use the attached script to check the index usage statistics since last instance restart.

If there are no seeks/scans/lookups then the index is not used.
 
SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
I.[NAME] AS [INDEX NAME],
USER_SEEKS,
USER_SCANS,
USER_LOOKUPS,
USER_UPDATES
FROM SYS.DM_DB_INDEX_USAGE_STATS AS S
INNER JOIN SYS.INDEXES AS I
ON I.[OBJECT_ID] = S.[OBJECT_ID]
AND I.INDEX_ID = S.INDEX_ID
WHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1 
order by OBJECT_NAME(S.[OBJECT_ID]), I.[NAME]

Open in new window

0
 
dastaubAuthor Commented:
it sounds like i have to look at what is going on in the background rather than develop a simple understanding of indexes.
0
 
Kobe_LenjouCommented:
Simple is a good beginning, but don't stop there ;-)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now