Yeah it's not the compression. That's fine. (although I know where you can get even better than native compression <cough> Redgate </cough>), it's the TDE with compression that can lead...

Don't forget, even with a log file, if the backup process ignored SQL Server transactions, the attach could still fail.

First, you can't do this on a server where the same database already exists. You must be doing this on a new, or different, instance of SQL Server.

Here's how...

While the results may be the same, putting the JOIN criteria and the filtering criteria into the WHERE clause is less clear. I'd always default to having the JOIN criteria...

Check the execution plans, but I'll bet the second choice there performs better.

Not a problem. Niko is the best. It's where I start when I have...

Frederico, do you have any test case to support your speculations?

I (and I guess Jeff Moden too) would be particularly interested in a test script which demonstrates advantages of...

I would still consider them good for history/audit tables as these aren't queried often - saving on space is what would really be important here, not speed of processing...

Columnstore index is a workaround for denormalised data storage.

follow basic data normalisation rules and there won’t be any place for columnstore indexes in your database.

not to mention - you’re...

So, not good for general history/audit tables...

You could certainly query sys.dm_exec_query_stats. That's going to show the queries that are currently in the cache, recent activity. It includes a last execution time too, so you can get...

Post the plans for both queries and it might be possible to suss out what's going on.

Yes to all the stuff @leo-2.Miller states. We simply don't have the data we need to tell you what the solution is. I'm pretty sure you don't have the data...

The app may be handling the deadlock "gracefully" by retrying the failed process (try/catch) without the user even knowing, but ideally the app should be fixed to reduce the...

Thanks Grant. I really appreciate for helping me with this. Yes it is a classic deadlock problem . Actually we have only one table and just one simple update....