I had an interview earlier this week. An interview for a SQL developer position. It went fine. But. Question number X in the initial tech screen was, and I quote:
“What are magic tables?”
……I’m sorry, what?
No, really. “What are magic tables?”
Uhm. There’s no such thing.
Oh, yes there are! This is my favorite question.
There’s no such thing in SQL Server. I’ll put it this way: The term “magic table” is nowhere in the official product documentation, and I’ve never seen it written anywhere else personally.
To cut to the chase, the answer they where looking for was “The inserted and deleted tables”. “Magic tables” is apparently .NETspeak for “inserted and deleted tables”. Let me be clear here: I am firmly against the use of the this term for the inserted and deleted tables….unless you’re being ironic.
Inserted and Deleted (NOT magic) Tables
The Inserted and Deleted tables aren’t magic. They’re a feature, just like anything else in SQL.
– After INSERT or UPDATE a table, the Inserted table is created (behind the scenes), and holds copies of the records that were inserted or updated after the fact.
– After UPDATE or DELETE, the Deleted table is created (behind the scenes), and holds copies of the records that were updated or deleted (as they were before the UPDATE or DELETE).
Facts about Inserted and Deleted tables:
SQL Server creates and manages them.
They live in memory. (Edit: Sort of. See conversation below.)
These tables are temporary, and only accessible from the statement that created them.
They are not magic.
Rather than read Books Online to you, I will just direct you to the official article, Using the Inserted and Deleted Tables. Also see Using the Inserted and Deleted Tables Outside of a Trigger on SQLServerPedia.com, and SQL Server BOL: OUTPUT Clause.
Edit: Awesome twitter conversation that I must pass on. (Gawd, I love having ex-MS dev team personnell comment on my blogs…)
PaulRandal: They’re not tables – they’re chains of version records in the version store in tempdb. In 2000 it was a view into the tnlog. Yes, they behave like tables, but they’re not.
BrentO: they’re not just in memory, to make that clear. (The blog post says they are.) Nice post though.
AaronBertrand: rather than “magic tables” they’re often referred to as pseudo-tables. But I agree with @BrentO – BOL is wrong about memory. Common myth that table variables are always in memory too. Usually they are but there is no guarantee.
Me (to Brent): I think when they say “memory-resident” it’s by default assumed that it’ll only live in memory if there’s enough space.Thats how Windows works, too. After mem is used up, it starts paging to disk, n’est-ce pas?
BrentO: yeah, that’s wrong. You can prove it by doing an operation on a table that’s too big to fit in memory.
PaulRandal: But in this case, I don’t think its necessary for BOL to explain the underlying storage mechanism
JDanton: If you go larger than memory, do they get written to tempDB?
PaulRandal: Yes. They’re stored in the version store, which is nonlogged, append-only storage in tempdb.
Me (to Paul): It makes sense that they’re not tables…they behave more like views, or table vars, far as I can see.
PaulRandal: Exactly. And they’re a shed-load faster from 2005 onwards when my dev team rewrote them to use version store
Side note: If the above doesn’t absolutely PROVE that you should be on Twitter, then nothing in this great wide world will.
THIS is a magic table:
Happy days,
Jen McCown
http://www.MidnightDBA.com/Jen
As a .NET developer that has written triggers that make use of these tables, I’ve never ever called them that. I just called them INSERTED and DELETED.
I didn’t think it was all that prevalent…I’ve talked to a few .NET devs, and never heard that term before. Clearly.
Wait, really? Then I guess every temporary object is magic? What a prat. Also, happy I’ve never gotten that question…
In other things that I hate when called magic: the iPad.
Before I “figured out” what he meant, he described them to me, and yes…it could have been a temp table, table var, DMV, or view. Or, as it turns out, INSERTED/DELETED.
Wow. I’m with Justin on this one – never referred to them as anything other than INSERTED and DELETED. I would have totally asked them how they’re any more magic than any other temporary object…
I was talking to an HR guy, reading a list of questions from his tech people. For unrelated reasons, I turned down a second interview, and so never met the team.
I’m terrible: now every time I use INSERTED and DELETED I am going to alias them as iMagic and dMagic. Just for you! 🙂
When you say you had an interview, you were asking the questions or answering the questions? I read it first as if you were asking the questions. But I don’t think that’s quite right.
So the only place we care about these tables is inside triggers, and when using the OUTPUT clause right?
Should’ve been more clear…i was being interviewed. He asked me.
Yep, far as I can tell, triggers and OUTPUT.
Thanks for taking the Magic out of life. Realist.
Magic tables are the ones that have well designed indexes and rarely have page splits. They stay in the buffer pool all the time. Transactions don’t deadlock on them, they just give each other HUGS. They *GLITTER*!!!
WORD!
Those are called Twilight Tables. Like vampires, they only exist in your imagination. Or in a chick flick.
I checked out the BOL yesterday, indeed they are talking about “memory resident” structures, and as far as I could remember they’re using the version store beginning with SQL 2005. Thank you for the update.
You mean an MVP does not know the DB setting
SET MAGIC ON
I put that in every sp that I write!
🙂
Great story!
Wonderful blog, and fun comments. This is why you’re a master.
Awww, thanks, uh, mister….”mac-gow-an”. You’re too kind…
Pingback: Disaster Prevention by Triggers | The Lone DBA
Pingback: Disaster Prevention by Triggers | The Lone DBA