Would a columnstore index improve this insert
PS: running the select gives error – An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.
CREATE INDEX [tDOD_V1] ON [dbo].[tblDataObjectDetail] ([Value]) INCLUDE ([DataObjectID],[FieldID]);
Or perhaps a filtered index would work as well (depending on how many rows actually have NULL in the Value column):
CREATE INDEX [tVOD_V2 ON [dbo].[tblDataObjectDetail] ([Value]) INCLUDE ([DataObjectID],[FieldID]) WHERE [Value] IS NOT NULL;
(However, since it seems like this is a large portion of the table, I don't know that the filter will add any substantial benefit, at least for this query. If you have other queries where you filter on only the NULL values, a filtered index with the opposite clause might be useful.)
The exception you're getting is from Management Studio, not SQL Server, and it sounds like you're running the query on a machine that doesn't have enough memory capacity to render the results. You are returning 47MM rows to a client grid – surely it is not useful to try to render that many rows.