Showing results for 
Search instead for 
Did you mean: 
Create Post

Indexing for SQL Server Performance - May 9, 2013

Below are answers to questions asked during the recent webinar. If you have additional questions, please contact Jeff Garbus at

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.

Labels (1)
Version history
Revision #:
1 of 1
Last update:
‎01-08-2015 09:23 AM
Updated by: