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.
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.
THIS is a magic table:
Happy days,
Jen McCown
