Magic Tables in the SQL Server

Question

Magic Tables in the SQL Server

Ankitdixit

SSC Enthusiast

Points: 196
More actions
December 29, 2020 at 7:09 am

#3824991

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.
July 14, 2022 at 4:25 pm

This was removed by the editor as SPAM
July 14, 2022 at 4:31 pm

This was removed by the editor as SPAM

Viewing 14 posts - 1 through 13 (of 13 total)

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

Jo Pattyn SSC-Dedicated Points: 32066 More actions · Answer 1

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

ScottPletcher SSC Guru Points: 100942 More actions · Answer 2

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) A socialist is someone who will give you the shirt off *someone else's* back.

irgp SSC Journeyman Points: 80 More actions · Answer 3

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

ScottPletcher SSC Guru Points: 100942 More actions · Answer 4

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) A socialist is someone who will give you the shirt off *someone else's* back.

ScottPletcher SSC Guru Points: 100942 More actions · Answer 5

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) A socialist is someone who will give you the shirt off *someone else's* back.

Mr. Brian Gale SSC-Insane Points: 24675 More actions · Answer 6

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!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

ScottPletcher SSC Guru Points: 100942 More actions · Answer 7

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) A socialist is someone who will give you the shirt off *someone else's* back.

Mr. Brian Gale SSC-Insane Points: 24675 More actions · Answer 8

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!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

ScottPletcher SSC Guru Points: 100942 More actions · Answer 9

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) A socialist is someone who will give you the shirt off *someone else's* back.

saurabhhooda SSC Enthusiast Points: 190 More actions · Answer 10

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

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 11

It's amazing to me how much incorrect information there is posted in posts containing a list of interview questions.

My recommendation to people using such lists is to only look at the questions and then first find out what the official documentation says. Then, look on multiple forums and articles to see other people's take on such things. Then go back and look at the original answer.

Always remember though... "Half of all that is written is wrong and the other half is frequently written in such a fashion that you can't actually tell".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)