Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

result of adding non-clustered non-unique index vs clustered one Expand / Collapse
Author
Message
Posted Thursday, March 21, 2013 2:20 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 24, 2013 12:12 PM
Points: 72, Visits: 326
We're testing some rather large - at least for us - narrow tables that will be populated with between 200 and 500 million records.

Any access to the table will be by addressing a low-cardinality id (some 20-50 distinct values) Without the option of partitioning we test some index scenarios.

The table:

Id1 (high cardinality)
Datekey
Name
Value
Id2 (low cardinality; always used in where clauses in queries)

When adding a non-unique non-clustered index , the index is only used when additional columns are included. The index space is then larger than the table.

When adding a non-unique clustered index , the index is always used (when Id2 is adressed) and index space is minimal

With DB2 as background and being used to Bitmap indices i'm trying to understand SQL Server's approach. The clustered index seems ideal, but what is the catch?
Post #1433626
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse