what is magic query in SQl - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

what is magic query in SQl

Author

Topic

prasaditc55
Starting Member

8 Posts

Posted - 2012-07-27 : 02:59:37

Hi,

wat is magic query in sql with example

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-07-27 : 03:11:36

No idea. Never heard of that.

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-27 : 03:17:11

where did you hear about it? in what context?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-07-27 : 06:48:58

I know we're not supposed to reveal all our tricks, but you can have these two:

SELECT Rabbit FROM Hat

INSERT Sleeve VALUES(Null)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-28 : 13:46:39

well, any sufficiently advanced technology may be indistinguishable from magic but I doubt sql server qualified for that. Yet..........

If you are coming from an iterative background then the set based operations can seem magical but I doubt that's what you are after...

here's some..... advanced exploits of the engine that seem pretty magical:
http://beyondrelational.com/modules/2/blogs/70/posts/10865/quirky-update-in-sql-server.aspx
http://www.sqlservercentral.com/articles/T-SQL/62867/

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-07-29 : 18:24:32

It is garbage/huge security hole that used to be built in with php befor version 5.

prasaditc55
Starting Member

8 Posts

Posted - 2012-07-29 : 23:37:32

I heard this question in interview..

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-30 : 04:38:42

were they maybe talking about a product? There is this:
http://www.merlon.com/products_sqlmagic_info.html

Never used it or even heard of it. If that was interview question, be glad you didn't get the job.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-30 : 05:49:38

merge rather than magic?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SQLJames
Starting Member

35 Posts

Posted - 2012-07-30 : 10:32:25

Perhaps:

There are two tables Inserted and deleted in the SQL Server, which are popularly known as the Magic tables. These are not the physical tables but the SQL Server virtual tables usually used with the triggers to retrieve the inserted, deleted or updated rows.

From this link:[url]http://beyondrelational.com/modules/17/interview-questions/238/interview-questions/7059/what-are-the-magic-tables-in-sql-server.aspx[/url]

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-30 : 10:40:23

well if someone asked me in an interview about magic tables I'd have to ask them what the hell they thought they were talking about. It would not be a good way to sell me on the company or development team!

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-30 : 11:11:39

>> popularly known as the Magic tables
Possibly not that popularly known.

Googling there are some articles about this. I suspect this is someone trying to get a name accepted. Looks like some people have got together and referenced each others articles.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-07-30 : 11:31:01

Some unintentional Errors could lead to some magic queries :P

--------------------------
Get rich or die trying
--------------------------

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-30 : 12:47:59

My guess is its called magic tables as it automatically assumes the structure of triggering table. But there's no official documentation I can find on this though.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

subhaoviya
Posting Yak Master

135 Posts

Posted - 2012-07-31 : 04:00:09

Magic tables are nothing but INSERTED, DELETED table scope level, These are not physical tables, only Internal tables.

This Magic table are used In SQL Server 6.5, 7.0 & 2000 versions with Triggers only.

But, In SQL Server 2005, 2008 & 2008 R2 Versions can use these Magic tables with Triggers and Non-Triggers also.

Using with Triggers:
If you have implemented any trigger for any Tables then,
1.Whenever you Insert a record on that table, That record will be there on INSERTED Magic table.
2.Whenever you Update the record on that table, That existing record will be there on DELETED Magic table and modified New data with be there in INSERTED Magic table.
3.Whenever you Delete the record on that table, That record will be there on DELETED Magic table Only.

These magic table are used inside the Triggers for tracking the data transaction.

Using Non-Triggers:
You can also use the Magic tables with Non-Trigger activities using OUTPUT Clause in SQL Server 2005, 2008 & 2008 R2 versions.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-07-31 : 04:14:44

if Magic tables = INSERTED / DELETED then Magic Query is query that uses INSERTED / DELETED.

That must be it. Yeah ! I got the job. Now how much are you offering me ?

KH
[spoiler]Time is always against us[/spoiler]

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-31 : 04:16:24

Where do you get the name "magic" for these tables?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-31 : 09:47:57

quote:
Originally posted by subhaoviya

Magic tables are nothing but INSERTED, DELETED table scope level, These are not physical tables, only Internal tables.

This Magic table are used In SQL Server 6.5, 7.0 & 2000 versions with Triggers only.

But, In SQL Server 2005, 2008 & 2008 R2 Versions can use these Magic tables with Triggers and Non-Triggers also.

Using with Triggers:
If you have implemented any trigger for any Tables then,
1.Whenever you Insert a record on that table, That record will be there on INSERTED Magic table.
2.Whenever you Update the record on that table, That existing record will be there on DELETED Magic table and modified New data with be there in INSERTED Magic table.
3.Whenever you Delete the record on that table, That record will be there on DELETED Magic table Only.

These magic table are used inside the Triggers for tracking the data transaction.

Using Non-Triggers:
You can also use the Magic tables with Non-Trigger activities using OUTPUT Clause in SQL Server 2005, 2008 & 2008 R2 versions.

Can you guide us to any official documentation which has a reference on this?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-31 : 10:18:37

there is no reference to Magic on BOL.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-07-31 : 12:27:01

quote:
Originally posted by Transact Charlie

there is no reference to Magic on BOL.

Funny... I would have thought that Basketball On Line would SURELY mention Magic Johnson...

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources