Jonathan, what do you mean by "you can just add a unique constraint or index to any column that should be unique?"  What is the purpose?  Putting a UK...

With one exception, all of the VARCHAR columns in that table are VARCHAR(MAX), which is a LOB datatype.  That's the first thing...

The query will remain the same, the optimiser will work out it needs to use it.

So just run the statement:

CREATE INDEX IX_DocumentLog_1 ON elliedb.DocumentLog(Title, OrderDateUtc) 
INCLUDE (Company,...

SQL Server thinks the suggested index will make a big difference. There might be more than one index needed but you'll need to look at the "missing indexes" section of...

To avoid any IF / CASE logic, I usually use this technique instead:

SIGN(Jan) + SIGN(Feb) + ...

If situations where the numbers could ever be negative, you'd need to include...

The Table has just under 2 million rows (1958393), and takes 32:08 to fully query every row and column. Does SQL go through the table line by line and...

Sorry, it looks like the end of my post was somehow truncated.

I meant to say that UNION ALL is more efficient than UNION as...

It could be something as simple as there are more than 1000 near the top of the table that satisfies the query and the 10,000th row is a long way...

Sometimes

select * from test where col_value = 'a'
union all
select * from test where col_value = 'b';

I believe that the natural unique identifier is a safety net for the application to prevent duplicate data that is hard to unravel when different records (different surrogate keys)...

It would be helpful if you could supply the query the DDL for the table(s) definitions and any indexes on the table(s).

Also the execution plan of the query when selecting...

I think you can do it by changing your LEFT JOINs to be OUTER APPLY and selecting TOP(1) within it.

  FROM #DataExtract t1
 OUTER APPLY(SELECT TOP(1)...

I'm not at all sure what your requirement is but if you want to show multiple rows on one row then you need to use a FOR XML PATH statement...

Just by looking at the join:

 tab1.Column2 = Database1.Schema1.Table1.Column2
AND...

I would try these two indexes:

CREATE INDEX IX_Table1_1 ON Table1(Column2, Column3, DateTime) INCLUDE (Id)

-- On Database 1
CREATE INDEX IX_Table1_1 ON Table1(Column2, DateTime)

Also change the...