Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Maaaaaagic Tables!!!

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
http://www.MidnightDBA.com/Jen

Comments

Posted by Jason Brimhall on 24 March 2011

Awesome.  I would have walked out on that one.  Proper explanation makes a world of difference.

Posted by Phil Factor on 27 March 2011

Never heard the word 'magic' used for any SQL Server component. The interviewer should put more water in it.

Posted by Dave on 28 March 2011

Funny...

Well, at least you found out early what type of employer they would be and what kind of knowledge they have of SQL Server.

What they ask on an interview tells you as much about them as the answers tell them about you.

Posted by webrunner on 28 March 2011

I wish SQL had magic tables. It would make a lot of tasks much easier -- assuming I didn't use up the wishes too quickly. :-)

Posted by Megistal on 28 March 2011

For fun, we could extend that mentality up to SQL Server itself and proclaim it "the magic box" !

Everyone talks about it

No one knows what's going on in it

No one really knows how to deal with it (and doesn't care much)

But everyone think it will solve instantly all their data problems

Seems like magic to me!

How Ironic I am this morning.

Posted by Richard on 28 March 2011

I'm primarily a .NET developer and I've NEVER heard of the inserted and deleted tables being called "Magic" even in the .NET realm. This must be an internal company term which makes their interview seem less than knowledgeable.

Posted by Paul Marriott-463518 on 29 March 2011

Does make you think though ... what things MIGHT we like to be "magic" in SQL Se:rver!!  A few to start the ball rolling:

1.  A table that will not accept non-valid values - without me having to specify what those are - it just KNOWS!!

2.  A stored procedure that self-corrects - I just have to THINK of what the proc needs to do, and it just DOES it!!

3.  Automatic indexes that create and destroy themselves, so that they are optimal for each query, but don't take up lots of space when not needed!!

NOW we are talking about a MAGIC database!! :)

Posted by chris.stuart on 30 March 2011

Magic to me implies, cant be explained. That’s what I would call magic, an insert or delete table is not magic, temp table maybe, but not magic!

Good thing you found out about this type of employer before you excepted the job offer, hehe!!!

The interviewer probably found out about the update/insert tables, asked someone about it and as a joke got the answer of magic tables. Then started asking this stupid question to candidates to try and fool them and make himself look good….

Posted by csaptd on 30 March 2011

The table in the video is not magic it's just a very clever piece of design but most people wouldn't have a clue how to make one or how it works.  Is there a parallel to SQL Server in there somewhere?

Posted by grandguru123 on 30 March 2011

LOL!!

These silly interview questions are really annoying. I remember a place I worked once and I wondered why certain functionalities where not implemented using triggers as that is what they are there for.

Well the reply I got was, " It is because triggers are hidden".

As chris says they probably only just found out about update and insert tables which is a feature that has always been with sql.

Posted by malcolm on 30 March 2011

Hilarious!

Posted by dave.clark on 30 March 2011

I love the table in that video!  I may have to get one of those ;-)    Thanks for sharing your interview experience.  It's funny to hear of the odd questions asked.

Posted by Drew Seale on 30 March 2011

That's really funny.  You definitely don't want to work there.  I'm guessing a lot of their code has "magic" in it as well.

Posted by ross.cecil on 30 March 2011

I've been writing code for over 40 years, work in both SQL and .NET worlds, and have never heard of "magic" tables.  That's almost as good as the interviewer who wanted me to document all of my illegal and illicit drug use because "all you computer guys smoke dope".

Posted by bvbellomo on 30 March 2011

If I were interviewed about "magic" tables, I would have done my disappearing act.

Posted by Carl Federl on 30 March 2011

I would have answered with Arthur C. Clarke's Third law:

Any sufficiently advanced technology is indistinguishable from magic.

Posted by Eric Russell on 30 March 2011

When I Google +sybase +"magic table", I see that the term is more common within the Sybase community for referring to the virtual deleted and inserted tables. There are several collections of interview questions that make a reference to it.

Posted by CriticalStatus on 30 March 2011

thanks for brightening my day.  that's too good.  did you take the job?  I would have thought, "OK this guy is a nut.  Do I want to work for him?"  I have some magic stored procedures for him...

Posted by Jen McCown on 30 March 2011

Wow, thanks for all the comments, folks!

Dave: "Well, at least you found out early what type of employer they would be and what kind of knowledge they have of SQL Server."  I'm not sure I can judge them entirely based on that. There are plenty of disconnects among different disciplines, and certainly among disparate shops.

Megistal: "extend that mentality up to SQL Server itself and proclaim it "the magic box" !" I'm pretty sure people already do that!

tyther9: "I'm primarily a .NET developer and I've NEVER heard of the inserted and deleted tables being called "Magic""   You're not the only one who said so, and I'm glad...

chris.stuart: "The interviewer probably found out about the update/insert tables, asked someone about it and as a joke got the answer of magic tables."  No, the interviewer was an HR guy with a great admiration of the .NET team that gave him the questions. He had the air of "oooh, this is real insider knowledge!" when he asked the question. I'm prepared to believe they really call them magic tables in that shop.

csaptd: "The table in the video is not magic it's just a very clever piece of design... Is there a parallel to SQL Server in there somewhere?"  Not necessarily. It was labeled "magic table", and I like that the physical table shown is no more magic than anything in SQL Server.

grandguru123: "...I wondered why certain functionalities where not implemented using triggers as that is what they are there for. Well the reply I got was, " It is because triggers are hidden"."  Wow. By that mentality, *everything* in SQL Server is hidden - including the tables and data - because they're not actually shouting at you when you log on...

ross.cecil: "That's almost as good as the interviewer who wanted me to document all of my illegal and illicit drug use because "all you computer guys smoke dope"."  Holy shinola, you have GOT to be kidding me...

Carl Federl: "Arthur C. Clarke's Third law: Any sufficiently advanced technology is indistinguishable from magic."  NICELY done.

Posted by Jen McCown on 30 March 2011

CriticalStatus: "did you take the job?"  Oh, no. I didn't even take the second interview, for unrelated reasons.  (I believe we talked about the unrelated reasons on last week's web show:  http://bit.ly/g1QCjO, somewhat NSFW for language and adult themes.)

They were very enthusiastic about me, though.  

Posted by Sid Seton on 30 March 2011

Maybe they are like the reports that users automagically expect to apear before they are requested.!

Posted by Wayne, The on 30 March 2011

I hate interviews that turn into "Guess what I am thinking". They are an ill conceived way to gauge a candidate's abilities. I always thought it would be better to ask the candidate what their duties where, interesting things they'd done and what problems they'd solved. Then dig into the details (both idiots and geniuses are revealed this way). Asking for trivia style answers only serves to show that titles are more important than skills to the interviewer.

Posted by Charles Cherry on 30 March 2011

Bullwinkle: "Hey Rocky! Watch me pull a record out of my magic table!"

Rocky: "Again!??"

Bullwinkle: "I'll just rustle up my sleeve and 'Presto!'"

Angry Record: "Roarrrr!"

Bullwinkle: "Ooh! Don't know my own strength!"

www.youtube.com/watch

Posted by Charles Kincaid on 30 March 2011

And here I thought that they we thinking of Dates and Numbers tables (Hello, Jeff).  Magic - good greif.

Interviewer:  Where do you see yourself in 5 years?

Me: Doing life for killing people who ask damn fool interview questions.

No, I did not give that one.  It's funny though.

Posted by WWare on 30 March 2011

Jen:  No, the interviewer was an HR guy with a great admiration of the .NET team that gave him the questions.

Always a bad thing when an HR person attempts to give a technical interview.

Jen:  I like that the physical table shown is no more magic than anything in SQL Server.

I thought the video was a perfect analogy!

Posted by Drapetomaniac on 30 March 2011

Love that table in the video.

Posted by Nakul Vachhrajani on 30 March 2011

Really funny! What a start to my day!

I have heard this term a few times, and I have attempted to correct the believers - how successful I am, I don't know.

Have a lovely day ahead!

Posted by ross.cecil on 31 March 2011

Jen, In my case the interviewer was the HR "leader" for the business; he was also the son of the founder.  He was a believer in what he called the "phsycometric method" and had a complete battery of tests he wanted to subject me to.  At that point, the Chief Engineer interrupted the process, took me from the HR guy and said "you weren't supposed to see him, sorry about that".  My only request of the Chief Engineer was to ask for the nearest exit because I was leaving NOW!  A week later I got a letter from the owner/founder offering me a job at a surprizingly high pay level.  I filed it appropriately and went on with my life.

Posted by casinc815 on 4 April 2011

Interesting interview technique.  I agree with many in this stream there are times when you just have to say "Thanks, but no thanks!" and wish the knuckleheads well.  Another classic example of a missed opportunity by an employer.

Leave a Comment

Please register or log in to leave a comment.