Below are answers to questions asked during the recent webinar. If you have additional questions, please contact Jeff Garbus at firstname.lastname@example.org
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.
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community.
More than 150,000 members are here to solve problems, share technology and best practices, and directly
contribute to our product development process.
Learn more today by joining now.