I have a business case where I need to store all details about invoices from vehicle workshop (invoice number, dealer,vehicle number, peace, nature of service , …). I have more than 50 columns which are all selected in the same query.
In my treatment I need to do some specific treatment in order to get cleansed data and some basic transformations. The problem that I’m facing it is that I have no business key to be able to create a primary key because each line of my workshop data represents an invoice details (like a piece reparation) and sometimes I have the same duplicated lines because there are the same operation more than once in the workshop.
So I find myself with a Heap table without any clustered index. So I tried to create some non clustered index to improve performance but it is not really working and every time I load more data the select query takes more times than before.
I checked the execution plan and I observed that the main reason of my problem is RID lookup (Heap) which costs more than 85%
I need some help here ^^ and I already had the idea to create a composite index which define an invoice but it will not be unique in my data set? What do you think?
Advertisement
Answer
Clustered indexes don’t need to be unique (if they’re not, SQL adds an internal ‘unique-ifier’ so rows can be differentiated from each other), so go ahead and create one!