Oh, ok… I'll explain some more.
Transparent Data Encryption encrypts data at rest. That's the stuff that's on disk – the encryption happens when the data is written to disk, and the decryption happens as the data is loaded into RAM from the disk. The engine handles this so that it's invisible to the user, applications, and so on. Without it, you can open an MDF/NDF file in a hex editor and read the contents. With it, you can't.
Here's an example with a database that's not encrypted:
And here's an example that is:
I searched for some of the text that I could see – successfully in the first, unsuccessfully in the second.
I also used SQL Server to show me the contents of a page using DBCC PAGE, and could do this successfully (once I'd closed the files in the hex editor and brought the databases back online).
…which also worked in both databases.
I had hoped this would work okay, because I figured that DBCC PAGE would have to pull the data into RAM again (remember this system was offline – the pages weren't in RAM before), and that it would decrypt this as it did it. But I wondered if DBCC PAGE might be slightly lower-level, and bypass it somehow. I argued with myself that if TDE was indeed Transparent, it shouldn't care… what if my application relied on using DBCC PAGE, it's a known feature, even if it is officially undocumented (which is where my doubts set in).
But as you see, it worked okay.
But what if I dropped the table first? Would SQL then go "Hang on – this page isn't one that I have control over any more…" and refuse to decrypt it?
No – it works just the same.
Even if you drop an object, you can still access the pages that it used until they get overwritten. You won't be able to read them with a hex editor, but DBCC PAGE will still read them in decrypted form, letting you pull that data out.
And yes, you can even use DBCC WRITEPAGE to overwrite the bytes in their unencrypted form, so that you can use (dangerous) method of fixing corruption, even in an encrypted database. I've just redone my fix for Steve Stedman's 10th Corruption Challenge, and it worked just fine on an encrypted version of the database.
It's still T-SQL Tuesday on the topic of encryption, so I'm throwing this one into the mix for that as well.