Excellent work - I did not even think about pre-aggregating the data but it makes sense. But again, as I suspected, the original cross-tab version still performs better than...

Okay, it can be done in a single pivot - but I had to cheat and performance will probably suffer. As far as I have been able to determine,...

I don't think it is possible - and, you have to use the derived table (or a CTE) or you get some very interesting results. Try to pivot straight...

Of course, now that I actually think about it. Been a long day...

sp_updatestats, by default - updates statistics with a default sampling rate. You can try using the resample option, but that will only work if an auto update has not...

Not exactly a single pivot - but 😎

SELECT amt.Company

,amt.Year

,COALESCE(amt.[1], 0.00) As Q1Amt

,COALESCE(qty.[1], 0.00) As Q1Qty

,COALESCE(amt.[2], 0.00) As Q2Amt

,COALESCE(qty.[2], 0.00) As Q2Qty

,COALESCE(amt.[3], 0.00) As Q3Amt

,COALESCE(qty.[3], 0.00) As Q3Qty

,COALESCE(amt.[4], 0.00) As...

Based upon how the question was stated, and the example given - only the second one is correct. The third answer does not return the result with DateOfLeaving in...

Yes - and that is why I avoid using it in production code. Although in this situation I can see that it shouldn't matter much and you could always...

Yeah, I guess I could see using '*' in that situation - but it still suffers from the same problem. If someone modifies the source table, you will have...

Sorry, guess I was not clear enough. All I was saying was that using synonyms ADDS options, but using synonyms does not replace using views at all.

Just one example...

I am not sure where synonyms cannot be used that views can. Any examples? I like the ability to abstract out access to linked servers and/or other databases...

In the above, it is mentioned that you should create views to access your linked servers. That is good, but I would go one step further and define synonyms...

Everything Jeff has given you is good, but - you don't need to modify any code at all including modifying the views if you use Synonyms. Here is an...

If you create a schema called 'db', then create a synonym for a table in your other database as:

CREATE SYNONYM db.Test FOR linkedserver.Database.dbo.Test;

You can then query it using:

SELECT * FROM...

Another thing you can do, and should do is:

ALTER DATABASE SET PAGE_VERIFY CHECKSUM;

Then, include the checksum option in your backups:

BACKUP DATABASE ... WITH CHECKSUM;

Not only with the backup create a...