May 3, 2012 at 11:01 am
Eugene Elutin (5/3/2012)
Actually this material wasn't written by SQL Server team and I would say it's irrelevant to MS T-SQL...
How do you know who wrote it? I don't see any authorship on it, and it is on MS's site. Even so, why would it be irrelevant for T-SQL? That's obviously T-SQL code in the examples. It certainly isn't C# code.
Scroll page down and read the comment (which many would agree with, as it's a true)...
Am I supposed to just take their and your word for this or do you have citations?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 3, 2012 at 11:02 am
drew.allen (5/2/2012)
Furthermore, it's just clearer. If you specify CROSS JOIN, it's absolutely clear that you are expecting the Cartesian product of the two tables.I simply see no advantage and plenty of disadvantages to using the comma syntax for joins of any type.
That's absolutely nothing but a personal choice. In certain instances, I find the exact opposite of what you say to be true.
I'll throw another "bomb" into the mix. It'll be a very long time before they totally do away with "Equi-Join" syntax in the WHERE clause because they would first have to deprecate and discontinue correlated sub-queries.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 3, 2012 at 11:58 am
drew.allen (5/3/2012)
Eugene Elutin (5/3/2012)
Actually this material wasn't written by SQL Server team and I would say it's irrelevant to MS T-SQL...How do you know who wrote it? I don't see any authorship on it, and it is on MS's site. Even so, why would it be irrelevant for T-SQL? That's obviously T-SQL code in the examples. It certainly isn't C# code.
Scroll page down and read the comment (which many would agree with, as it's a true)...
Am I supposed to just take their and your word for this or do you have citations?
Drew
Citations? How about the page it links to at that point?
MS documentation makes it very clear exactly what's being deprecated, which is the use of *= and =* in the Where clause for an outer join. If you have a question/challenge about that, check with Microsoft.
I checked their documentation and the ISO standard, and a comma for a cross join is still very much part of SQL queries. You disagree with that, despite me already pointing you at plenty of documentation of that point. Nothing anyone here says at this time will convince you otherwise. So, as a final solution for this, write to MS and ask about it directly. You can do so on their forums on MSDN.
If they say I'm wrong about this, link to that, and I'll accept it. Till then, I'll keep going on the assumption that MSDN and BOL are correct.
There's no point continuing to argue this when data directly from MS is available to you for the asking.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 3, 2012 at 12:28 pm
Jeff Moden (5/3/2012)
drew.allen (5/2/2012)
Furthermore, it's just clearer. If you specify CROSS JOIN, it's absolutely clear that you are expecting the Cartesian product of the two tables.I simply see no advantage and plenty of disadvantages to using the comma syntax for joins of any type.
That's absolutely nothing but a personal choice. In certain instances, I find the exact opposite of what you say to be true.
I'll throw another "bomb" into the mix. It'll be a very long time before they totally do away with "Equi-Join" syntax in the WHERE clause because they would first have to deprecate and discontinue correlated sub-queries.
Also - for better or worse, the documentation as to deprecation regarding join syntax is taking place in 2012. There is no further mention of deprecating anything else relating to join syntax in any of the "future" section of the deprecation list.
So - the easiest way to resolve this matter might simply be to download and test within the SQL 2012 preview version available today.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 3, 2012 at 1:30 pm
GSquared (5/3/2012)
MS documentation makes it very clear exactly what's being deprecated, which is the use of *= and =* in the Where clause for an outer join.
If it's so clear, why is the example one above, also from MS documentation, listed as being deprecated despite using neither *= nor =*?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 3, 2012 at 1:46 pm
drew.allen (5/3/2012)
GSquared (5/3/2012)
MS documentation makes it very clear exactly what's being deprecated, which is the use of *= and =* in the Where clause for an outer join.If it's so clear, why is the example one above, also from MS documentation, listed as being deprecated despite using neither *= nor =*?
Drew
To quote a guy named Drew:
I think that CROSS JOIN was left out, because it's just so rarely used, and also because the person who wrote it up wasn't clear, for whatever reason, on exactly what was being deprecated.
(emphasis added)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 3, 2012 at 1:48 pm
If I may also point out. The page being pointed to does not list the SQL Server version. It lists the Visual Studio version. Therefore it does not apply to SQL Server from that page.
May 3, 2012 at 4:54 pm
For what it's worth I took my own advice. I remembered having access to a 2012 downloaded copy, so I installed it.
Fresh out of the install, this works:
with base as (
select row_number() over (order by (select null)) rn from sys.columns),
base2 as (select RN from base where rn%2=0),
base3 as (select RN from base where rn%3=0)
select * from base2 , base3
where base2.rn = base3.rn
These two however, yield a "syntax error"
with base as (
select row_number() over (order by (select null)) rn from sys.columns),
base2 as (select RN from base where rn%2=0),
base3 as (select RN from base where rn%3=0)
select * from base2 , base3
where base2.rn *= base3.rn
with base as (
select row_number() over (order by (select null)) rn from sys.columns),
base2 as (select RN from base where rn%2=0),
base3 as (select RN from base where rn%3=0)
select * from base2 , base3
where base2.rn =* base3.rn
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 3, 2012 at 7:14 pm
NLV (5/2/2012)
Hi,I want to insert 100 rows like (1,2,3,4,..100) without using loops into a table using CTE. can you please help.
Ok... getting back on track for this thread... I've got the feeling that this could be an interview question because it's the type of question that I would certainly ask. 😉
Do you understand the methods being offered on this thread well enough to explain them, possible speed differences, advantages and disadvantages, and more to an interviewer? Although the question about the 100 rows has a fairly simple set of methodical and easy to memorize coded answers, the technology behind why these things work is a whole lot more complicated and has a great deal of impact in all of the T-SQL you may write.
People who [font="Arial Black"]just [/font]get the answer right because they memorized the simple code to solve it, still crash and burn during an interview because it's a "leader" question that leads to many more questions if the interviewer has anything on the ball. People who understand the technology behind the multiple correct answers and can compare many different answers including some wrong answers (like using an rCTE for this task) stand a much better chance of making it to a second interview (unless they're hired on the spot ;-))
I'll also point out that there are STILL other very high performance methods to solve this problem that have not yet been posted on this thread. My favorite consists of the following...
SELECT t.N INTO #sometable FROM dbo.Tally t WHERE t.N BETWEEN 1 AND 100;
Your next task on the way to getting "the job" would be to Google "dbo.Tally" and see what it's all about because, on any interview, if you use it, you'd better be able to 'splain it even if the interviewer doesn't ask! 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
May 3, 2012 at 7:19 pm
michael vessey (5/2/2012)
does the following count as a loop?INSERT INTO dbo.yourTable default values
go 100
??:-P??
BWAAAA-HAAAA!!!!! Ask that question on the receiving end of an interview and see what happens. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
May 4, 2012 at 2:00 am
drew.allen (5/3/2012)
GSquared (5/3/2012)
MS documentation makes it very clear exactly what's being deprecated, which is the use of *= and =* in the Where clause for an outer join.If it's so clear, why is the example one above, also from MS documentation, listed as being deprecated despite using neither *= nor =*?
It is a Visual Studio documentation error. The SQL-89 syntax is not deprecated and is unlikely to be. There is a slight difference in the internal tree that results from parsing and binding the two syntaxes, but it is generally not important. My personal view is that there are good reasons to prefer the SQL-92 syntax, but the opposing view is equally strongly held by others, so it comes down to local standards and personal preference.
Deprecated features are reported in the sys.dm_os_performance_counters DMV:
SELECT
dopc.[object_name],
dopc.instance_name,
dopc.cntr_value
FROM sys.dm_os_performance_counters AS dopc
WHERE
dopc.[object_name] LIKE N'%:Deprecated Features%'
ORDER BY
dopc.instance_name;
The never-standard *= and *= outer join syntax finally disappeared from SQL Server 2012 with the removal of the 80 compatibility option.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 4, 2012 at 3:27 am
Jeff Moden (5/3/2012)
michael vessey (5/2/2012)
does the following count as a loop?INSERT INTO dbo.yourTable default values
go 100
??:-P??
BWAAAA-HAAAA!!!!! Ask that question on the receiving end of an interview and see what happens. 😉
Agree, it could be very tricky one to answer.
I would probably go with:
"Strictly speaking, it's not a loop. It's a single statement sql batch executed 100 times."
I'll ask this one and will see what kind of responses I get...
May 4, 2012 at 8:07 am
Eugene Elutin (5/4/2012)
Jeff Moden (5/3/2012)
michael vessey (5/2/2012)
does the following count as a loop?INSERT INTO dbo.yourTable default values
go 100
??:-P??
BWAAAA-HAAAA!!!!! Ask that question on the receiving end of an interview and see what happens. 😉
Agree, it could be very tricky one to answer.
I would probably go with:
"Strictly speaking, it's not a loop. It's a single statement sql batch executed 100 times."
I'll ask this one and will see what kind of responses I get...
i would actually argue that it is a loop. except the loop is on the client side not the server side. just like a client program looping through an array and then handing SQL Server single inserts for each item in the array. its a loop but the server never see's the loop
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
May 4, 2012 at 8:30 am
capn.hector (5/4/2012)
Eugene Elutin (5/4/2012)
Jeff Moden (5/3/2012)
michael vessey (5/2/2012)
does the following count as a loop?INSERT INTO dbo.yourTable default values
go 100
??:-P??
BWAAAA-HAAAA!!!!! Ask that question on the receiving end of an interview and see what happens. 😉
Agree, it could be very tricky one to answer.
I would probably go with:
"Strictly speaking, it's not a loop. It's a single statement sql batch executed 100 times."
I'll ask this one and will see what kind of responses I get...
i would actually argue that it is a loop. except the loop is on the client side not the server side. just like a client program looping through an array and then handing SQL Server single inserts for each item in the array. its a loop but the server never see's the loop
it's not a loop - it's an iterative process 😀
MVDBA
May 4, 2012 at 1:19 pm
michael vessey (5/4/2012)
capn.hector (5/4/2012)
Eugene Elutin (5/4/2012)
Jeff Moden (5/3/2012)
michael vessey (5/2/2012)
does the following count as a loop?INSERT INTO dbo.yourTable default values
go 100
??:-P??
BWAAAA-HAAAA!!!!! Ask that question on the receiving end of an interview and see what happens. 😉
Agree, it could be very tricky one to answer.
I would probably go with:
"Strictly speaking, it's not a loop. It's a single statement sql batch executed 100 times."
I'll ask this one and will see what kind of responses I get...
i would actually argue that it is a loop. except the loop is on the client side not the server side. just like a client program looping through an array and then handing SQL Server single inserts for each item in the array. its a loop but the server never see's the loop
it's not a loop - it's an iterative process 😀
Actually, it doesn't matter if it's a loop or not. If you submit it as the only answer to the question during an interview with me, you will be promptly escorted off the premises. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply