Thanks Ed for the nod.

Quick update, so I ended up modifying our application to default to "starts with" when searching and using a non-clustered index for that column....

For S&G and I tried adding in a Full Text Index on the ItemCode column. I also added in the following items ABC123456, DEF123456, GHI123456.

SELECT ItemCode FROM TABLE...

I wouldn't say that the part number thing is horribly overloaded, but I understand where you are going with that. We take the manufacturer part number (123) and add a...

Thanks Grant for reviewing and providing great feedback and suggestions. I try to keep table columns as minimal as possible, implementing star approach, however, this table holds a multitude of...

"When you mentioned narrowing down the rows to scan, it sounds like you're thinking the query is filtering out rows before it executes. It can't do this. It has to...

I tried a View with only the needed columns and that didn't seem to help. The query specific NCI seems to work pretty good, returning results first go in 2-4...

OK, thanks for the replies. Something else I wanted to point out is that if I take the ID and ItemCode into a new table as the only columns, either...

Thanks for the reply. The heap is definitely faster however the problem with leaving as a heap is that our software uses the ID column primarily for joins, selects and...

OK, I gotcha. So no harm other than space used in having the non-clustered on CustomerID with the includes as well as the Clustered on CustomerID?

Josh

Thanks for the reply, the CustomerID is an auto increment integer column. The CustomerID is used regularly in our ERP to bring up the record, passed when making updates....

OK, so TRUNCATE TABLE TableName definitely took it to 0 KB; that seems like a good fix for this w/o adding the clustered index.

I guess my follow up question is...

OK, something is off here...please tell me I am not crazy.

So, for testing, I added two tables to my db:

TABLEC

column ID int, identity

column PN varchar(250)

I added a...

OK, thanks for the alter statement. I've been reading up on heap versus clustered and I am not sure why in this type of situation you would use a clustered...