Below are answers to questions asked during the recent webinar. If you have additional questions, please contact Jeff Garbus at jeff@soaringeagle.biz
Q: How can I effectively perform index on varchar columns?
A: Use "full text" indexing instead of clustered or nonclustered for contents of textual data. For easy things like names, simply use regular indexes.
Q: When you create clustered index, do you have two physical sturctures that store the same data? One at leaf level of clustered index and one in the table?
A: The clustered index data page is actually the leaf of the clustered index, so no.
Q: Can you compare the advantages and disadvantages using multiple non-cllustered indexes vs. one covered index for a single table? What is the rule of thumb?
A: For an "and", one index is best (and will often be the only one used); for an "or", multiple indexes are best.
Q: On a nonclustered index, I read that it's better to have few or one NCI with several columns than several nci with a single column in each. Please confirm.
A: Yes, that is correct.
Q: Column store indexes do not support (n)varchar(max) datatype.
A: Avoid the column store for performance
Q: In an attempt to change multiple indices with single columns each into single nci with multiple columns, what's the best approach to implement this change? DB needs to be up 99.9%.
A: Creating a NCI will cause shared locks on the table; do it during a low impact time, and turn parallelism on.
Q: In below scenerio..Table1 (Col1, Col2,col3....col10)Ind1 : Col1.Ind2 : col1, col2Ind3 : col1, col2, Col3Ind4 : Col1, col3, col2. How to avoid/trace these overlapping indexes? In this scenerio, can I delete Ind1 and Ind2?
A: Yes because index 3 can handle anythign that index 1 & 2 are used for
Q: Can an index help with a COUNT(*) query that does not have a WHERE clause?
A: Yes. Via an index scan
Q: Does it make sense to create an index independent from where clause order? I mean(where id=@1 and city=@2, create index id,city or create index city,id)
A: Only the order for columns in the index matters, not the order in the where clause
Q: Which is faster Clustered or nonclustered index?
A: Usually clustered for random retrievals or ranges, though nci can be faster if index covering is used.