Version 1

    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.