July 3, 2012 at 11:00 pm
Hi ,
what is Microsoft join?why people dont recommend it.Does using Microsoft join degrades the performance .
Please help me in understanding this.
Regards
Chaithanya M
July 4, 2012 at 1:55 am
No such thing as a 'Microsoft join'. What are you referring to?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 4, 2012 at 2:30 am
Hi ,
Let me take 2 examples :
microsoft i am referring to : select * from emp ,dept where emp.deptno=dept.deptno
So, is there any performance impact over ANSI JOIN, if we write the query like above.
Regards
Chaithanya M
July 4, 2012 at 2:37 am
chaithu559 (7/4/2012)
Hi ,Let me take 2 examples :
microsoft i am referring to : select * from emp ,dept where emp.deptno=dept.deptno
So, is there any performance impact over ANSI JOIN, if we write the query like above.
Regards
Chaithanya M
That syntax is the old ANSI-89 style join. AFAIK, the SQL optimiser will read it the same as the more modern ANSI syntax so performance isn't an issue. Readability however, is a huge issue.
This: -
SELECT *
FROM emp, dept
WHERE emp.deptno = dept.deptno
is harder to read than this: -
SELECT *
FROM emp a
INNER JOIN dept b ON a.deptno = b.deptno;
Of course, that's just my opinion. But I bet a lot of people share it.
Also, imagine you're working with big data and you forget to add your join condition. In the first instance you execute and get a cartesian product of the two tables. In the second you get an error at compile time so you notice the error straight away.
July 4, 2012 at 2:38 am
What you've shown there is the very old-style ANSI join. It's not recommended because it's harder to read and easier to make a mistake and cross join your tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 4, 2012 at 3:38 am
Hi ,
Thank you very much for the quick reply. so there's no term called Microsoft join ?
What is ANSI standard followed in Sql server 2008 ?
Please help me in understanding the above
Regards
Chaithanya M
July 4, 2012 at 4:04 am
July 4, 2012 at 4:46 am
chaithu559 (7/4/2012)
so there's no term called Microsoft join ?
No, and the join type you're asking about is the style that's heavily used in Oracle and, I believe, Sybase, not so much SQL Server
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 4, 2012 at 6:04 am
In Sybase people use the ANSI standrad but still there are people who use old style. For left join almost everyone uses the ansi standard as the old syntax *= doesnt work properly sometimes as no diff betwene on and where clause in old.
In sybase you could write like this as well.I have never checked in SQL. But DO NOT USE THIS.It is very unreadable.
select * from syscolumns sc
inner join sysobjects so
on sc.id= so.id
,systypes st
where st.usertype = so.id
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 4, 2012 at 11:01 pm
Hi Gail,
Thank you very much for your reply. One more thing i wanted to know is, if ANSI-89 syntax is supported or not supported in sql server 2012(Denali).
Please help me in knowing this
Thanks and Regards
Chaithanya M
July 5, 2012 at 4:34 am
It's still supported and probably always will be since it's made up of the most basic elements of sql (tables and where clauses). To stop supporting it would mean removing one of those concepts and that's not going to happen.
But just because it's supported doesn't mean you should use it. The newer style is far more readable. About the only argument I could think of for continuing to use the old style is if you want your code to run on very old databases.
July 5, 2012 at 4:39 am
chaithu559 (7/4/2012)
Hi Gail,Thank you very much for your reply. One more thing i wanted to know is, if ANSI-89 syntax is supported or not supported in sql server 2012(Denali).
Not fully, no.
The inner join syntax is, because that's impossible to remove. The outer join syntax (WHERE table1.column1 *= table2.column2) has been deprecated since SQL 2005 (7 years now) and is completely unavailable in SQL 2012.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply