Magic Tables in the SQL Server

  • Hello Everyone, Can anyone explain Magic Tables in the SQL Server? I know it automatically creates temporary tables but I want to know deep information about it. Actually, I am preparing some topics for the SQL server interview and I have doubts regarding this. If anyone knows please explain me.

  • Had to lookup up what you meant with "magic tables". Are those the inserted / deleted tables you can reference from triggers or the output clause?

    What is magic table in SQL Server? (c-sharpcorner.com)

    Haven't found the deep dive yet

  • I've been a SQL Server DBA for 20 years, I've never heard of "magic tables" in SQL Server.  You need to be clearer and more specific about what you're trying to analyze.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • This sounds a little like a LMGTFY question: https://www.sqlshack.com/magic-tables-in-sql-server. More seriously, it's not possible for you to anticipate every question an employer will ask you. Whilst I don't pretend to have anything like the experience of the regular contributors, I'd never heard of the term "magic tables"  before and I would doubt most experienced DBAs or developers would use the term. Take another look at the job specification and ask yourself if you really do have the right knowledge, skills and experience. If you do, you will be able to go into the interview with a reasonable chance of succeeding. If not, try to improve your skills in your current job or set your sights lower

  • Oh, virtual tables.  Yeah, SQL has lots of virtual tables.

    Yes, you could easily be asked about the inserted and deleted tables.  You can treat these as regular (physical) tables in SQL triggers, but realize that they don't exist as physical tables, SQL just makes them look to you like they're physical tables.

    Also, SQL provides virtual tables (from functions and/or views) that give you metadata.  For example, sys.dm_db_index_usage_stats.  You might also be asked about those "tables".  Some of these are only views, some are based on actual tables.

    One big advantage of a relational data base is that you can use any view as if it were a table.  You don't even need to know whether it physically exists as a table or not.  Just like when you create your own views, and use them as if they were tables.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Btw, I suggest not using the term "magical tables" in your interview.  It sounds rather unknowledgeable about relational dbms's.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • I just wanted to add my vote that I have not heard the INSERTED/DELETED tables being called "magic tables" before.  I am also not sure what "deep information" you are hoping to get about them.  If you had some specific questions about them, we can probably help or point you in the right direction.

    Overall though, I agree with irgp's comments. Expanding on them, knowing the internals for how SQL does things in the INSERTED/DELETED tables is interesting knowledge, but doesn't really help you that much in your day to day duties as a DBA.

    If you are applying for the job, I would say go for it.  Best case, you get hired.  Worst case, you get asked questions you don't know the answers to and have a good base for what to train on next.

    I know when we were hiring a Developer (C# and SQL), I helped design some of the SQL related questions.  Two of my best ones (as they helped filter out a few people) were:

    1 - Can you explain the difference between a LEFT JOIN, a RIGHT JOIN, and an INNER JOIN?

    2 - Can you explain the difference between UNION and UNION ALL?

    Question 1 is pretty easy and even if you don't know SQL, you can get a pretty good guess from the names.  And if the interviewee explained those 3 and some of the others, they got bonus points.  Question 2 is one that I've seen experts get caught by before when doing presentations, which is why I have it in my back pocket as a good interview question.  Most of the interviewees had no idea what the difference was, which was a little surprising, but at the same time I gave credit to ones who admitted they didn't know and guessed anyway.  The ones who confidently gave a wrong answer got negative bonus points.  In the end we hired someone who got question 2 wrong, but they told us they didn't know the difference, but explained what UNION did and that it was something they would look into.  When they got hired, they came to me and my boss (who did the actual hiring) and explained what the difference was so we knew we hired the correct person for the role!  Someone who doesn't know everything and is willing to learn.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!

  • Mr. Brian Gale wrote:

    1 - Can you explain the difference between a LEFT JOIN, a RIGHT JOIN, and an INNER JOIN?

    I'm curious as to why you left out FULL OUTER JOIN?  I'm guessing you don't expect people to know that one, but it still might be interesting to see their reaction to it, and if they can "work it out" if they do happen to know that LEFT and RIGHT JOINs are.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • My goal was not to "stump" the interviewee, but to see if they had knowledge of SQL Joins and if they could do basic problem solving based on the words provided.

    In a real world scenario, if they got stuck with JOIN syntax, they could easily google it.  I mean, if I was looking to stump them, I would ask them to write a syntactically correct MERGE statement on a whiteboard.

    Plus the position was going to be focused more on C# development than SQL development, but there was SQL that would be involved and for the most part, they should have only needed LEFT JOINs for their data.

    Plus, that fell under the "bonus points" if they provided the full outer join or the cross join.  Probably wouldn't hurt to add it in though.  The FULL OUTER JOIN is a useful one in some cases.  Not for the project they were being hired for (LEFT or RIGHT was sufficient for that), but I may add it in for future interviewees.  Mind you, my current role is migrating away from DBA so I may not be included in future database hires.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!

  • Excellent point about the CROSS JOIN.  It's much more common than FULL.  Probably want to add CROSS rather than FULL to the q.  Or bring up FULL and/or CROSS in a separate q, as, like you said, a kind of bonus q.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Soon, i will provide a complete article on this and for SQL interview questions, you can visit here.

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply