Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nvisibhm
Starting Member
23 Posts |
 Posted - 2004-10-14 : 08:35:20
|
| can someone explain to me how indexes should be setup. I created an index on a date field with the default settings. When running a query on table with the date field in the where clause, the index was not used. I changed the index to be clustered and now the index is used. Why does it have to be clustered? Also, is there any other settings I need to change to optimize my index? Thanks.NV |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-10-14 : 09:09:26
|
| post relevant DDL and SELECT statements to advance this furthur. |
 |
|
|
nvisibhm
Starting Member
23 Posts |
Posted - 2004-10-14 : 10:10:05
|
| relatively simple sql statementSelect CustomerNumber, CustomerName, SUM(SalesAmountUSDollars) PriorSales, SUM(CASE WHEN InvoiceDate >= '09/01/2003' and InvoiceDate < '10/01/2003' THEN SalesAmountUSDollars ELSE 0 END) PriorYearMonthlySalesFrom slsMain Where InvoiceDate >= '01/01/2003' and InvoiceDate < '01/01/2004'Group By CustomerNumber, CustomerName Order By SUM(SalesAmountUSDollars) DESCNV |
|
|
|
surefooted
Posting Yak Master
188 Posts |
Posted - 2004-10-14 : 12:52:46
|
| It doesn't have to be clustered. The query choose an index in part based on the selectivity of the index. If you have a table with 10000 rows with only 3 distinct vaules and then index the column, the query engine will probably choose a table scan over the index. Also, be careful of how it uses the clustered index. You want a clustered index seek. a clustered index scan is a table scan.-JonNow a "Yak Posting Veteran". |
|
|
|
sify
Starting Member
18 Posts |
Posted - 2004-10-14 : 21:00:37
|
| Refering to Jon comment, if we have a table with few distinct values and repeating many times over in the table, is it realy worth creating an index on the column on that table?We have a table with 14 lac rows and only 4 distinct values. Each values atleast repeats 4 lac times there is no advantage gained by creating an index on this column on this table. SQL Server will go for the table scan even if index is created.So if the date is repeated, to create a composite index is a better option. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-14 : 21:14:33
|
| You should not create indexes on columns that have low selectivity. The index will never be used. Tara |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-10-15 : 01:20:10
|
| Am I right in thinking that a low selectivity index will still be used for RI?Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-15 : 12:09:23
|
| Well yes. You can't avoid the ones needed for RI. Just don't add needless ones.Tara |
|
|
|
|
|
|
indexes